Triggers

Learn AFTER and INSTEAD OF triggers in SQL Server with a simple audit-logging example, and when to use - or avoid - triggers.

Introduction

A trigger is a special kind of stored code that runs automatically in response to an INSERT, UPDATE, or DELETE against a table or view - nobody has to call it explicitly. Triggers are powerful for enforcing rules and capturing history that must never be skipped, but they are also easy to overuse in ways that make a database's behavior hard to trace. This lesson covers AFTER and INSTEAD OF triggers, with a practical audit-logging example.

What You Will Learn

What a trigger is and how it differs from a procedure.
How AFTER triggers work, and the special Inserted/Deleted tables.
How to build a simple audit-logging trigger.
What INSTEAD OF triggers do differently.
When triggers are the right tool - and when they are not.

What Is a Trigger?

Triggers are attached to a table (or view) and a specific event - INSERT, UPDATE, or DELETE. SQL Server fires the trigger automatically whenever that event happens, as part of the same transaction as the triggering statement. This makes triggers useful for things application code could forget to do, like writing an audit trail, but it also means trigger logic is invisible to anyone just reading the INSERT or UPDATE statement that fired it.

AFTER Triggers

An AFTER trigger (also called a FOR trigger) runs once the triggering action has already happened against the base table, but still inside the same transaction - so a ROLLBACK inside the trigger undoes the original change too. AFTER triggers can only be defined on tables, not views.

CREATE TABLE dbo.Employees (
    EmployeeID   INT IDENTITY(1,1) PRIMARY KEY,
    FirstName    VARCHAR(50) NOT NULL,
    LastName     VARCHAR(50) NOT NULL,
    Salary       DECIMAL(10,2) NOT NULL,
    DepartmentID INT NOT NULL
);

CREATE TABLE dbo.EmployeeAudit (
    AuditID      INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID   INT NOT NULL,
    OldSalary    DECIMAL(10,2) NULL,
    NewSalary    DECIMAL(10,2) NULL,
    ChangedAt    DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    ChangedBy    SYSNAME NOT NULL DEFAULT SUSER_SNAME()
);

The Inserted and Deleted Tables

Inside a trigger, SQL Server automatically exposes two special, in-memory tables: Inserted, which holds the new row values (for INSERT and UPDATE), and Deleted, which holds the old row values (for UPDATE and DELETE). For an UPDATE, both tables are populated - Deleted has the row before the change, Inserted has it after.

Event	Inserted Table	Deleted Table
INSERT	New rows	Empty
UPDATE	New (post-update) values	Old (pre-update) values
DELETE	Empty	Removed rows

Audit-Logging Example

This AFTER UPDATE trigger writes a row to EmployeeAudit every time an employee's salary changes, capturing both the old and new values.

CREATE TRIGGER trg_Employees_AuditSalary
ON dbo.Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(Salary)
    BEGIN
        INSERT INTO dbo.EmployeeAudit (EmployeeID, OldSalary, NewSalary)
        SELECT
            i.EmployeeID,
            d.Salary,
            i.Salary
        FROM Inserted i
        JOIN Deleted d ON d.EmployeeID = i.EmployeeID
        WHERE i.Salary <> d.Salary;
    END
END;
GO

UPDATE dbo.Employees SET Salary = 82000 WHERE EmployeeID = 3;

SELECT * FROM dbo.EmployeeAudit;

Result

Click Run to see what this code prints.

IF UPDATE(column)

IF UPDATE(Salary) checks whether the Salary column was part of the UPDATE statement's SET list, letting you skip the audit logic entirely when an unrelated column changed.

INSTEAD OF Triggers

An INSTEAD OF trigger replaces the triggering action entirely - the original INSERT, UPDATE, or DELETE never actually runs against the base table unless the trigger's own code performs it. This is especially useful on views that are not normally updatable, letting you define custom logic for what an INSERT against the view should really do.

CREATE TRIGGER trg_vwOrderSummary_InsteadOfDelete
ON dbo.vwOrderSummary
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- Redirect deletes on the view into a soft-delete on the base table
    UPDATE o
    SET o.Status = 'Cancelled'
    FROM dbo.Orders o
    JOIN Deleted d ON d.OrderID = o.OrderID;
END;
GO

When to Use (and Avoid) Triggers

Good Fit	Poor Fit
Mandatory audit trails that must never be skipped	Business logic better placed in a stored procedure the app calls explicitly
Enforcing cross-row rules constraints cannot express	Anything performance-critical in a high-throughput OLTP table
Redirecting writes on a non-updatable view	Cascading changes across many tables (hard to trace, easy to create loops)

Triggers Are Invisible

Because triggers fire automatically, a developer looking at an application's INSERT statement has no way to know a trigger will also run, unless they specifically check the table. Overusing triggers is one of the most common causes of 'mystery' behavior in a database - use them sparingly and document them clearly.

Common Mistakes

Avoid These Mistakes

Assuming a trigger only fires for single-row changes - Inserted and Deleted can hold many rows from one multi-row statement.
Writing row-by-row logic (like a cursor) inside a trigger instead of set-based logic against Inserted/Deleted.
Creating multiple triggers on the same table and event with unpredictable execution order.
Building recursive or chained triggers that fire each other, sometimes infinitely.
Forgetting that an unhandled error inside a trigger rolls back the entire triggering statement.

Best Practices

Write triggers to handle multi-row Inserted/Deleted sets, never assume exactly one row.
Keep trigger logic short, fast, and set-based.
Document every trigger clearly, since they are not visible from the calling statement.
Prefer constraints (CHECK, FOREIGN KEY) over triggers when a simple constraint can enforce the same rule.
Avoid nested or recursive triggers unless absolutely necessary, and test them carefully if you do.

Frequently Asked Questions

Can a table have more than one trigger for the same event?

Yes, SQL Server supports multiple triggers per event, but their firing order is only loosely controllable (via sp_settriggerorder for 'first' and 'last'), so it is best to keep it to one trigger per event where possible.

Do triggers run inside the same transaction as the statement that fired them?

Yes. If the trigger raises an error or issues a ROLLBACK, the original INSERT/UPDATE/DELETE is undone along with it.

What is the difference between AFTER and INSTEAD OF?

AFTER runs once the change has already been applied to the table; INSTEAD OF runs in place of the change, so the original action only happens if the trigger's own code performs it.

Can INSTEAD OF triggers be used on tables, not just views?

Yes, though they are most commonly associated with views since that is where they solve the 'this view is not updatable' problem.

Key Takeaways

Triggers run automatically in response to INSERT, UPDATE, or DELETE.
Inserted and Deleted are special in-memory tables that hold the affected rows.
AFTER triggers run after the change; INSTEAD OF triggers replace the change entirely.
Triggers are ideal for mandatory audit trails but easy to overuse for regular business logic.
Trigger logic should be set-based and handle multi-row operations correctly.

Summary

Triggers let SQL Server react automatically to data changes, which is powerful for things like audit trails but should be used sparingly. Next, you will look at transactions, which give you explicit control over grouping multiple statements into one all-or-nothing unit of work.

Next Lesson →