Lesson 15 — Programmability

Triggers

A trigger is special code that runs automatically in response to a data-modifying event on a table, such as an INSERT, UPDATE, or DELETE.

Creating an AFTER Trigger

An AFTER trigger runs once the triggering statement has completed. It has access to special inserted and deleted tables, which hold the new and old row values respectively, letting you log or react to exactly what changed.

T-SQL — An audit trigger 
CREATE TRIGGER trg_LogSalaryChange
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO SalaryChangeLog (EmployeeId, OldSalary, NewSalary, ChangedAt)
    SELECT i.EmployeeId, d.Salary, i.Salary, GETDATE()
    FROM inserted i
    INNER JOIN deleted d ON i.EmployeeId = d.EmployeeId
    WHERE i.Salary <> d.Salary;
END;

Common Mistakes

  • Assuming a trigger fires once per row — it actually fires once per statement, and the inserted/deleted tables may contain multiple rows.
  • Writing overly complex trigger logic that makes ordinary INSERT/UPDATE/DELETE statements unexpectedly slow.
  • Forgetting that triggers execute silently in the background, which can surprise developers unaware one exists on a table.
  • Using a trigger for logic that would be clearer and easier to maintain as an explicit stored procedure call.

Professional Tip

Because triggers run silently and invisibly whenever certain statements execute, document them clearly and keep their logic simple — a hidden, complex trigger is one of the hardest kinds of bugs to track down later.

Your Turn

Design a trigger that automatically inserts a row into an OrderAudit table whenever a row in the Orders table is deleted, capturing the deleted order's details.

Mini Quiz

How many times does a standard AFTER trigger fire for a single UPDATE statement that changes 50 rows at once?