AFTER Triggers — Audit Logging and Cascading Operations

An AFTER trigger fires after the DML operation completes and data has been written to the table. This makes it ideal for audit logging (record what changed after the fact), maintaining denormalised counters (update CommentCount on Posts after a Comment is inserted), and cascading updates to related tables that foreign key cascades cannot handle. The key difference from INSTEAD OF: the data is already written when AFTER triggers fire — they add behaviour on top of the operation, not replace it.

AFTER Triggers for Audit and Denormalisation #

-- ── AFTER trigger — maintain denormalised CommentCount ────────────────────
CREATE OR ALTER TRIGGER trg_Comments_UpdatePostCount
ON dbo.Comments
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Handle INSERT — increment count for posts that got new comments
    UPDATE dbo.Posts
    SET    CommentCount = CommentCount + 1,
           UpdatedAt    = SYSUTCDATETIME()
    WHERE  Id IN (SELECT PostId FROM inserted);

    -- Handle DELETE — decrement count for posts that lost comments
    UPDATE dbo.Posts
    SET    CommentCount = CASE
                            WHEN CommentCount > 0 THEN CommentCount - 1
                            ELSE 0  -- never go negative
                          END,
           UpdatedAt    = SYSUTCDATETIME()
    WHERE  Id IN (SELECT PostId FROM deleted);
END;
GO

-- ── AFTER UPDATE trigger — audit log for Post changes ─────────────────────
CREATE OR ALTER TRIGGER trg_Posts_AuditChanges
ON dbo.Posts
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Log each changed post — compare inserted (new) vs deleted (old)
    INSERT INTO dbo.AuditLog (TableName, RecordId, Action, OldValues, NewValues, ChangedAt)
    SELECT
        'Posts',
        i.Id,
        'UPDATE',
        -- Capture old values as JSON
        (SELECT d.Title, d.Status, d.IsPublished, d.ViewCount
         FROM deleted d WHERE d.Id = i.Id
         FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        -- Capture new values as JSON
        (SELECT i.Title, i.Status, i.IsPublished, i.ViewCount
         FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        SYSUTCDATETIME()
    FROM inserted i;
END;
GO

-- ── Trigger order — when multiple triggers exist on the same table ─────────
-- Set the first-to-fire and last-to-fire triggers:
EXEC sp_settriggerorder
    @triggername = 'trg_Posts_AuditChanges',
    @order       = 'First',
    @stmttype    = 'UPDATE';

-- ── @@NESTLEVEL — detect trigger depth ────────────────────────────────────
-- Triggers can fire other triggers (nested triggers)
-- @@NESTLEVEL returns the current nesting depth (max = 32)
CREATE OR ALTER TRIGGER trg_Tags_Audit
ON dbo.Tags
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF @@NESTLEVEL > 1 RETURN;  -- prevent recursive or cascading audit loops

    INSERT INTO dbo.AuditLog (TableName, RecordId, Action, ChangedAt)
    SELECT 'Tags', ISNULL(i.Id, d.Id),
           CASE WHEN EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) THEN 'UPDATE'
                WHEN EXISTS(SELECT 1 FROM inserted) THEN 'INSERT'
                ELSE 'DELETE' END,
           SYSUTCDATETIME()
    FROM   (SELECT Id FROM inserted UNION SELECT Id FROM deleted) x(Id)
    LEFT   JOIN inserted i ON i.Id = x.Id
    LEFT   JOIN deleted  d ON d.Id = x.Id;
END;
GO

Common Mistakes #

Mistake 1 — Decrementing counter without bounds check (CommentCount goes negative) #

❌ Wrong — SET CommentCount = CommentCount - 1 on delete; if count is already 0, it goes to -1.

✅ Correct — SET CommentCount = CASE WHEN CommentCount > 0 THEN CommentCount - 1 ELSE 0 END.

Mistake 2 — Recursive trigger loop without @@NESTLEVEL guard #

❌ Wrong — UPDATE trigger updates a related table; that table’s trigger updates back; infinite loop until nesting limit (error 217).

✅ Correct — add IF @@NESTLEVEL > 1 RETURN to break recursive chains.