question

Hi Everybody, I am trying to setup a DML trigger to capture Programname,Loginname,Ip adress,Hostname along with Insert Update delete statements that have been executed.I did pursue options like CDC,change tracking,evendata,database audit specification,fn_dblog() but none of them offered me to capture the details i wanted. i did try tweaking the following query without any luck. /* This trigger audit trails all changes made to a table. It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed. It will put out an error message if there is no primary key on the table You will need to change @TableName to match the table to be audit trailed */ –Set up the tables if exists (select * from sysobjects where id = object_id(N'[dbo].[Audit]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1) drop table [dbo].[Audit] go 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),Ip varchar(250),Hostname varchar(500)) go if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1) drop table [dbo].[trigtest] go create table trigtest (i int not null, j int not null, s varchar(10), t varchar(10)) go alter table trigtest add constraint pk primary key (i, j) go create trigger tr_trigtest on trigtest for insert, update, 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) , @hostname varchar(500)=(SELECT HOST_NAME()), @ip VARCHAR(32) = ( SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID ), @PKSelect varchar(1000) select @TableName = ‘trigtest’ — 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+’+’,”) + ””” 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 @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,Ip,Hostname)’ select @sql = @sql + ‘ select ”’ + @Type + ”” select @sql = @sql + ‘,”’ + @TableName + ”” select @sql = @sql + ‘,’ + @PKSelect select @sql = @sql + ‘,”’ + @fieldname + ”” select @sql = @sql + ‘,convert(varchar(1000),d.’ + @fieldname + ‘)’ select @sql = @sql + ‘,convert(varchar(1000),i.’ + @fieldname + ‘)’ select @sql = @sql + ‘,”’ + @UpdateDate + ”” select @sql = @sql + ‘,”’ + @UserName + ”” select @sql = @sql + ‘ from #ins i full outer join #del d’ select @sql = @sql + @PKCols select @sql = @sql + ‘,”’ + @ip + ”” select @sql = @sql + ‘,”’ + @hostname + ”” select @sql = @sql + ‘ where i.’ + @fieldname + ‘ d.’ + @fieldname select @sql = @sql + ‘ or (i.’ + @fieldname + ‘ is null and d.’ + @fieldname + ‘ is not null)’ select @sql = @sql + ‘ or (i.’ + @fieldname + ‘ is not null and d.’ + @fieldname + ‘ is null)’ exec (@sql) end end go insert trigtest select 1,1,’hi’, ‘bye’ insert trigtest select 2,2,’hi’, ‘bye’ insert trigtest select 3,3,’hi’, ‘bye’ update trigtest set s = ‘hibye’ where i 1 update trigtest set s = ‘bye’ where i = 1 update trigtest set s = ‘bye’ where i = 1 update trigtest set t = ‘hi’ where i = 1 select * from Audit select * from trigtest delete trigtest Any help would be highly appreciated. Thank you, BT