Day 19: Triggers

A. Lesson overview

Activity breakdown

Business scenario. Auditors want a record of every list-price change and every customer change — however the change is made, including ad hoc fixes. Sales want “deleted” customers kept for history. Operations want stock to go down automatically when order lines are recorded. Each request is a candidate for a trigger; each also has a way to go wrong when many rows change at once.

B. Theory explained clearly

Part 1 — What is a trigger?

A DML trigger is a special procedure attached to a table (or view) that runs automatically when an INSERT, UPDATE or DELETE statement affects that table. You never call it directly.

CREATE OR ALTER TRIGGER Lab.trg_Product_PriceAudit
ON Lab.Product
AFTER UPDATE                        -- or INSERT, DELETE, or a list: AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;                 -- stop extra "(n rows affected)" messages confusing client code
    -- logic here, using the inserted and deleted tables
END;

The single most important fact: a trigger fires once per statement, not once per row. An UPDATE that changes 500 rows fires the trigger once, with 500 rows to process. An UPDATE that changes 0 rows still fires it, with no rows.

There are also DDL triggers (fire on CREATE, ALTER, DROP and similar — today’s stretch) and logon triggers (server level, not covered).

Part 2 — inserted and deleted

Inside a DML trigger, two read-only tables with the same columns as the parent table are available:

To compare before and after for an update, join them on the primary key:

SELECT i.ProductID, d.ListPrice AS OldPrice, i.ListPrice AS NewPrice
FROM   inserted AS i
JOIN   deleted  AS d ON d.ProductID = i.ProductID;

(If a statement changes the primary key value itself, this join cannot match old to new. Treat primary keys as unchangeable — another reason for surrogate keys.)

The multi-row rule

This trigger looks reasonable and is wrong:

DECLARE @ProductID int, @NewPrice decimal(10,2);
SELECT  @ProductID = ProductID, @NewPrice = ListPrice FROM inserted;   -- keeps ONE row's values

When several rows change, the variable holds the values from just one of them (which one is not defined), and every other row is silently ignored. Single-row testing will never reveal it. Always write trigger logic as set-based statements over inserted/deleted, never by copying them into scalar variables.

Part 3 — AFTER vs INSTEAD OF

Errors and transactions. A trigger runs inside the transaction of the statement that fired it. If the trigger raises an error with THROW (or a statement inside it fails), the batch is aborted and the transaction is rolled back — the original change and everything the trigger did are undone. That is how a trigger rejects invalid data. Day 20 covers how callers catch such errors.

IF EXISTS (SELECT 1 FROM inserted WHERE …invalid…)
    THROW 50010, N'Explain the rule that was broken.', 1;

Constraints run before AFTER triggers. If a CHECK or foreign key fails, the AFTER trigger never runs.

Part 4 — Behaviours to know

When not to use a trigger

Triggers are invisible to whoever writes the UPDATE, add work to every change, and are easy to get wrong. Prefer:

Use a trigger when the rule must hold however the data is changed and none of the above can express it — for example, auditing selected columns, rules that look at another table, or soft deletes.

C. Dataset and starting state

Setup. Open and run scripts/lessons/day19_trigger_lab.sql (download it). It is destructive within Lab: it drops and recreates the six tables above (and therefore any triggers on them). Re-run it whenever you want to start again.

Validation. The script ends with this query. Expected: 30, 40, 37, 0, 29, 1142, 0, 0.

SELECT  (SELECT COUNT(*)            FROM Lab.Product)            AS Products,
        (SELECT COUNT(*)            FROM Lab.Customer)           AS Customers,
        (SELECT SUM(CAST(IsActive AS int)) FROM Lab.Customer)    AS ActiveCustomers,
        (SELECT COUNT(*)            FROM Lab.OrderLine)          AS OrderLines,
        (SELECT COUNT(*)            FROM Lab.ProductStock)       AS StockProducts,
        (SELECT SUM(QuantityOnHand) FROM Lab.ProductStock)       AS TotalUnits,
        (SELECT COUNT(*) FROM Lab.ProductPriceAudit) + (SELECT COUNT(*) FROM Lab.CustomerAudit) AS AuditRows,
        (SELECT COUNT(*) FROM sys.triggers AS t
         JOIN sys.tables AS tb ON tb.object_id = t.parent_id
         WHERE tb.schema_id = SCHEMA_ID('Lab'))                  AS LabTableTriggers;

D. Worked examples

Worked example 1 — The single-row bug

CREATE OR ALTER TRIGGER Lab.trg_Product_PriceAudit
ON Lab.Product
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- WRONG: assumes exactly one row changed
    DECLARE @ProductID int, @Old decimal(10,2), @New decimal(10,2);
    SELECT @ProductID = ProductID, @New = ListPrice FROM inserted;
    SELECT @Old = ListPrice FROM deleted WHERE ProductID = @ProductID;

    IF @Old <> @New
        INSERT INTO Lab.ProductPriceAudit (ProductID, OldListPrice, NewListPrice)
        VALUES (@ProductID, @Old, @New);
END;
GO

UPDATE Lab.Product
SET    ListPrice = ListPrice + 1.00
WHERE  ProductCode IN ('TNT-001', 'SLP-003', 'COK-003');

SELECT COUNT(*) AS AuditRows FROM Lab.ProductPriceAudit;

Expected result: (3 rows affected) from the UPDATE, but AuditRows = 1. Two price changes have no audit record. Which product was recorded is not defined — that unpredictability is part of the bug.

Worked example 2 — The set-based fix (and disabling a trigger)

CREATE OR ALTER TRIGGER Lab.trg_Product_PriceAudit
ON Lab.Product
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE(ListPrice) RETURN;          -- ListPrice not in the SET list: nothing to audit

    INSERT INTO Lab.ProductPriceAudit (ProductID, OldListPrice, NewListPrice)
    SELECT  i.ProductID, d.ListPrice, i.ListPrice
    FROM    inserted AS i
    JOIN    deleted  AS d ON d.ProductID = i.ProductID
    WHERE   i.ListPrice <> d.ListPrice;       -- only real changes (ListPrice is NOT NULL)
END;
GO

-- Put the prices back WITHOUT auditing the correction, then clear the audit
DISABLE TRIGGER Lab.trg_Product_PriceAudit ON Lab.Product;
UPDATE lp SET ListPrice = p.ListPrice
FROM   Lab.Product AS lp JOIN Inventory.Product AS p ON p.ProductID = lp.ProductID
WHERE  lp.ListPrice <> p.ListPrice;
ENABLE TRIGGER Lab.trg_Product_PriceAudit ON Lab.Product;
TRUNCATE TABLE Lab.ProductPriceAudit;

-- Test 1: three real changes
UPDATE Lab.Product SET ListPrice = ListPrice + 1.00 WHERE ProductCode IN ('TNT-001', 'SLP-003', 'COK-003');
-- Test 2: four rows "updated" but no price changes
UPDATE Lab.Product SET ListPrice = ListPrice WHERE CategoryID = 2;
-- Test 3: zero rows
UPDATE Lab.Product SET ListPrice = 1.00 WHERE 1 = 0;

SELECT a.ProductID, p.ProductCode, a.OldListPrice, a.NewListPrice
FROM   Lab.ProductPriceAudit AS a JOIN Lab.Product AS p ON p.ProductID = a.ProductID
ORDER BY a.ProductID;

Expected result: 3 audit rows — 1 TNT-001 189.00 190.00, 6 SLP-003 59.00 60.00, 9 COK-003 6.50 7.50. Test 2 (4 rows affected) and test 3 (0 rows affected) fired the trigger but added nothing. Leave the three +1.00 prices in place; later steps don’t depend on them.

Worked example 3 — Rejecting invalid rows with THROW

A CHECK constraint can’t look at another table, so “you can’t order a discontinued product” needs a trigger.

CREATE OR ALTER TRIGGER Lab.trg_OrderLine_CheckProduct
ON Lab.OrderLine
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1
               FROM   inserted AS i
               LEFT JOIN Lab.Product AS p ON p.ProductID = i.ProductID
               WHERE  p.ProductID IS NULL OR p.DiscontinuedDate IS NOT NULL)
        THROW 50010, N'Order lines must reference an existing product that is not discontinued.', 1;
END;
GO

INSERT INTO Lab.OrderLine (OrderID, ProductID, Quantity, UnitPrice)
VALUES (9001, 1, 2, 189.00),
       (9001, 30, 1, 599.00);      -- product 30 = TNT-004, discontinued

SELECT COUNT(*) AS OrderLines FROM Lab.OrderLine;

Expected result: Msg 50010 … Order lines must reference an existing product that is not discontinued. Then OrderLines = 0 — the valid first row was rolled back too, because the whole statement is one transaction. (The identity values used by the failed insert are not reused, so later OrderLineIDs may start above 1.)

The error ends the batch, so run the SELECT again on its own to see the count.

Worked example 4 — INSTEAD OF DELETE for soft deletes

CREATE OR ALTER TRIGGER Lab.trg_Customer_SoftDelete
ON Lab.Customer
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE c
    SET    IsActive = 0
    FROM   Lab.Customer AS c
    JOIN   deleted      AS d ON d.CustomerID = c.CustomerID
    WHERE  c.IsActive = 1;
END;
GO

DELETE FROM Lab.Customer WHERE CustomerID IN (38, 39, 40);

SELECT COUNT(*) AS Customers, SUM(CAST(IsActive AS int)) AS ActiveCustomers FROM Lab.Customer;
SELECT CustomerID, FirstName, LastName, IsActive FROM Lab.Customer WHERE CustomerID >= 38 ORDER BY CustomerID;

Expected result: Customers 40, ActiveCustomers 34; Freya Lindqvist, Arjun Mehta and Hugo Bernard are still there with IsActive 0. The DELETE never removed anything — deleted told the trigger which rows the statement targeted.

E. Guided practical lab — A complete customer audit

Build one trigger that audits inserts, updates and deletes on Lab.Customer, test it with multi-row statements, and see how it interacts with the soft-delete trigger.

Step 1 — Create the audit trigger

1. Do: One trigger for all three actions. A FULL OUTER JOIN of inserted and deleted tells you the action per row.
2. Run:

   USE RetailLab;
   GO
   
   CREATE OR ALTER TRIGGER Lab.trg_Customer_Audit
   ON Lab.Customer
   AFTER INSERT, UPDATE, DELETE
   AS
   BEGIN
       SET NOCOUNT ON;
   
       INSERT INTO Lab.CustomerAudit
             (AuditAction, CustomerID, OldEmail, NewEmail, OldSegment, NewSegment, OldIsActive, NewIsActive)
       SELECT  CASE WHEN d.CustomerID IS NULL THEN 'I'
                    WHEN i.CustomerID IS NULL THEN 'D'
                    ELSE 'U' END,
               COALESCE(i.CustomerID, d.CustomerID),
               d.Email, i.Email, d.CustomerSegment, i.CustomerSegment, d.IsActive, i.IsActive
       FROM    inserted AS i
       FULL OUTER JOIN deleted AS d ON d.CustomerID = i.CustomerID
       WHERE   d.CustomerID IS NULL                                  -- insert
          OR   i.CustomerID IS NULL                                  -- delete
          OR   i.Email           IS DISTINCT FROM d.Email            -- update of an audited column
          OR   i.CustomerSegment IS DISTINCT FROM d.CustomerSegment
          OR   i.IsActive        IS DISTINCT FROM d.IsActive;
   END;
   GO

3. Observe: Commands completed successfully.
4. Why it matters: One set-based statement handles any number of rows and all three actions. IS DISTINCT FROM treats NULL → value and value → NULL as changes (Email allows NULL), which <> would miss.
5. Confirm: SELECT name, is_instead_of_trigger FROM sys.triggers WHERE parent_id = OBJECT_ID('Lab.Customer'); → trg_Customer_Audit 0 and trg_Customer_SoftDelete 1.

Step 2 — Multi-row insert

1. Do: Add two customers from the import in one statement.
2. Run:

   INSERT INTO Lab.Customer
          (CustomerID, FirstName, LastName, Email, Phone, City, Country, CustomerSegment, DateJoined, IsActive)
   VALUES (41, N'Daisy', N'Holt',   N'daisy.holt@example.com',  '07700 900301', N'Bath',   N'United Kingdom', 'Consumer', '2026-01-02', 1),
          (42, N'Kofi',  N'Mensah', N'kofi.mensah@example.com', NULL,           N'London', N'United Kingdom', 'Consumer', '2026-01-02', 1);
   
   SELECT AuditAction, CustomerID, NewEmail, NewSegment, NewIsActive
   FROM   Lab.CustomerAudit ORDER BY AuditID;

3. Observe: two I rows, for 41 and 42.
4. Why it matters: Proves the trigger is not limited to one row.
5. Confirm: the old-value columns are NULL for inserts.

Step 3 — Multi-row update, and an update that changes nothing

1. Do: Upgrade London business customers to VIP; then run an update that touches rows without changing audited columns.
2. Run:

   UPDATE Lab.Customer SET CustomerSegment = 'VIP' WHERE CustomerSegment = 'Business' AND City = N'London';
   UPDATE Lab.Customer SET City = City            WHERE Country = N'Ireland';
   
   SELECT AuditAction, CustomerID, OldSegment, NewSegment
   FROM   Lab.CustomerAudit WHERE AuditAction = 'U' ORDER BY CustomerID;

3. Observe: (2 rows affected) then (4 rows affected); the audit shows 2 U rows: customers 13 and 39, Business → VIP. The Ireland update produced nothing.
4. Why it matters: Customer 39 is inactive (soft-deleted in worked example 4) but was still updated — the WHERE didn’t filter on IsActive. Audits capture what actually happened, not what was intended.
5. Confirm: SELECT COUNT(*) FROM Lab.CustomerAudit; → 4.

Step 4 — Delete, and watch the triggers interact

1. Do: Delete the two new customers.
2. Run:

   DELETE FROM Lab.Customer WHERE CustomerID IN (41, 42);
   
   SELECT AuditAction, CustomerID, OldIsActive, NewIsActive
   FROM   Lab.CustomerAudit WHERE CustomerID IN (41, 42) ORDER BY AuditID;

3. Observe: For each customer: the I row from step 2, then a U row with IsActive 1 → 0. No D rows.
4. Why it matters: The INSTEAD OF DELETE trigger replaced the delete with an UPDATE, and that UPDATE fired the AFTER audit trigger (a nested trigger). The audit correctly records what really happened: a soft delete.
5. Confirm: SELECT COUNT(*) FROM Lab.Customer; → 42.

Step 5 — Summarise

1. Do: Check the totals.
2. Run:

   SELECT AuditAction, COUNT(*) AS AuditRows FROM Lab.CustomerAudit GROUP BY AuditAction ORDER BY AuditAction;
   SELECT COUNT(*) AS Customers, SUM(CAST(IsActive AS int)) AS ActiveCustomers FROM Lab.Customer;

3. Observe: I 2, U 4; 42 customers, 34 active.
4. Why it matters: Totals you can predict before running are the best test of trigger logic.
5. Confirm: the numbers match.

F. Independent practice

Tasks 1 and 2 add triggers to Lab.Product, where trg_Product_PriceAudit already exists; task 3 works on Lab.OrderLine.

Task 1 — Maintain a LastModified column

Add LastModified datetime2(0) NULL to Lab.Product. Create Lab.trg_Product_Touch (AFTER UPDATE) that sets it to the current date and time for every updated row. Make sure the trigger cannot loop, even if RECURSIVE_TRIGGERS were turned on. Test with one statement that raises the prices of TNT-002 and TNT-003 by 10.00.

Success criteria: exactly 2 products have a LastModified value, 28 are NULL; Lab.ProductPriceAudit gains exactly 2 rows from your test.

Task 2 — Block large price rises

Create Lab.trg_Product_PriceGuard (AFTER UPDATE) that rejects the whole statement with error 50020 if any row’s new list price is more than 20% above its old price. Test with ListPrice * 1.15 and then ListPrice * 1.25 for the Navigation category (CategoryID = 10).

Success criteria: the first update succeeds (4 rows); the second fails with error 50020; afterwards NAV-001 28.74, NAV-002 343.85, NAV-003 17.24, NAV-004 401.35. The audit contains 4 rows for the Navigation products from the first update and none from the second.

Task 3 — Decrease stock as order lines arrive (challenging)

Create Lab.trg_OrderLine_Stock (AFTER INSERT) that reduces Lab.ProductStock.QuantityOnHand by the quantity ordered. Test with one statement inserting four lines: order 9001 — product 1 × 2, product 6 × 1; order 9002 — product 1 × 3, product 24 × 4. Then try one more line: order 9003, product 6 × 5.

Success criteria: after the first insert: product 1 = 64, product 6 = 1, product 24 = 52, total units 1132, 4 order lines. The second insert fails with a CHECK constraint error (product 6 would go negative); stock and order lines are unchanged.

Stretch challenge — A database-level DDL audit

Create Lab.SchemaChangeLog (LogID int IDENTITY PRIMARY KEY, EventTime datetime2(0), EventType nvarchar(100), ObjectName nvarchar(256), LoginName sysname, CommandText nvarchar(max)) and a database trigger trg_DDL_SchemaLog for DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_PROCEDURE_EVENTS and DDL_FUNCTION_EVENTS that records each event from EVENTDATA(). Test by creating Lab.DdlTest, adding a column, and dropping it. Drop the DDL trigger afterwards — otherwise it will log everything you do for the rest of the course.

Success criteria: 3 log rows: CREATE_TABLE, ALTER_TABLE, DROP_TABLE, each with ObjectName Lab.DdlTest.

G. Solutions and explanations

ALTER TABLE Lab.Product ADD LastModified datetime2(0) NULL;
GO

CREATE OR ALTER TRIGGER Lab.trg_Product_Touch
ON Lab.Product
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(LastModified) RETURN;     -- the statement (or this trigger's own UPDATE) set it already

    UPDATE p
    SET    LastModified = SYSDATETIME()
    FROM   Lab.Product AS p
    JOIN   inserted    AS i ON i.ProductID = p.ProductID;
END;
GO

UPDATE Lab.Product SET ListPrice = ListPrice + 10.00 WHERE ProductCode IN ('TNT-002', 'TNT-003');

SELECT SUM(CASE WHEN LastModified IS NOT NULL THEN 1 ELSE 0 END) AS Touched,
       SUM(CASE WHEN LastModified IS NULL     THEN 1 ELSE 0 END) AS Untouched
FROM   Lab.Product;

SELECT a.ProductID, a.OldListPrice, a.NewListPrice
FROM   Lab.ProductPriceAudit AS a WHERE a.ProductID IN (2, 3) ORDER BY a.ProductID;

CREATE OR ALTER TRIGGER Lab.trg_Product_PriceGuard
ON Lab.Product
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE(ListPrice) RETURN;

    IF EXISTS (SELECT 1
               FROM   inserted AS i
               JOIN   deleted  AS d ON d.ProductID = i.ProductID
               WHERE  i.ListPrice > d.ListPrice * 1.20)
        THROW 50020, N'A list price cannot rise by more than 20% in one change.', 1;
END;
GO

UPDATE Lab.Product SET ListPrice = ListPrice * 1.15 WHERE CategoryID = 10;   -- succeeds
GO
UPDATE Lab.Product SET ListPrice = ListPrice * 1.25 WHERE CategoryID = 10;   -- error 50020
GO
SELECT ProductCode, ListPrice FROM Lab.Product WHERE CategoryID = 10 ORDER BY ProductCode;
SELECT COUNT(*) AS NavAuditRows FROM Lab.ProductPriceAudit WHERE ProductID IN (21, 22, 23, 29);

CREATE OR ALTER TRIGGER Lab.trg_OrderLine_Stock
ON Lab.OrderLine
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s
    SET    QuantityOnHand = s.QuantityOnHand - x.Qty
    FROM   Lab.ProductStock AS s
    JOIN  (SELECT ProductID, SUM(Quantity) AS Qty
           FROM   inserted
           GROUP BY ProductID) AS x ON x.ProductID = s.ProductID;
END;
GO

INSERT INTO Lab.OrderLine (OrderID, ProductID, Quantity, UnitPrice)
VALUES (9001,  1, 2, 189.00),
       (9001,  6, 1,  59.00),
       (9002,  1, 3, 189.00),
       (9002, 24, 4,  39.00);
GO
INSERT INTO Lab.OrderLine (OrderID, ProductID, Quantity, UnitPrice)
VALUES (9003, 6, 5, 59.00);                  -- fails: stock would be -4
GO
SELECT ProductID, QuantityOnHand FROM Lab.ProductStock WHERE ProductID IN (1, 6, 24) ORDER BY ProductID;
SELECT SUM(QuantityOnHand) AS TotalUnits, (SELECT COUNT(*) FROM Lab.OrderLine) AS OrderLines FROM Lab.ProductStock;

DROP TABLE IF EXISTS Lab.SchemaChangeLog;
CREATE TABLE Lab.SchemaChangeLog
(
    LogID       int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EventTime   datetime2(0)  NOT NULL DEFAULT (SYSDATETIME()),
    EventType   nvarchar(100) NOT NULL,
    ObjectName  nvarchar(256) NULL,
    LoginName   sysname       NOT NULL,
    CommandText nvarchar(max) NULL
);
GO

CREATE OR ALTER TRIGGER trg_DDL_SchemaLog
ON DATABASE
FOR DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_PROCEDURE_EVENTS, DDL_FUNCTION_EVENTS
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @e xml = EVENTDATA();        -- one event per firing, so a variable is correct here

    INSERT INTO Lab.SchemaChangeLog (EventType, ObjectName, LoginName, CommandText)
    VALUES (@e.value('(/EVENT_INSTANCE/EventType)[1]',  'nvarchar(100)'),
            @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)') + N'.'
          + @e.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
            ORIGINAL_LOGIN(),
            @e.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'));
END;
GO

CREATE TABLE Lab.DdlTest (ID int NOT NULL);
ALTER TABLE  Lab.DdlTest ADD Note nvarchar(50) NULL;
DROP TABLE   Lab.DdlTest;
GO

SELECT LogID, EventType, ObjectName, LoginName FROM Lab.SchemaChangeLog ORDER BY LogID;
GO

-- Required cleanup
DROP TRIGGER trg_DDL_SchemaLog ON DATABASE;

H. Knowledge check

1. Understanding. An UPDATE changes 250 rows of a table with an AFTER UPDATE trigger. How many times does the trigger fire, and how many rows are in inserted?

2. Debugging. An audit trigger passed every test, but auditors find that a nightly job that updates prices in bulk leaves only one audit row per run. What is the most likely bug?

3. Query interpretation. Inside an AFTER UPDATE trigger, UPDATE(ListPrice) returns true, yet no price changed. How is that possible, and what should the trigger do about it?

4. Understanding. Order lines are inserted together with a row that breaks a trigger’s rule, and the trigger runs THROW. Which rows remain in the table, and what happens to rows the trigger had already written to an audit table?

5. Understanding. For each requirement choose a trigger, a constraint or a temporal table: (a) Quantity must be positive; (b) keep every past version of every product row; (c) “deleting” a customer must only mark them inactive.

I. Troubleshooting and cleanup

Cleanup. Make sure the DDL trigger from the stretch is gone, then reset the Lab copies. Dropping a table drops its triggers.

IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg_DDL_SchemaLog' AND parent_class_desc = 'DATABASE')
    DROP TRIGGER trg_DDL_SchemaLog ON DATABASE;
DROP TABLE IF EXISTS Lab.SchemaChangeLog, Lab.OrderLine, Lab.ProductStock,
                     Lab.ProductPriceAudit, Lab.CustomerAudit, Lab.Product;

Lab.Customer is left in place; Day 20 starts by re-running the Day 17 setup, which replaces it with a fresh copy without triggers. Core data is unchanged.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 showed 1 audit row for 3 changed prices; the fixed trigger recorded all 3.
• The discontinued-product insert failed and left 0 order lines.
• The soft-delete left 40 customers, 34 active.
• Your customer audit ended with I 2, U 4, 42 customers, 34 active.
• Tasks 1–3 match the success criteria; the DDL trigger has been dropped.

Recap

• Triggers fire once per statement; inserted and deleted hold all affected rows — 0, 1 or many.
• Never copy inserted into scalar variables; aggregate before updating other tables.
• AFTER triggers see the completed change; INSTEAD OF triggers replace it.
• THROW in a trigger rolls back the statement and everything the triggers did.
• UPDATE(col) means “in the SET list”, not “changed”; compare inserted with deleted.
• Prefer constraints, temporal tables and procedures when they can do the job; triggers are hidden logic.

Next: Day 20 — Error handling: TRY…CATCH, THROW, XACT_STATE() and XACT_ABORT, applied to the Day 17 procedures.

References (Microsoft Learn)

• CREATE TRIGGER (Transact-SQL)
• Use the inserted and deleted tables
• DML triggers
• DDL triggers
• UPDATE() (Transact-SQL)
• IS [NOT] DISTINCT FROM (Transact-SQL)
• EVENTDATA (Transact-SQL)