<aside> ⚡ A trigger is a special stored procedure that automatically fires in response to an event on a table (INSERT, UPDATE, DELETE). You don't call it — SQL Server calls it for you.

</aside>

How Triggers Work

• AFTER INSERT — fires after a row is inserted
• AFTER UPDATE — fires after a row is updated
• AFTER DELETE — fires after a row is deleted
• INSTEAD OF — fires in place of the original operation

Inside a trigger, two special virtual tables are available:

• INSERTED — contains the new rows (used in INSERT and UPDATE triggers)
• DELETED — contains the old rows (used in DELETE and UPDATE triggers)

Step-by-Step Example — Audit Logging Trigger

Step 1: Create the Log Table

CREATE TABLE Sales.EmployeeLogs
(
    LogID      INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    LogMessage VARCHAR(255),
    LogDate    DATE
);
GO

Step 2: Create the Trigger

This trigger fires automatically every time a new employee is inserted into Sales.Employees.

CREATE TRIGGER trg_AfterInsertEmployee
ON Sales.Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO Sales.EmployeeLogs (EmployeeID, LogMessage, LogDate)
    SELECT
        EmployeeID,
        'New Employee Added = ' + CAST(EmployeeID AS VARCHAR),
        GETDATE()
    FROM INSERTED;  -- INSERTED holds the newly added row(s)
END;
GO

Step 3: Test It — Insert a New Employee

INSERT INTO Sales.Employees
VALUES (6, 'Maria', 'Doe', 'HR', '1988-01-12', 'F', 80000, 3);
GO

Step 4: Check the Log