I am working on SQL Server
.I to have log some of my Table activities..so I had created an Audit Table Like this:
IF NOT EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE Audit
(Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
FieldName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate datetime,
UserName VARCHAR(128))
GO
I have a Table 'Location' on which I created trigger for catching all activities in the Table like this:
ALTER TRIGGER [dbo].[TR_lOCATION_AUDIT]
ON [dbo].[lOCATION] FOR UPDATE,INSERT,DELETE
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000)
--You will need to change @TableName to match the table to be audited.
-- Here we made GUESTS for your example.
SELECT @TableName = 'lOCATION'
-- date and user
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','')
+ '''<' + COLUMN_NAME
+ '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''','''
+ @TableName + ''',' + @PKSelect
+ ',''' + @fieldname + ''''
+ ',convert(varchar(1000),d.' + @fieldname + ')'
+ ',convert(varchar(1000),i.' + @fieldname + ')'
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname
+ ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null)'
EXEC (@sql)
END
END
This is working fine.but my last column is username.here I want to get value from my application.(who logged in the application).this application is installed several computer and working on same database,while logging the application I am taking his user name..actually I want to show that user name here..How I can pass that value to here
How I can pass one value from c# application to this .username column I want to show this passed value
any help is very appreciable..
The answer by @Veera was perfect for Auditing with Unique ID using
SQL Server
but as you have mentionedC#
application you can Create aStatic class
that holds the User name and any other variables needed across the application .In your case it would be something like:
Now get the UserID during the User logs in using the Login form,
then you can access the UserID from anywhere in your code:
I had shown both cases of with and without SP to Pass UserID from
C#
Application and store it in the DBWithout SP
With SP
and in the
SQL Server
Create an SP likeand in the Trigger add the below lines
Note: The
Substring
length which I have entered is according to my test data alter it according to your parameter lengthEdited SP
Below is the query and Trigger I used to test the Scenario
Trigger
Here is what the result I am getting by testing like your
State_Tbl
table with TriggerNote: This concept will work only for
Insert
andUpdate
notDelete
Trigger Used