3

I want to create a trigger to detect whether a row has been changed in SQL Server. My current approach is to loop through each field, apply COLUMNS_UPDATED() to detect whether UPDATE has been called, then finally compare the values of this field for the same row (identified by PK) in inserted vs deleted.

I want to eliminate the looping from the procedure. Probably I can dump the content of inserted and deleted into one table, group on all columns, and pick up the rows with count=2. Those rows will count as unchanged.

The end goal is to create an audit trail: 1) Track user and timestamp 2) Track insert, delete and REAL changes

Any suggestion is appreciated.

Candy Chiu
  • 6,579
  • 9
  • 48
  • 69
  • Is this in the context of a Trigger? – StuartLC Sep 05 '12 at 17:00
  • Yes. I looked at SQL Server's built in solutions - they don't satisfy all the requirements. – Candy Chiu Sep 05 '12 at 17:01
  • 1
    possible duplicate of [How To Create Generic SQL Server Stored Procedure To Perform Inserts Into Audit Table Based on Inserted and Deleted In Trigger](http://stackoverflow.com/questions/8873335/how-to-create-generic-sql-server-stored-procedure-to-perform-inserts-into-audit) – GSerg Sep 05 '12 at 17:04
  • it is not a duplicate. my current solution implements the idea in the aforementioned post. I want a solution that detects a change, but not tracking the details of it. – Candy Chiu Sep 05 '12 at 17:17

1 Answers1

5

Instead of looping you can use BINARY_CHECKSUM to compare entire rows between the inserted and deleted tables, and then act accordingly.

Example

Create table SomeTable(id int, value varchar(100))

Create table SomeAudit(id int, Oldvalue varchar(100), NewValue varchar(100))


Create trigger tr_SomTrigger  on SomeTable for Update 
as
begin
        insert into SomeAudit 
        (Id, OldValue, NewValue)
        select i.Id, d.Value, i.Value 
        from
        (
            Select Id, Value, Binary_CheckSum(*) Version from Inserted
        ) i
        inner join 
        (
            Select Id, Value, Binary_CheckSum(*) Version from Deleted
        ) d
        on i.Id = d.Id and i.Version <> d.Version


End 

Insert into sometable values (1, 'this')
Update SomeTable set Value = 'That'
Select * from SomeAudit
cmsjr
  • 56,771
  • 11
  • 70
  • 62
  • Do you know how can I use this function in a WHERE clause? I have something like this: WHERE BINARY_CHECKSUM(i.*) <> BINARY_CHECKSUM(d.*) – Candy Chiu Sep 05 '12 at 17:18
  • To use it in a where clause I'd recommend including it in a subquery, and then doing then doing the where clause against the subquery, I added a sample. – cmsjr Sep 05 '12 at 17:41
  • NOTE: Some types of columns are excluded from the BINARY_CHECKSUM() comparison. From MS doco "BINARY_CHECKSUM ignores columns of noncomparable data types in its computation. Noncomparable data types include text, ntext, image, cursor, xml,..". – David Coster Aug 22 '17 at 23:53