<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Programmability
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 41 of 54 18 minAdvanced

Triggers

Code that runs by itself when data changes: AFTER triggers, the inserted and deleted tables, the single-row trigger bug, an audit trail, blocking changes with THROW, INSTEAD OF triggers on views, managing triggers, and when not to use them.

A trigger is a special procedure attached to a table. Nobody calls it: SQL Server runs it automatically whenever an INSERT, UPDATE or DELETE changes the table. BuildMart wants a history of every price change, whoever makes it, from whatever application. That is a classic job for a trigger.

Query.sql
DROP TABLE IF EXISTS dbo.PriceHistory;CREATE TABLE dbo.PriceHistory (    ChangeId  int           NOT NULL IDENTITY PRIMARY KEY,    ProductId int           NOT NULL,    OldPrice  decimal(10,2) NOT NULL,    NewPrice  decimal(10,2) NOT NULL,    ChangedAt datetime2(0)  NOT NULL DEFAULT SYSDATETIME());

inserted and deleted

Inside a trigger, two read-only tables hold the rows the statement changed. They have the same columns as the table:

Statement`inserted` holds`deleted` holds
INSERTthe new rowsnothing
UPDATEthe rows after the changethe same rows before the change
DELETEnothingthe removed rows

So an UPDATE trigger can compare old and new values by joining deleted to inserted on the key. Here is the trigger most people write first. It looks right:

Query.sql
CREATE OR ALTER TRIGGER dbo.trg_Products_PriceHistoryON dbo.ProductsAFTER UPDATEASBEGIN    SET NOCOUNT ON;    DECLARE @Id int, @Old decimal(10,2), @New decimal(10,2);     SELECT @Id = i.ProductId, @Old = d.Price, @New = i.Price    FROM inserted AS i    JOIN deleted AS d ON d.ProductId = i.ProductId;     IF @Old <> @New        INSERT INTO dbo.PriceHistory (ProductId, OldPrice, NewPrice)        VALUES (@Id, @Old, @New);END;GOUPDATE dbo.Products SET Price = 19.99 WHERE ProductId = 1;          -- one rowUPDATE dbo.Products SET Price = Price * 1.10 WHERE CategoryId = 5;  -- two rows SELECT COUNT(*) AS PricesChanged, (SELECT COUNT(*) FROM dbo.PriceHistory) AS RowsLoggedFROM dbo.ProductsWHERE ProductId = 1 OR CategoryId = 5;
Output
PricesChanged  RowsLogged
-------------  ----------
            3           2

Three prices changed but only two were logged. A trigger runs once per statement, not once per row. The second UPDATE changed two paint products, so inserted held two rows, and SELECT @Id = … kept only one of them.

The correct version is shorter. One INSERT … SELECT handles any number of rows, and the WHERE skips updates that did not change the price:

Query.sql
CREATE OR ALTER TRIGGER dbo.trg_Products_PriceHistoryON dbo.ProductsAFTER UPDATEASBEGIN    IF @@ROWCOUNT = 0 RETURN;     -- the statement changed no rows    SET NOCOUNT ON;     INSERT INTO dbo.PriceHistory (ProductId, OldPrice, NewPrice)    SELECT i.ProductId, d.Price, i.Price    FROM inserted AS i    JOIN deleted AS d ON d.ProductId = i.ProductId    WHERE i.Price <> d.Price;END;GOTRUNCATE TABLE dbo.PriceHistory; UPDATE dbo.Products SET Price = Price * 1.10 WHERE CategoryId = 5;  -- two pricesUPDATE dbo.Products SET Stock = Stock + 1 WHERE ProductId = 2;      -- no price changeUPDATE dbo.Products SET Price = 5 WHERE CategoryId = 7;             -- no rows at all SELECT ChangeId, ProductId, OldPrice, NewPrice FROM dbo.PriceHistory ORDER BY ChangeId;
Output
ChangeId  ProductId  OldPrice  NewPrice
--------  ---------  --------  --------
       1         11     13.75     15.13
       2         10     59.40     65.34

A trigger fires even when the statement changes no rows, as the last UPDATE did. IF @@ROWCOUNT = 0 RETURN; skips the work in that case. It must be the first line, because SET and most other statements reset @@ROWCOUNT.

An audit trail for every change

One trigger can fire for several kinds of statement. To tell them apart, look at which of the two tables has rows: a FULL OUTER JOIN of inserted and deleted covers all three cases in one statement.

Query.sql
DROP TABLE IF EXISTS dbo.ProductAudit;CREATE TABLE dbo.ProductAudit (    AuditId   int           NOT NULL IDENTITY PRIMARY KEY,    Action    varchar(6)    NOT NULL,    ProductId int           NOT NULL,    OldPrice  decimal(10,2) NULL,    NewPrice  decimal(10,2) NULL);GOCREATE OR ALTER TRIGGER dbo.trg_Products_AuditON dbo.ProductsAFTER INSERT, UPDATE, DELETEASBEGIN    IF @@ROWCOUNT = 0 RETURN;    SET NOCOUNT ON;     INSERT INTO dbo.ProductAudit (Action, ProductId, OldPrice, NewPrice)    SELECT CASE WHEN d.ProductId IS NULL THEN 'INSERT'                WHEN i.ProductId IS NULL THEN 'DELETE'                ELSE 'UPDATE' END,           COALESCE(i.ProductId, d.ProductId), d.Price, i.Price    FROM inserted AS i    FULL OUTER JOIN deleted AS d ON d.ProductId = i.ProductId    ORDER BY COALESCE(i.ProductId, d.ProductId);   -- AuditIds follow ProductIdEND;GOINSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)VALUES (N'Spirit level 600 mm', 1, 15.00, 20, N'each', '2024-07-01'),       (N'Wood glue 1 L',       4,  8.40, 35, N'bottle', '2024-07-01'); UPDATE dbo.Products SET Price = 14.50 WHERE ProductId = 13;DELETE FROM dbo.Products WHERE ProductId > 12; SELECT AuditId, Action, ProductId, OldPrice, NewPriceFROM dbo.ProductAuditORDER BY AuditId;
Output
AuditId  Action  ProductId  OldPrice  NewPrice
-------  ------  ---------  --------  --------
      1  INSERT         13      NULL     15.00
      2  INSERT         14      NULL      8.40
      3  UPDATE         13     15.00     14.50
      4  DELETE         13     14.50      NULL
      5  DELETE         14      8.40      NULL
Interactive

Inside the audit trigger

CREATE OR ALTER TRIGGER dbo.trg_Products_Audit ON dbo.Products
AFTER INSERT, UPDATE, DELETE AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    INSERT INTO dbo.ProductAudit (Action, ProductId, OldPrice, NewPrice)
    SELECT CASE WHEN d.ProductId IS NULL THEN 'INSERT'
                WHEN i.ProductId IS NULL THEN 'DELETE' ELSE 'UPDATE' END,
           COALESCE(i.ProductId, d.ProductId), d.Price, i.Price
    FROM inserted AS i FULL OUTER JOIN deleted AS d ON d.ProductId = i.ProductId
    ORDER BY COALESCE(i.ProductId, d.ProductId);
END;
Run a statement
dbo.ProductAudit
AuditIdActionProductIdOldPriceNewPrice
(0 rows)
Run a statement. The trigger sees the changed rows in inserted and deleted, and writes to the audit table.

Blocking a change

An AFTER trigger runs inside the statement’s transaction, after the change is made but before it is committed. If the trigger raises an error, the change is undone. That allows rules a CHECK constraint cannot express, such as comparing the old and new value:

Query.sql
CREATE OR ALTER TRIGGER dbo.trg_Products_PriceGuardON dbo.ProductsAFTER UPDATEASBEGIN    IF @@ROWCOUNT = 0 RETURN;    SET NOCOUNT ON;     IF EXISTS (SELECT 1               FROM inserted AS i               JOIN deleted AS d ON d.ProductId = i.ProductId               WHERE i.Price > d.Price * 1.5)        THROW 50010, N'A price rise of more than half needs approval.', 1;END;GOUPDATE dbo.Products SET Price = Price * 2 WHERE ProductId = 5;   -- Sand: 4.10 -> 8.20PRINT N'Not reached';GOSELECT Name, Price FROM dbo.Products WHERE ProductId = 5;
Output
Msg 50010, Level 16, State 1, Procedure trg_Products_PriceGuard, Line 13
A price rise of more than half needs approval.

Name        Price
----------  -----
Sand 25 kg   4.10

The error in the trigger rolled back the UPDATE and ended the batch, so the PRINT never ran. Inside a trigger, XACT_ABORT is on by default.

INSTEAD OF triggers

An INSTEAD OF trigger runs in place of the statement: the change itself never happens unless the trigger makes it. Its main use is making a view writable when SQL Server cannot do it alone. This view shows category names, so an INSERT into it cannot know the CategoryId. The trigger looks it up:

Query.sql
CREATE OR ALTER VIEW dbo.ProductCatalogueASSELECT p.ProductId, p.Name, c.Name AS Category, p.PriceFROM dbo.Products AS pJOIN dbo.Categories AS c ON c.CategoryId = p.CategoryId;GOCREATE OR ALTER TRIGGER dbo.trg_ProductCatalogue_InsertON dbo.ProductCatalogueINSTEAD OF INSERTASBEGIN    SET NOCOUNT ON;     INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)    SELECT i.Name, c.CategoryId, i.Price, 0, N'each', '2024-07-01'    FROM inserted AS i    JOIN dbo.Categories AS c ON c.Name = i.Category;END;GOINSERT INTO dbo.ProductCatalogue (Name, Category, Price)VALUES (N'Pipe wrench', N'Plumbing', 16.00),       (N'Ear defenders', N'Safety', 11.50); SELECT ProductId, Name, Category, PriceFROM dbo.ProductCatalogueWHERE ProductId > 12ORDER BY ProductId;
Output
ProductId  Name           Category  Price
---------  -------------  --------  -----
       15  Ear defenders  Safety    11.50
       16  Pipe wrench    Plumbing  16.00

The ProductIds start at 15, not 13: the two products inserted and deleted in the audit example used 13 and 14, and IDENTITY never reuses a value.

Managing triggers

Triggers are easy to forget, because nothing in an INSERT or UPDATE shows that one will run. sys.triggers lists them, and a trigger can be switched off and on without dropping it:

Query.sql
DISABLE TRIGGER dbo.trg_Products_PriceGuard ON dbo.Products; SELECT t.name AS TriggerName, OBJECT_NAME(t.parent_id) AS OnObject,       t.is_instead_of_trigger AS InsteadOf, t.is_disabled AS DisabledFROM sys.triggers AS tORDER BY t.name; ENABLE TRIGGER dbo.trg_Products_PriceGuard ON dbo.Products;
Output
TriggerName                  OnObject          InsteadOf  Disabled
---------------------------  ----------------  ---------  --------
trg_ProductCatalogue_Insert  ProductCatalogue  1          0
trg_Products_Audit           Products          0          0
trg_Products_PriceGuard      Products          0          1
trg_Products_PriceHistory    Products          0          0

dbo.Products now has three AFTER UPDATE triggers. They all run, but in an order you should not rely on. (sp_settriggerorder can choose the first and last.)

When not to use a trigger

Triggers are hidden: a simple UPDATE can set off several of them, they make every change slower, and their errors surprise people. Use the simplest tool that works:

NeedBetter tool
A rule about one row’s valuesCHECK constraint
A value must exist in another tableFOREIGN KEY
A default valueDEFAULT constraint
Return the changed rows to the callerthe OUTPUT clause
Full row history with point-in-time queriestemporal tables (Master level module)
Compare old and new values, or audit every change from any sourcea trigger
Quick check

An UPDATE changes 50 rows. How many times does an AFTER UPDATE trigger on the table run?

Quick check

Inside an UPDATE trigger, where do you find the price before the change?

Practice

Create a table dbo.StockAlerts (ProductId int, Name nvarchar(60)) and an AFTER UPDATE trigger on dbo.Products that adds a row to it for every product whose Stock drops from above zero to zero. Test it by setting Stock = 0 for products 1 and 3 in one UPDATE, then select the alerts.

Show a hint

Join inserted to deleted on ProductId and keep the rows where d.Stock > 0 AND i.Stock = 0. Use one INSERT … SELECT so both products are recorded.

Show the solution
Solution.sql
DROP TABLE IF EXISTS dbo.StockAlerts;CREATE TABLE dbo.StockAlerts (ProductId int, Name nvarchar(60));GOCREATE OR ALTER TRIGGER dbo.trg_Products_StockAlertON dbo.ProductsAFTER UPDATEASBEGIN    IF @@ROWCOUNT = 0 RETURN;    SET NOCOUNT ON;     INSERT INTO dbo.StockAlerts (ProductId, Name)    SELECT i.ProductId, i.Name    FROM inserted AS i    JOIN deleted AS d ON d.ProductId = i.ProductId    WHERE d.Stock > 0 AND i.Stock = 0;END;GOUPDATE dbo.Products SET Stock = 0 WHERE ProductId IN (1, 3); SELECT ProductId, Name FROM dbo.StockAlerts ORDER BY ProductId;
Output
ProductId  Name
---------  ----------------
        1  Claw hammer
        3  Tape measure 8 m

Remember

  • A trigger runs automatically for INSERT, UPDATE or DELETE on its table or view.
  • inserted holds new rows, deleted old rows; an UPDATE fills both.
  • Triggers fire once per statement: never copy inserted or deleted into variables; write set-based code.
  • Start with IF @@ROWCOUNT = 0 RETURN; then SET NOCOUNT ON.
  • AFTER triggers run inside the statement’s transaction: THROW in a trigger undoes the change and ends the batch.
  • INSTEAD OF triggers replace the statement; they make complex views writable.
  • sys.triggers lists triggers; DISABLE / ENABLE TRIGGER switch them off and on.
  • Prefer constraints, OUTPUT or temporal tables when they do the job; triggers are hidden and add cost.
0 of 54 lessons completed