question

I'm using this trigger from [link text][1] It works great, with a few modifications. However, I'm trying to dynamically insert the column name into the @OldValue select statement from the deleted table (please see code below). How do you insert a column name into a query dynamically, or just get the value out of the deleted table without specifying a column. I tried using EXEC and sp_executesql, but no luck. They just return the sql statement. I understand this is dynamic SQL and the ramifications. Because of some compatibility problems with an old app we are forced to do it this way. Thanks for the help. /****** Object: Trigger [dbo].[LE_TRIG_DEL_AUDIT] Script Date: 04/29/2016 14:01:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND [name] = 'LE_TRIG_DEL_AUDIT') BEGIN EXEC ('CREATE TRIGGER dbo.LE_TRIG_DEL_AUDIT ON dbo.TESTAUDITTABLE FOR INSERT AS BEGIN SELECT 1 END') END GO ALTER TRIGGER [dbo].[LE_TRIG_DEL_AUDIT] ON [dbo].[TESTAUDITTABLE] AFTER INSERT, UPDATE, DELETE AS BEGIN DECLARE @bit INT , @field INT , @maxfield INT , @char INT , @fieldname NVARCHAR(128) , @TableName VARCHAR(128) , @PKCols VARCHAR(1000) , @sql VARCHAR(2000), @old NVARCHAR(2000), @new VARCHAR(2000), @UpdateDate VARCHAR(21) , @UserName VARCHAR(128) , @Type CHAR(1) , @PKSelect VARCHAR(1000), @OldValue NVARCHAR(1000), @NewValue NVARCHAR(1000), @ID VARCHAR(128), @debug bit = 0, @params NVARCHAR(4000) 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 SELECT @TableName = N'TESTAUDITTABLE' SELECT @PKCols = column_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME and ku.table_name= @TableName ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION; SELECT @UserName = SYSTEM_USER SELECT @UpdateDate = {fn now()} SELECT @field = 0 SELECT @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 @ID = coalesce( i.ORIGREC , d.ORIGREC ) , @Type = case when i.ORIGREC is not null and d.ORIGREC is null then 'Insert' -- insert when i.ORIGREC is not null and d.ORIGREC is not null then 'Update' -- update when i.ORIGREC is null and d.ORIGREC is not null then 'Delete' -- delete end, --@OldValue = (Select QUOTENAME(@fieldname) from #del), @OldValue = (SELECT d.[NAME] FROM #del d), @NewValue = (SELECT i.NAME from #ins) FROM inserted i FULL join deleted d on d.ORIGREC = i.ORIGREC SELECT @sql = ' INSERT INTO [dbo].[LE_AUDIT] ( DB_USER, TableName, ID, FieldName, OldValue, NewValue, CHANGEDATE, TRIG_TYPE) VALUES (''' + SYSTEM_USER + ''',''' + @TableName + ''',''' + @ID + ''',''' + @fieldname + ''',''' + @OldValue + ''',''' + @NewValue + ''',''' + @UpdateDate + ''',''' + @Type + ''')' EXEC (@sql) print @sql END END END IF @debug = 1 PRINT @sql GO [1]: https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/