SQL Triggers Overview

Triggers are special types of stored procedures that automatically execute in response to specific events on a particular table or view. They are commonly used to enforce business rules, maintain audit trails, and synchronize tables.

Trigger Types

• AFTER (FOR) Triggers – Execute after the triggering DML statement has completed.
• INSTEAD OF Triggers – Replace the triggering action, primarily used with views.
• DDL Triggers – Fire in response to Data Definition Language events (CREATE, ALTER, DROP).
• LOGON Triggers – Execute when a user session is established.

Basic Syntax

CREATE TRIGGER [schema_name.]trigger_name
ON { table | view }
[WITH ]
{ AFTER | INSTEAD OF } 
{ [INSERT] [,] [UPDATE] [,] [DELETE] }
AS
BEGIN
    -- T-SQL statements
END;

Example: Auditing Changes

CREATE TABLE dbo.Employee (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary MONEY
);

CREATE TABLE dbo.EmployeeAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    ChangeDate DATETIME DEFAULT GETDATE(),
    ChangedBy SYSNAME,
    Action NVARCHAR(10),
    OldSalary MONEY NULL,
    NewSalary MONEY NULL
);

CREATE TRIGGER trg_Employee_Audit
ON dbo.Employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Inserted rows (new values)
    INSERT INTO dbo.EmployeeAudit (EmployeeID, Action, OldSalary, NewSalary, ChangedBy)
    SELECT i.EmployeeID,
           CASE 
                WHEN d.EmployeeID IS NULL THEN 'INSERT'
                WHEN i.EmployeeID IS NULL THEN 'DELETE'
                ELSE 'UPDATE' END,
           d.Salary,
           i.Salary,
           ORIGINAL_LOGIN()
    FROM   inserted i
    FULL   OUTER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;

This trigger records every insert, update, and delete on the Employee table, storing who made the change and the salary values before and after the operation.

Performance Considerations

• Avoid complex logic inside triggers; keep them lightweight.
• Beware of recursive triggers; control recursion with RECURSIVE_TRIGGERS option.
• Use SET NOCOUNT ON to reduce unnecessary network traffic.
• Test trigger impact on bulk operations (e.g., BULK INSERT).

Further Reading

• Creating Triggers
• Altering Triggers
• Dropping Triggers
• Trigger Best Practices
• Microsoft Docs – Triggers