Writing trigger on a column

Question

Is it possible to write an update trigger which executes only if the particular column is updated?

eg: I have a table Table1 with columns column1, column2, column3.

I want to create an update trigger which executes only if the column3 is updated.

you can refer to http://stackoverflow.com/questions/651524/most-efficient-method-to-detect-column-change-in-ms-sql-server — Nolan Shang, Nov 25 '15 at 05:31

score 0 · Accepted Answer · answered Nov 25 '15 at 05:47

You cannot write a trigger that only executes if column3 is updated - the trigger is on the table and will fire every time something on the table changes.

But inside the trigger, you can check to see if column3 has been updated, and if it has, you can do something.

Something like

CREATE TRIGGER updateTrigger
ON dbo.YourTableName
AFTER UPDATE
AS
    -- the "deleted" pseudo table contains the old values before the update,
    -- the "Inserted" table the new values after the update
    -- but DO REMEMBER: the trigger is run **once per statement** - so 
    -- both tables will most likely contain *multiple rows* and you need
    -- to work with that
    -- Scenario here: insert data into an "Audit" table if "column3" changed
    INSERT INTO dbo.Audit (ID, OldValueCol3, NewValueCol3)
        SELECT
            d.Id, d.Column3, i.Column3
        FROM 
            Deleted d
        INNER JOIN
            Inserted i ON d.ID = i.ID  -- join on the primary key
        WHERE
            d.Column3 <> i.Column3     -- use those rows where "column3" changed

Writing trigger on a column

1 Answers1