SELECT @NewValue = @ColName FROM inserted

[pcawdron]

SELECT @NewValue = @ColName FROM inserted

Does anyone know why this doesn't work and how I can get around it?

I'm building an audit trail trigger. The trigger intercepts the update, places the old and the new value in the audit trail table. Everything appears to be running fine except the INSTEAD OF UPDATE prevents the normal update from running. In light of this I'm building a second section in the Trigger that updates the table normally and this is where the problem occurs. If possible I'd like to build it so the trigger can be quickly and easily transpose to other tables, but I can't get SELECT @NewValue = @ColName FROM inserted to work. If @ColName = 'MyPrimaryKey' then @NewValue = 'MyPrimaryKey' and not the value in the column MyPrimaryKey in the inserted table.

Any ideas?

Here's the full code FYI

ALTER TRIGGER [__SmartCash_UAT_Audit_Trail] ON [dbo].[__SmartCash_UAT]
INSTEAD OF UPDATE
AS
DECLARE @OldValue varchar(50), @PrimaryKey numeric, @InsertKey numeric, @ColName char(40),@sql varchar(1000), @tablename char(40), @tablekey char(40), @ColType char(15)
SET @tablename = '__SmartCash_UAT'
SET @tablekey = 'SmartCashID'

SELECT @InsertKey = SmartCashID from inserted
--------------------------------
IF UPDATE(Amount)
BEGIN
SELECT @OldValue = CAST(Amount as varchar(50)) FROM __SmartCash_UAT
WHERE __SmartCash_UAT.SmartCashID = @InsertKey;


INSERT INTO __Audit_Trail (TableName, ColumnName, OldValue, NewValue, ByUser, PrimaryKey)
SELECT @tablename,'Amount', @OldValue , CAST(Amount as varchar(50)), 5, @InsertKey
FROM inserted;
END
----------------------------------------
--Update column normally
----------------------------------------


DECLARE @ColPrec smallint, @NewValue nvarchar(50)
SET @sql = 'UPDATE dbo.[' + rtrim(@tablename) + '] SET '
DECLARE db_cursor CURSOR FOR
select dbo.syscolumns.[name],dbo.systypes.[name],dbo.syscolumns.[prec] from dbo.syscolumns,dbo.sysobjects,dbo.systypes where dbo.syscolumns.xtype= dbo.systypes.xtype AND dbo.syscolumns.[id] = dbo.sysobjects.[id] and dbo.sysobjects.[name]=@tablename
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ColName, @ColType, @ColPrec
WHILE @@FETCH_STATUS = 0
BEGIN
--Here's the problem
SELECT @NewValue = @ColName FROM inserted
SET @sql = @sql +'dbo.['+ rtrim(@tablename) + '].[' + rtrim(@ColName) + '] = cast('''+rtrim(@NewValue)+''' as '+ case rtrim(@ColType) when 'char' then rtrim(@ColType)+'('+CAST(@ColPrec as char(4))+')' when 'varchar' then rtrim(@ColType)+'('+CAST(@ColPrec as char(4))+')' when 'nvarchar' then rtrim(@ColType)+'('+CAST(@ColPrec as char(4))+')' else rtrim(@ColType) end +'),'
FETCH NEXT FROM db_cursor INTO @ColName, @ColType, @ColPrec
END
SET @sql = left(@sql,len(@sql)-1)
SET @sql = @sql + ' WHERE '+ rtrim(@tablekey) +'='+ cast(@InsertKey as char(10))
print (@sql)
CLOSE db_cursor
DEALLOCATE db_cursor

 

[tlbroadbent]

SQL doesn't allow the use of variables for identifiers in SQL statements. You cannot use variables for database, table, column, view, or procedure names. You can build a SQL statement as you are already doing. Then execute the statement using sp_executeSQL. sp_executeSQL handles input and output variables. See SQL BOL for more information about this SP.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[pcawdron]

I'd thought about that, the problem is I'm querying the temp table inserted and it's outside the scope of the sp_executeSQL

Any other ideas?

 

[tlbroadbent]

Insert the rows in inserted into a temporary table. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm