Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 39 ยท Procedures, Triggers and Functions

Triggers in SQL Server

Learn SQL Server triggers: AFTER and INSTEAD OF DML triggers, the inserted and deleted tables, auditing, DDL and logon triggers, and the mistakes that make triggers slow.

A trigger is code that SQL Server runs automatically when something happens: a row is inserted, updated or deleted, a table is dropped, or someone logs in. You don't call it; the event does. Triggers are used for audit trails, keeping derived data in step, enforcing rules a constraint can't express, and making views updatable.

They are also easy to abuse. They run invisibly, inside the caller's transaction, and slow every write. This lesson shows how they work, how to write them correctly for multi-row statements and when to choose something else. For the portable idea, see the SQL triggers lesson.

DML triggers

A DML trigger belongs to one table (or view) and fires once per statement, not once per row. That is the single most important fact about T-SQL triggers. (Oracle and others have row-level triggers; SQL Server has none.)

SQL
CREATE OR ALTER TRIGGER dbo.trg_Products_Audit
ON dbo.Products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.ProductAudit (ProductId, OldPrice, NewPrice, ChangedBy, ChangedAt)
    SELECT COALESCE(i.ProductId, d.ProductId),
           d.Price,
           i.Price,
           SUSER_SNAME(),
           SYSUTCDATETIME()
    FROM   inserted AS i
    FULL   JOIN deleted AS d ON d.ProductId = i.ProductId
    WHERE  ISNULL(i.Price, -1) <> ISNULL(d.Price, -1);
END;

The inserted and deleted tables

Inside a trigger two virtual tables hold the affected rows:

Statement inserted deleted
INSERT new rows empty
DELETE empty removed rows
UPDATE rows after the change rows before the change

Because one statement can touch many rows, the trigger must be written with set-based code that joins to inserted/deleted. The classic bug is DECLARE @id int = (SELECT ProductId FROM inserted), which works for one row and breaks on a bulk update.

You can rehearse the pattern in SQLite, whose triggers use NEW and OLD for each row:

SQL An audit trigger (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The WHEN OLD.price <> NEW.price guard mirrors the WHERE in the T-SQL trigger: only real changes are logged.

AFTER vs INSTEAD OF

  • AFTER (also called FOR): runs after the statement and its constraint checks pass. The data is already changed; ROLLBACK inside the trigger undoes the statement. A table can have several AFTER triggers; sp_settriggerorder fixes only the first and last.
  • INSTEAD OF: runs in place of the statement. Nothing happens unless the trigger does it. Use it to make a complex view updatable, or to turn a delete into a soft delete.
SQL
CREATE OR ALTER TRIGGER dbo.trg_Customers_SoftDelete
ON dbo.Customers
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE c
    SET    c.IsDeleted = 1
    FROM   dbo.Customers AS c
    JOIN   deleted AS d ON d.CustomerId = c.CustomerId;
END;

SQLite supports INSTEAD OF on views, so the soft delete can be tried:

SQL INSTEAD OF trigger: soft delete

Press Run (or Ctrl + Enter) to see the result here.

Useful functions inside triggers

  • UPDATE(ColumnName) is true when the column was named in the SET (or INSERT), a cheap way to skip work.
  • COLUMNS_UPDATED() returns a bitmask for the same purpose.
  • @@ROWCOUNT at the top tells you how many rows were affected; IF @@ROWCOUNT = 0 RETURN; avoids running empty.
  • TRIGGER_NESTLEVEL() guards against recursion; by default triggers don't fire themselves (RECURSIVE_TRIGGERS is off).

DDL and logon triggers

DDL triggers react to CREATE, ALTER and DROP events, either database-wide or server-wide. They get the event details from EVENTDATA():

SQL
CREATE TRIGGER trg_NoDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    DECLARE @sql nvarchar(max) =
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
    PRINT 'Blocked: ' + @sql;
    ROLLBACK;
END;

Logon triggers run on every login and can restrict access (for example, limit sessions per login). A logon trigger with a bug can lock everyone out; the fix is the Dedicated Administrator Connection (sqlcmd -A). Test them carefully.

When not to use a trigger

Triggers hide logic, add cost to every write and surprise people. Prefer:

  • Constraints (CHECK, UNIQUE, foreign keys) for rules.
  • Computed columns for derived values.
  • System-versioned temporal tables (WITH (SYSTEM_VERSIONING = ON)) for history and audit instead of hand-made triggers.
  • Stored procedures for business workflows.
  • Change data capture or Change Tracking for replication-style needs.

Keep any trigger short, set-based and quiet: no cursors, no external calls, no user messages, no transaction juggling.

flowchart LR
  A["UPDATE statement"] --> B["Constraints checked"]
  B --> C["AFTER trigger runs once<br/>sees inserted and deleted"]
  C --> D["COMMIT or ROLLBACK"]
  A -. "INSTEAD OF trigger replaces the statement" .-> C

An AFTER trigger fires per statement, inside the same transaction.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How often does a SQL Server DML trigger fire for an UPDATE that changes 500 rows?

    SQL Server triggers are statement-level. The inserted and deleted tables contain all 500 rows.
  2. 2. What do the inserted and deleted tables contain for an UPDATE?

    An update is treated as a delete of the old row image plus an insert of the new image.
  3. 3. Which trigger type replaces the original statement?

    INSTEAD OF triggers run in place of the INSERT, UPDATE or DELETE; nothing changes unless the trigger does it.
  4. 4. What is a better tool than a hand-written trigger for keeping row history?

    Temporal tables record every old row version automatically, with no trigger code to maintain.

Summary

  • DML triggers are per statement; always write set-based code against inserted and deleted.
  • AFTER triggers run inside the transaction after the change; INSTEAD OF triggers replace it.
  • DDL and logon triggers guard schema and logins; test them with care.
  • Prefer constraints, temporal tables and procedures where they fit.

Next: Dynamic SQL and cursors