Sql update trigger only when column is modified

sql

trigger-engineering

best-practices

performance-tweaks

byNikita Barsukov·Sep 26, 2024

To trigger an update only when a certain column changes, craft a conditional trigger that checks for before and after values of that column. Here's how you cook it hot in SQL:

CREATE TRIGGER update_if_changed ON your_table
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON; -- "No Count" for Count Dracula

  IF UPDATE(column_name)
  BEGIN
    UPDATE t 
    SET t.Modified = GETDATE(), t.ModifiedUser = SUSER_NAME(), t.ModifiedHost = HOST_NAME()
    FROM your_table t
    INNER JOIN Inserted i ON t.id = i.id
    INNER JOIN Deleted d ON t.id = d.id
    WHERE i.column_name <> d.column_name; -- old and new values rolling with <> style
  END
END;

Tip: Substitute your_table, column_name, and id with your own table, column, and id field. The trigger swings into action only if column_name becomes the eye of change.

Taking charge of column change

When you action an update to your table, SQL Server gets moving with the Inserted and Deleted temporary tables. They're the fellows holding the new and old values of the column. We can play these two off against each other to see if column_name took a wardrobe change.

Updating modification deets

When column_name is spotted with a change, we mark the Modified, ModifiedUser, and ModifiedHost fields about when this change happened, who did it, and from which host. They report for duty through GETDATE(), SUSER_NAME(), and HOST_NAME() functions.

Performance tweak with SET NOCOUNT ON

By sticking SET NOCOUNT ON atop your trigger, you forestall the dispatch of row count messages. This saves your computer breath and shaves off network traffic.

How to make sense of the trigger

Let's decipher this with a real-world table inventory having a column QtyToRepair that you wish to monitor for updates:

CREATE TRIGGER qty_repair_update ON inventory
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON; -- Starting the count at zero, just like my bank balance

  IF UPDATE(QtyToRepair)
  BEGIN
    UPDATE i
    SET i.Modified = GETDATE(), i.ModifiedUser = SUSER_NAME(), i.ModifiedHost = HOST_NAME() 
    FROM inventory i
    INNER JOIN Inserted ins ON i.id = ins.id
    INNER JOIN Deleted del ON i.id = del.id
    WHERE ins.QtyToRepair <> del.QtyToRepair; -- Aha, somebody touched the cookies in the jar!
  END
END;

Points to ponder:

The trigger is tailored to respond when QtyToRepair is updated.
IF UPDATE(QtyToRepair) be the gatekeeper, letting only those updates through where QtyToRepair is part of the bargain.
The dance between the Inserted and Deleted tables filters out updates where QtyToRepair is just messing with you and hasn't essentially changed.

All you need to know about trigger best practices

Push for speedy performance

When sketching your trigger, always consider its performance impact. A lagging trigger can bog down your database operations. So, wear down its logic to the bare minimum for ultimate speed.

Buff up error management

Arm your triggers with error handling to tackle inconsistencies or hurdles during execution. This can be a life-saver, keeping your database from collapsing into an inconsistent state if the chips fall wrong.

Sync with the business logic

Ensure your trigger is singing the same song as your business requirements. Put down its purpose eloquently and double-check that it doesn't enforce contradicting rules against your application's tune.

Prioritize alternative solutions

If triggers seem to be falling short, take a detour. Consider other options like stored procedures or managing column updates at the application level which can offer more command and flexibility.

explain-codes / Sql / Sql update trigger only when column is modified

Linked

Replace Into Query Syntax



How to replace a string in a SQL Server Table Column



Postgresql - add boolean column to table set default



Row-level trigger vs statement-level trigger



How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

