T-SQL Triggers Introduction

Master T-SQL triggers — DML, DDL, and logon triggers. Comprehensive overview of trigger types, AFTER vs INSTEAD OF timing, the inserted/deleted pseudo-tables, the critical 'fires once per statement' rule, advantages and disadvantages, and when to use triggers vs alternatives like CHECK constraints.

A trigger is a special kind of stored procedure that the database engine runs automatically in response to specific events — without anyone calling it directly. Each trigger is bound to a particular table, view, database, or server, and fires when something happens to it (a row is inserted, a table is dropped, a user logs in).

You can think of a trigger as a piece of code that says "whenever X happens, also do this." The database guarantees the code runs every time, regardless of which application or user caused the event.

DML triggers respond to data changes on tables and views. They have access to two pseudo-tables — inserted and deleted — that hold the affected rows.

DML Trigger Timings

• AFTER trigger (AFTER or its synonym FOR) — fires after the DML statement executes but before the surrounding transaction commits. Most common; used for auditing, cascading updates, and post-change validation.
• INSTEAD OF trigger — fires instead of the DML statement, replacing it. Used to make complex views updatable, or to redirect an operation to a different table.

Common Use Cases

• Audit logging (who changed what, when)
• Cross-table validation that can't be expressed as a CHECK constraint
• Maintaining derived columns or summary tables
• Enforcing complex referential rules
• Making views with joins updatable (INSTEAD OF triggers)

DDL triggers respond to schema-level events — they fire when someone runs CREATE TABLE, ALTER PROCEDURE, DROP INDEX, and so on. They live at one of two scopes:

Common Use Cases

• Auditing schema changes — tracking who created or dropped objects and when
• Enforcing change-control policies — block DROP TABLE outside a maintenance window
• Notifying ops when schema changes happen in production

Logon triggers fire when a user session is established with the SQL Server instance. They live only at server scope (ON ALL SERVER) and let you inspect (or even reject) connections as they happen.

Common Use Cases

• Logging connection attempts for security audit
• Restricting logins from particular IPs or at particular times
• Limiting the number of concurrent sessions per login

The most common form — a DML trigger on a table:

Key parts:

• schema.trigger_name — the new trigger's name (use the schema explicitly).
• ON table_or_view — what the trigger is bound to.
• AFTER vs INSTEAD OF — when the trigger runs relative to the DML.
• FOR is a synonym for AFTER in DML triggers — the two are interchangeable.
• NOT FOR REPLICATION — if present, the trigger is skipped when the change comes from replication. Useful in replicated environments.

Quick example of the right and wrong way to write this:

DECLARE @id INT, @name NVARCHAR(50);
SELECT  @id = id, @name = name FROM inserted;       -- ⚠️ silently grabs ONE row

INSERT INTO AuditLog (event, target_id, target_name)
VALUES ('inserted', @id, @name);

INSERT INTO AuditLog (event, target_id, target_name)
SELECT 'inserted', id, name
FROM   inserted;                                     -- ✓ handles ANY row count

• Automatic enforcement. Rules apply to every change, no matter how it gets in — application code, ad-hoc query, bulk insert.
• Centralized logic. Auditing or validation lives in one place, not scattered across multiple applications.
• Cross-table integrity. Rules that span tables (more complex than what a CHECK or FK can express) can be enforced consistently.
• Reduced application code. Logic that would otherwise need to be replicated in every client gets handled by the database.

• Hidden cost. Triggers run on every qualifying DML — and they're invisible to the calling code. A "simple" UPDATE can become arbitrarily expensive when triggers do extra work behind the scenes.
• Hard to debug. When a query fails or runs slowly, the trigger may be the cause but isn't visible in the user's SQL.
• Order-of-execution surprises. Multiple triggers on the same event can fire in non-obvious order, especially after schema changes.
• Lock and transaction interaction. Trigger code participates in the calling transaction — long-running trigger logic blocks every concurrent writer to the same table.
• Easy to misuse. Triggers should not contain expensive joins, calls to external systems, or anything that takes more than milliseconds.

The pages that follow drill into each kind of trigger:

• DDL Triggers — schema-event triggers using EVENTDATA()
• DML Triggers — AFTER vs INSTEAD OF triggers, INSERTED/DELETED pseudo-tables, multi-event triggers
• Enabling Triggers — turning a trigger back on after disabling it
• Disabling Triggers — pausing trigger execution without dropping the trigger
• Dropping Triggers — permanently removing triggers

• A trigger is code the database runs automatically in response to events — DML (data), DDL (schema), or logon (connection).
• DML triggers can be AFTER (fire after the DML, before commit) or INSTEAD OF (replace the DML entirely). FOR is a synonym for AFTER.
• Inside a DML trigger, the inserted and deleted pseudo-tables hold the affected rows — empty for irrelevant operations.
• Triggers fire once per statement, not once per row — write set-based logic that handles any row count.
• Triggers add hidden cost to every DML; reach for constraints and other features first when they suffice.