HLRosenberger
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?
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
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
http://msdn.microsoft.com/en-us/library/bb933875.aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is the solution I went with.
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