CODEWITHSUNDEEP
sql-servertriggers
samvdst
samvdst

Reputation: 638

SQL Server trigger based audit table with comparisons for specific columns

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.

Upvotes: 1

Views: 88

Answers (1)

marc_s
marc_s

Reputation: 754488

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.

Upvotes: 1

Related Questions