SQL Server Performance Forum – Threads Archive
Inserted table contains?Hi, I have a need to write a trigger that fire up an event when a column value is 1, another event if the column value is updated to 2. Due to the programming done by the previous person, instead of just updating the column when needed, it updated all the values in the columns whenever there is a change in the record. This make it difficult for me to know if the update is for a particular column. At such, I think I have to compare the old and the new value of the particular record of that column to know what action should I fire. I know that the "Inserted" table contains some values, but can someone tell me it is the new or old values? If "Inserted" is the new values, how should I draw the old values? Thanks,
"inserted" contains the new values, and there’s another table you can use in triggers, called "deleted", that contains the old values. This is true for UPDATE events, but on INSERT the "deleted" table has no records and on DELETE the "inserted" table has no records. You can use IF UPDATE(column_name) BEGIN … END on INSERT and on UPDATE to see if a specific column was entered/updated and then act accordingly. Also, you may need to check the value for @@ROWCOUNT to see if a single record was affected or more than one. Check CREATE TRIGGER in BOL for full details.