Triggers (Transact‑SQL)

Overview

A trigger is a special type of stored procedure that automatically executes when certain events occur in the database. Triggers can be used to enforce business rules, audit data modifications, or cascade changes.

Syntax

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
[ WITH { ENCRYPTION | SCHEMABINDING | EXECUTE AS Clause } ]
{ AFTER | INSTEAD OF } 
{ [INSERT] [,] [UPDATE] [,] [DELETE] }
[ NOT FOR REPLICATION ]
AS
BEGIN
    -- T‑SQL statements
END
GO

-- DDL Trigger example
CREATE TRIGGER trigger_name
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    -- DDL handling logic
END
GO

Trigger Types

AFTER – Fires after the triggering action completes.
INSTEAD OF – Fires instead of the triggering action.
DDL – Fires on Data Definition Language events (CREATE, ALTER, DROP, etc.).
LOGON – Fires when a session is established.

Best Practices

Keep trigger logic short and efficient.
Avoid long-running operations that can block user transactions.
Never use triggers for security enforcement — use permissions instead.
Document the purpose of each trigger clearly in comments.
Prefer INSTEAD OF for view modifications to control data flow.

Examples

1. Auditing INSERTs on a Sales table

CREATE TRIGGER trg_AuditSalesInsert
ON dbo.Sales
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.SalesAudit (SaleID, InsertedAt, InsertedBy)
    SELECT i.SaleID, SYSDATETIME(), SUSER_SNAME()
    FROM inserted i;
END
GO

2. INSTEAD OF trigger for a view

CREATE VIEW dbo.vEmployeeDept
AS
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM dbo.Employee e
JOIN dbo.Department d ON e.DeptID = d.DeptID;
GO

CREATE TRIGGER trg_UpsertEmployeeDept
ON dbo.vEmployeeDept
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    MERGE dbo.Employee AS target
    USING inserted AS src
    ON target.EmployeeID = src.EmployeeID
    WHEN MATCHED THEN
        UPDATE SET Name = src.Name,
                   DeptID = (SELECT DeptID FROM dbo.Department WHERE DepartmentName = src.DepartmentName)
    WHEN NOT MATCHED THEN
        INSERT (EmployeeID, Name, DeptID)
        VALUES (src.EmployeeID, src.Name,
                (SELECT DeptID FROM dbo.Department WHERE DepartmentName = src.DepartmentName));
END
GO