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.

DDL and Logon Triggers in SQL Server: Auditing Schema Changes and Login Restrictions

Beyond DML, SQL Server triggers can fire on schema changes (CREATE/ALTER/DROP — Fundamentals Chapter 2’s DDL statements) and even login attempts — powerful, and in the case of logon triggers, genuinely risky if you get it wrong, in a way DML triggers never are.

DDL Trigger: Auditing Schema Changes

CREATE TRIGGER trg_LogSchemaChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @data XML = EVENTDATA();
    INSERT INTO dbo.SchemaChangeLog (event_type, object_name, changed_by)
    VALUES (
        @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(200)'),
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)')
    );
END;

EVENTDATA() returns an XML document describing exactly what changed and who changed it — a genuinely useful audit trail for compliance-sensitive environments, where “who dropped that table, and when” is a question that needs a real answer, not a guess from backup timestamps.

-- Trigger it and see the audit row appear
CREATE TABLE dbo.Scratch_DDLTest (id INT);
SELECT * FROM dbo.SchemaChangeLog ORDER BY changed_by DESC; -- your CREATE TABLE is logged
DROP TABLE dbo.Scratch_DDLTest;

Logon Triggers: Powerful, and Genuinely Dangerous

A logon trigger fires when a login session is established — used for things like restricting logins by time of day or capping concurrent sessions. Unlike every other trigger type in this chapter, a logon trigger sits between you and the ability to connect at all — an error in its logic, or a bug that always evaluates to “deny,” locks out every single login attempt, with no normal way back in.

CREATE TRIGGER trg_RestrictOffHoursLogin ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS
BEGIN
    IF DATEPART(HOUR, GETDATE()) NOT BETWEEN 6 AND 22
       AND ORIGINAL_LOGIN() NOT IN ('sa', 'app_admin')
        ROLLBACK; -- rejects the connection
END;

Test extremely carefully, typically with that recovery plan via DAC in case something goes wrong — this is one of the very few features in this entire course where “just try it and see” is genuinely bad advice.