Triggers

A trigger is a stored block of T-SQL that fires automatically in response to events. SQL Server has three families: DML triggers (on tables/views), DDL triggers (on schema and server events), and logon triggers (on session start).

DML Triggers

Fire on INSERT, UPDATE, or DELETE. Two timing options:

Inserted and Deleted Virtual Tables

Triggers expose two pseudo-tables:

These tables hold all rows affected by the statement — not one row at a time. Triggers must be written set-based:

CREATE OR ALTER TRIGGER hr.trg_employees_audit
ON hr.employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- INSERTs and UPDATEs land in `inserted`
    INSERT INTO hr.audit_log (table_name, action, employee_id, salary, changed_at)
    SELECT N'employees',
           CASE WHEN EXISTS (SELECT 1 FROM deleted) THEN N'UPDATE' ELSE N'INSERT' END,
           i.employee_id,
           i.salary,
           SYSDATETIME()
    FROM   inserted i;

    -- DELETEs land in `deleted` (and `inserted` is empty)
    INSERT INTO hr.audit_log (table_name, action, employee_id, salary, changed_at)
    SELECT N'employees', N'DELETE', d.employee_id, d.salary, SYSDATETIME()
    FROM   deleted d
    WHERE  NOT EXISTS (SELECT 1 FROM inserted);
END;
GO

Detecting Operation Type

CREATE OR ALTER TRIGGER hr.trg_employees_change
ON hr.employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @action CHAR(1);

    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        SET @action = 'U';
    ELSE IF EXISTS (SELECT 1 FROM inserted)
        SET @action = 'I';
    ELSE
        SET @action = 'D';

    -- ... rest of trigger
END;
GO

The deprecated IF UPDATE(column) and IF COLUMNS_UPDATED() exist but are awkward; the pattern above (or comparing inserted to deleted) is cleaner.

INSTEAD OF Triggers

Used to make views updatable when the view spans multiple tables:

CREATE VIEW hr.v_employee_dept AS
SELECT  e.employee_id, e.first_name, e.last_name, e.salary,
        d.department_id, d.department_name
FROM    hr.employees   e
JOIN    hr.departments d ON d.department_id = e.department_id;
GO

CREATE OR ALTER TRIGGER hr.trg_v_employee_dept_update
ON hr.v_employee_dept
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Translate the UPDATE on the view to the underlying employees table
    UPDATE  e
    SET     e.first_name = i.first_name,
            e.last_name  = i.last_name,
            e.salary     = i.salary
    FROM    hr.employees e
    JOIN    inserted     i ON i.employee_id = e.employee_id;
END;
GO

UPDATE hr.v_employee_dept SET salary = 30000 WHERE employee_id = 100;

DDL Triggers

Fire on schema changes — CREATE TABLE, ALTER PROCEDURE, DROP USER, etc. Useful for audit and policy enforcement.

CREATE OR ALTER TRIGGER trg_ddl_audit
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML = EVENTDATA();

    INSERT INTO hr.ddl_audit (event_type, object_name, sql_text, changed_by, changed_at)
    SELECT  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)'),
            @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)'),
            @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'),
            ORIGINAL_LOGIN(),
            SYSDATETIME();
END;
GO

-- Server-scope (run on master)
CREATE OR ALTER TRIGGER trg_audit_logins
ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
    PRINT 'Login DDL detected: ' + EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)');
GO

Logon Triggers (Server-Level)

Fire when a session establishes:

CREATE OR ALTER TRIGGER trg_audit_logon
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    INSERT INTO master.dbo.logon_audit (login_name, host_name, logon_time)
    VALUES (ORIGINAL_LOGIN(), HOST_NAME(), SYSDATETIME());

    -- Optionally block a login by raising an error here
    -- IF ORIGINAL_LOGIN() = 'baduser' AND HOST_NAME() <> 'allowedhost'
    --     ROLLBACK;
END;
GO

Nested and Recursive Triggers

By default a trigger that fires DML on another table can cause that table's triggers to fire (nested). Recursive triggers — a trigger that affects its own table and re-fires itself — are off by default.

-- Nesting (server-wide, default = 1; max recursion depth 32)
EXEC sp_configure 'nested triggers', 1;
RECONFIGURE;

-- Recursive triggers per database (default = OFF)
ALTER DATABASE HR SET RECURSIVE_TRIGGERS ON;

Disabling and Dropping Triggers

DISABLE TRIGGER hr.trg_employees_audit ON hr.employees;
ENABLE TRIGGER  hr.trg_employees_audit ON hr.employees;

-- Disable all triggers on a table during a bulk load
DISABLE TRIGGER ALL ON hr.employees;

DROP TRIGGER hr.trg_employees_audit;

When to Avoid Triggers

Triggers are powerful but easy to misuse:

• Cascading writes — a trigger that fires on every UPDATE writing to another audited table can multiply IO.
• Hidden side effects — developers often forget triggers exist, leading to "ghost" updates / extra rows.
• Performance ceilings — every DML fires the trigger code, even bulk loads.
• Difficult debugging — failures in triggers cascade as cryptic errors at the call site.

Prefer alternatives where possible:

Best Practices

• Always write triggers set-based against inserted / deleted — never assume single-row.
• Begin with SET NOCOUNT ON; to avoid extra rows-affected messages confusing clients.
• Keep trigger logic short — long-running triggers extend every transaction.
• For audit, prefer OUTPUT ... INTO from the originating DML instead of an AFTER trigger.

Summary

• DML triggers fire on INSERT/UPDATE/DELETE; AFTER runs post-DML, INSTEAD OF replaces it.
• inserted and deleted virtual tables hold all affected rows — write set-based.
• DDL triggers audit/restrict schema changes via EVENTDATA().
• Logon triggers can block sessions — test with DAC available.
• Nested/recursive trigger settings control re-entry; default to off for recursion.
• Reach for triggers as a last resort; prefer constraints, OUTPUT INTO, and indexed views first.