[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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?
0
HLRosenberger
Asked:
HLRosenberger
1 Solution
 
Shaun KlineLead Software EngineerCommented:
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
0
 
Scott PletcherSenior DBACommented:
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
0
 
Randy Knight, MCMCommented:
Instead of rolling you own code, check out the SQL Server change tracking feature.  

http://msdn.microsoft.com/en-us/library/bb933875.aspx
0
 
HLRosenbergerAuthor Commented:
I went with this:  http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL

works great.   The SQL Server change tracking feature, I believe, tracks based on the logged in SQL user account;  I need to track based on my own "users" table; I was able to modify this  "Audit-Trail-Generator-for-Microsoft-SQL " to do this.
0
 
HLRosenbergerAuthor Commented:
This is the solution I went with.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now