CODEWITHSUNDEEP
sqlsql-server
Candy Chiu
Candy Chiu

Reputation: 6679

sql server: How to detect changed rows

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.

Upvotes: 3

Views: 5758

Answers (1)

cmsjr
cmsjr

Reputation: 59225

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

Upvotes: 5

Related Questions