SQL Server trigger based audit table with comparisons for specific columns

Question

I will try to explain my problem as basic as possible:

I have two tables: Foo and Foo_Audit.

On row update I want to copy the row to the Foo_Audit table, but only if the value of Foo.Bar or Foo.Qux changes. Foo.Baz needs to be ignored.

Here is my code:

CREATE TRIGGER tr_Foo_Modified ON Foo FOR UPDATE AS SET NOCOUNT ON DECLARE @rowCount int SELECT @rowCount = COUNT(*) FROM (SELECT Bar, Qux FROM inserted EXCEPT SELECT Bar, Qux FROM deleted) #TempTable IF (@rowCount > 0) BEGIN INSERT INTO Foo_Audit(Bar, Qux, Baz) SELECT Bar, Qux, Baz FROM Foo END

Everything works perfectly fine, if I test it by updating just one row at the time. But when multiple rows are updated at the same time (~400), it sometime adds the same row multiple times to the Foo_Audit table and it adds the row even if the value of Foo.Bar or Foo.Qux hasn't changed.

Does anyone know, how to make my code work on multiple row-updates at the same time? And by the same time I mean same millisecond.

marc_s · Accepted Answer · 2015-11-25 12:30:03Z

Try something like this:

CREATE TRIGGER tr_Foo_Modified ON Foo FOR UPDATE AS SET NOCOUNT ON DECLARE @rowCount int INSERT INTO dbo.Foo_Audit(Bar, Qux, Baz) SELECT i.Bar, i.Qux, i.Baz FROM Inserted i INNER JOIN Deleted d ON i.PrimaryKey = d.PrimaryKey WHERE i.Bar <> d.Bar OR .Qux <> d.Qux

Basically, just insert any rows into your audit table if bar or qux have been modified between the Deleted pseudo table (with the old values before the UPDATE) and the Inserted pseudo table (with the new values after the UPDATE).

This should work just fine for any number of rows being updated at once.

Thanks for your answer. Works like a charm! Greetings from Basel :)

Collectives™ on Stack Overflow

SQL Server trigger based audit table with comparisons for specific columns

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related