How to track SQL Server field value changes using triggers or any other utility

My client wants to audit changes to a date field (TargetDate) in a SQL Server 2012 table as dates seem to be changing without any direct entries from users. I am investigating using triggers to capture the OrderNumber, DateValue, and a few other pertinent fields before being updated and after. My understanding is that SQL Server does not support an "Before Update" trigger so I don't know how to find the value before being updated. If possible, I'd also like to add the workstation ID that initiated the date change as well.
pabrannPresidentAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
CREATE TRIGGER table_name__trg_UPDATE
ON dbo.table_name
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(TargetDate) /*IF is optional*/
BEGIN
    INSERT INTO dbo.audit_table ( OrderNumber, TargetDate, WhenUpdated, OtherColumns )
    SELECT d.OrderNumber, d.TargetDate, GETDATE() AS WhenUpdated, d.OtherColumns
    FROM inserted i
    INNER JOIN deleted d ON d.OrderNumber = i.OrderNumber --AND ...
    --only capture row if TargetDate actually changed.
    WHERE ISNULL(i.TargetDate, '19000101') <> ISNULL(d.TargetDate, '19000101')
END /*IF*/
GO
0
 
Scott PletcherSenior DBACommented:
SQL's "AFTER UPDATE" trigger contains both the values before being UPDATEd and after being UPDATEd.

SQL automatically provides two "tables" in a trigger: inserted and deleted.  The deleted table contains the rows BEFORE the UPDATE was applied, and the inserted table contains the rows AFTER the UPDATE.  You can join the inserted to the deleted table on the table key column(s).
0
 
pabrannPresidentAuthor Commented:
Wonderful, do you have any code samples that will make it easy for me to insert this data into an audit table???   Thanks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pabrannPresidentAuthor Commented:
Fantastic, it works. Is there a way to capture the computer name that initiated the change?
0
 
Scott PletcherSenior DBACommented:
Your best chance for that would be to use the  
 HOST_NAME()
and/or:
HOST_ID()
functions.
Note that the HOST_NAME can be easily "spoofed" if the client knows what they are doing there, and takes the time and effort to do it.
0
 
pabrannPresidentAuthor Commented:
I am thrilled with how this worked out. Scott, you are the very best and I appreciate it very much.. Thank you, thank you, thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.