CODEWITHSUNDEEP
sql-servert-sqltriggers
Ed Mozley
Ed Mozley

Reputation: 3519

Recording info in SQL Server trigger

I have a table called dsReplicated.matDB and a column fee_earner. When that column is updated, I want to record two pieces of information:

from the row where fee_earner has been updated.

I've got the basic syntax for doing something when the column is updated but need a hand with the above to get this over the line.

ALTER TRIGGER [dsReplicated].[tr_mfeModified] 
ON [dsReplicated].[matdb]
AFTER UPDATE
AS 
BEGIN
    IF (UPDATE(fee_earner))
    BEGIN
        print 'Matter fee earner changed to '
    END 
END

Upvotes: 1

Views: 61

Answers (1)

marc_s
marc_s

Reputation: 754488

The problem with triggers in SQL server is that they are called one per SQL statement - not once per row. So if your UPDATE statement updates 10 rows, your trigger is called once, and the Inserted and Deleted pseudo tables inside the trigger each contain 10 rows of data.

In order to see if fee_earner has changed, I'd recommend using this approach instead of the UPDATE() function:

ALTER TRIGGER [dsReplicated].[tr_mfeModified] 
ON [dsReplicated].[matdb]
AFTER UPDATE
AS 
BEGIN
    -- I'm just *speculating* here what you want to do with that information - adapt as needed!
    INSERT INTO dbo.AuditTable (Id, TriggerTimeStamp, Mt_Code, Old_Fee_Earner, New_Fee_Earner)
        SELECT
            i.PrimaryKey, SYSDATETIME(), i.Mt_Code, d.fee_earner, i.fee_earner
        FROM Inserted i
        -- use the two pseudo tables to detect if the column "fee_earner" has 
        -- changed with the UPDATE operation
        INNER JOIN Deleted d ON i.PrimaryKey = d.PrimaryKey 
                             AND d.fee_earner <> i.fee_earner
END

The Deleted pseudo table contains the values before the UPDATE - so that's why I take the d.fee_earner as the value for the Old_Fee_Earner column in the audit table.

The Inserted pseudo table contains the values after the UPDATE - so that's why I take the other values from that Inserted pseudo-table to insert into the audit table.

Note that you really must have an unchangeable primary key in that table in order for this trigger to work. This is a recommended best practice for any data table in SQL Server anyway.

Upvotes: 2

Related Questions