Update trigger

[sujosh]

Is there a way I can track just the columns that were changed in an update statement.
eg.
I have a table with 50 columns. I am writing a trigger to trap the changes made to that table. When three columns got changed I would like to get the new and old value of those three columns and populate a transaction table.

Now the only way I know is to get the values for all 50 columns and check whether the old and new values are different and then do an insert inot the transaction.

Instead of doing that, Is there a way where I can get only the names of the columns that got changed?

Thanks

 

[apollo11]

You can use:
IF UPDATE(COL_NAME)
where COL_NAME is the column name for the table against which the trigger fires to test the trigger firing for specfic columns

 

[fluteplr]

You can also use columns_updated()
which will return a bitmap of all the updated columns.

Sample code from BOL.

CREATE TRIGGER my_trig2
ON my_table
FOR INSERT
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
PRINT 'Column b Modified'
GO

 

[sujosh]

Thanks apollo11!! I guess then, I have to basically loop through and do If update(col_name) and if its true then insert.

I was hoping that I can get a list of the changed column names (maybe from a temp table)

 

[apollo11]

well you can insert into a temp table that you can flush periodically. Each time you trap the column that you are looking for insert into this temp table. You can also query this temp table to prevent duplicate records being inserted

 

[sujosh]

Hi apollo,

Well i am using this procedure and was wondering what is the syntax for have the col_name as a variable because
if I try this it says syntax error
"IF UPDATE(@COL_NAME) "
Where am I wrong on the syntax?

Thanks