SQL update/delete trigger not working properly

Question

I have a trigger that is used for auditing inserted, updated and deleted rows. I am having an issue with acquiring the Old Values and New Values from the trigger. The trigger utilizes a loop to insert all values of any row that has been altered in any way (inserted, updated, deleted). However, my code is not returning the values but instead the column names as the values.

Here is part of my code:

SELECT @COLNAME = NAME 
FROM SYSCOLUMNS 
WHERE COLID = @FIELD AND
    ID = (SELECT ID FROM SYSOBJECTS WHERE NAME = 'FIN_HOTEL_DTL_TYPE') 
SELECT @OLDVAL = SUBSTRING(@COLNAME, 2, LEN(@COLNAME)) FROM DELETED 
SELECT @NEWVAL = SUBSTRING(@COLNAME, 2, LEN(@COLNAME)) FROM INSERTED 
SELECT @MODBY = MODIFIED_BY FROM INSERTED 
SELECT @MODDT = MODIFIED_DATETIME FROM INSERTED 

Answer 1

T-SQL is just a different world.

Your code is doing exactly what you have told it to do. @ColName is not a reference to a column object, but a variable holding a string value which you have loaded with the name of a column.

I suggest that you look at an example. Read the whole thing if you wish, but if you are in a hurry, drop down to the phrase CREATE TRIGGER Audit.

Also, take the time to do this with set based logic - as in the example. Harness the power and performance of the DBMS by making use of T-SQL and throwing loose the bonds of row by row coding. No really. Coded loops = poor performance. If needed, the DBMS will implement loops ( or however it wishes ) in the background.

Good luck!

---

I was trying to find this link earlier. I believe that this posting by Paul Nielsen is pretty close to what you want.