SET @execSQL = 'DECLARE @BeforeValue sql_variant, @AfterValue sql_variant '+
'SELECT @BeforeValue = D.['+@ColumnName+'], '+
' @AfterValue = I.['+@ColumnName+'] '+
'FROM Inserted I '+
'INNER JOIN Deleted D on I.['+@KeyCol+'] = D.['+@KeyCol+'] '
exec(@execSQL)
Where @ColumnName = column being updated
@KeyCol = Key column that is designated in each trigger
i see a couple of things:
isn't it true that the either the INSERTED table or the DELETED table will always be empty in the trigger, so if you inner join, the two, you'd always get no rows?
since you are declaring a variable, then the trigger would fail if the INSERTED table had more than one row in it's event.
i think you should copy whatever the stored proc was doing, instead of trying to use the stored proc.
and of course make sure the whole thing remails set based instead of assuming only one row would fire the trigger.
Lowell