Link to home
Start Free TrialLog in
Avatar of HLRosenberger
HLRosenbergerFlag for United States of America

asked on

trigger to determine if any column changed

Is there a way to write a trigger to determine when any column in a record has changed and then update a "edit_datetime" column?
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

If you do not need to know if a column was updated, you can create a trigger that occurs instead of an update or after an update (but prior to the change committing). See this link: http://msdn.microsoft.com/en-us/library/ms189799.aspx
If you need to know a specific column changed you can use the UPDATE ( <column name> ) syntax. http://msdn.microsoft.com/en-us/library/ms187326.aspx
Sure.  You'd probably want to generate the code using sys.columns rather than writing it by hand, but the general structure would look like this:


CREATE TRIGGER <trigger_name>
ON dbo.<table_name>
AFTER UPDATE
AS
SET NOCOUNT ON;
UPDATE tn
SET edit_datetime = GETDATE()
FROM inserted i
INNER JOIN deleted d ON
    d.key_col = i.key_col
INNER JOIN dbo.<table_name> tn ON
    tn.key_col = i.key_col
WHERE
    ISNULL(i.data_col1, '~~') <> ISNULL(d.data_col1, '~~') OR
    ISNULL(i.data_col2, '~~') <> ISNULL(d.data_col2, '~~') OR
    ISNULL(i.data_col3, -999) <> ISNULL(d.data_col3, -999) --OR ...other columns

GO
Instead of rolling you own code, check out the SQL Server change tracking feature.  

http://msdn.microsoft.com/en-us/library/bb933875.aspx
ASKER CERTIFIED SOLUTION
Avatar of HLRosenberger
HLRosenberger
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HLRosenberger

ASKER

This is the solution I went with.