DML Triggers in SQL Server: AFTER vs INSTEAD OF, and the inserted/deleted Tables

Chapter 5 asked “where do I enforce a business rule” and pointed to triggers whenever logic needs to reach across rows or tables — something CHECK constraints structurally can’t do. This is that tool, in full. A trigger is code that runs automatically in response to an INSERT/UPDATE/DELETE. Getting the two DML trigger types right — and understanding their special tables — matters a lot in production, because a trigger bug affects every write to the table, silently.

AFTER Trigger: Audit Logging

CREATE TRIGGER trg_Account_AuditBalance
ON dbo.Account
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(balance)
    BEGIN
        INSERT INTO dbo.AccountAudit (account_id, old_balance, new_balance)
        SELECT i.account_id, d.balance, i.balance
        FROM inserted i
        INNER JOIN deleted d ON d.account_id = i.account_id
        WHERE i.balance <> d.balance;
    END
END;

UPDATE(balance) is a trigger-specific function that returns true only if the balance column was included in the UPDATE’s SET list — a cheap early-exit that avoids doing audit work on updates that never touched the column you actually care about.

The Two Special Tables

On an UPDATE, both are populated simultaneously — exactly how the audit trigger above compares old vs. new balance by joining them together on the primary key. On a plain INSERT, only inserted has rows; on a plain DELETE, only deleted does.

The Bug That Bites in Production

Triggers fire once per statement, operating on the whole batch of affected rows, not once per row. A trigger written assuming only one row was updated (using a scalar variable instead of joining to inserted/deleted) will silently process only one arbitrary row and miss the rest of a multi-row UPDATE — with no error, just quietly incomplete auditing.

-- The exact bug: looks reasonable, is completely wrong for multi-row updates
CREATE TRIGGER trg_Bad_AuditBalance ON dbo.Account AFTER UPDATE AS
BEGIN
    DECLARE @id INT, @newBalance DECIMAL(10,2);
    SELECT @id = account_id, @newBalance = balance FROM inserted; -- only grabs ONE row
    INSERT INTO dbo.AccountAudit (account_id, new_balance) VALUES (@id, @newBalance);
END;

-- Prove the bug: update multiple rows in one statement, check the audit table
UPDATE dbo.Account SET balance = balance * 1.01 WHERE balance > 0; -- affects many rows
SELECT COUNT(*) FROM dbo.AccountAudit; -- only 1 row logged, not one per account updated

INSTEAD OF: Replacing the Operation Entirely

CREATE TRIGGER trg_Account_PreventDeleteIfFunded
ON dbo.Account
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM deleted WHERE balance > 0)
    BEGIN
        RAISERROR('Cannot delete an account with a positive balance.', 16, 1);
        RETURN;
    END
    DELETE FROM dbo.Account WHERE account_id IN (SELECT account_id FROM deleted);
END;

INSTEAD OF triggers replace the operation entirely — the original INSERT/UPDATE/DELETE never happens unless the trigger body performs it itself. This is the exact mechanism that makes a JOIN-based view (Chapter 6) updatable despite SQL Server’s own restrictions: an INSTEAD OF trigger on the view can manually split the write across the correct base tables, however that logic needs to work.