Triggers in SQL Server

A DML trigger is a stored procedure that runs automatically when an INSERT, UPDATE, or DELETE hits a table or view. SQL Server's model differs from MySQL's in two ways that cause real bugs when people port code: there is no BEFORE trigger (the choices are AFTER and INSTEAD OF), and triggers fire once per statement, not once per row. A single UPDATE that touches 10,000 rows fires the trigger exactly once, with all 10,000 rows visible in the virtual tables.

The inserted and deleted Tables

Inside a trigger, two special tables expose what the statement changed:

• inserted -- the new versions of the rows (for INSERT and UPDATE)
• deleted -- the old versions of the rows (for DELETE and UPDATE)

For an UPDATE, both are populated; joining them on the primary key gives before-and-after per row. Both tables have the same columns as the trigger's table, and both can contain zero, one, or many rows.

A Correct Multi-Row Trigger

Audit every price change:

CREATE TABLE price_history (
    product_id  int           NOT NULL,
    old_price   decimal(10,2) NOT NULL,
    new_price   decimal(10,2) NOT NULL,
    changed_at  datetime2     NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
 
CREATE TRIGGER trg_products_price_audit
ON products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
 
    IF NOT UPDATE(price) RETURN;
 
    INSERT INTO price_history (product_id, old_price, new_price)
    SELECT i.product_id, d.price, i.price
    FROM inserted i
    JOIN deleted  d ON d.product_id = i.product_id
    WHERE i.price <> d.price;
END;

Everything here is set-based: one INSERT ... SELECT handles any number of changed rows. Notes:

• SET NOCOUNT ON stops the trigger's row-count messages from leaking into the client's result stream, which confuses some drivers and ORMs.
• UPDATE(price) is true when the column appeared in the SET clause -- it does not mean the value actually changed, hence the i.price <> d.price filter as well.
• The trigger fires even when the statement affected zero rows. The early RETURN patterns you'll see (IF @@ROWCOUNT = 0 RETURN; as the first line) exist to skip work in that case.

The #1 Trigger Bug: Assuming One Row

This pattern is everywhere, and it's wrong:

-- BROKEN: silently processes ONE arbitrary row of a multi-row statement
DECLARE @id int, @price decimal(10,2);
SELECT @id = product_id, @price = price FROM inserted;
 
INSERT INTO price_history (product_id, new_price) VALUES (@id, @price);

Assigning a multi-row table to scalar variables picks one arbitrary row and ignores the rest -- no error, no warning. It passes every single-row test and corrupts your audit trail the first time someone runs a bulk update. The fix is never a cursor; it's writing the logic as a set operation, as in the previous section. If you find yourself needing a loop inside a trigger, the design is usually wrong.

AFTER vs INSTEAD OF

AFTER triggers run after the statement's changes are made (but before the transaction commits -- see below). They're for reactions: auditing, maintaining denormalized totals, enforcing rules too complex for constraints. A table can have multiple AFTER triggers per action; the order is undefined except that sp_settriggerorder can pin one as first and one as last.

INSTEAD OF triggers replace the statement entirely -- the original INSERT/UPDATE/DELETE never happens, and your trigger body does whatever should happen instead. Only one INSTEAD OF trigger per action is allowed. Their main legitimate use is making multi-table views updatable:

CREATE TRIGGER trg_v_customer_orders_insert
ON v_customer_orders   -- a view joining customers and orders
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- route the view's columns to the right base tables
    INSERT INTO orders (customer_id, order_date, amount)
    SELECT customer_id, order_date, amount FROM inserted;
END;

There is no BEFORE trigger in SQL Server. If you're porting MySQL code that used BEFORE INSERT to fix up values, the T-SQL equivalents are a DEFAULT constraint, a computed column, or an INSTEAD OF trigger that inserts corrected values.

Triggers Run Inside Your Transaction

The trigger executes within the transaction of the statement that fired it. Two consequences:

1. A ROLLBACK inside a trigger rolls back the entire transaction -- the triggering statement, everything before it in the transaction, and it aborts the rest of the batch. This is the standard way to veto a change from an AFTER trigger, but understand the blast radius:

CREATE TRIGGER trg_orders_no_backdating
ON orders
AFTER UPDATE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted i JOIN deleted d
               ON d.order_id = i.order_id
               WHERE i.order_date < d.order_date)
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50001, 'Order dates cannot be moved backward.', 1;
    END
END;

2. Slow triggers make every write slow. The client's UPDATE doesn't return until the trigger finishes. A trigger that does a table scan or calls out to a linked server turns a millisecond write into a multi-second one, holding locks the entire time. Trigger bodies should be short, indexed, set-based operations.

For transaction mechanics in general (XACT_ABORT, doomed transactions), see our transactions guide.

Nesting and Recursion

• Triggers nest up to 32 levels: trigger A modifies table B, firing B's trigger, and so on. Exceeding 32 aborts the statement. Nesting is controlled by the server-wide nested triggers configuration (on by default).
• Direct recursion -- a trigger modifying its own table and re-firing itself -- only happens when the database option RECURSIVE_TRIGGERS is ON (it's off by default). INSTEAD OF triggers don't support direct recursion regardless.

A trigger updating its own table is legal and common (stamping a modified_at column), and with RECURSIVE_TRIGGERS off it won't loop. But pairs of triggers on different tables that write to each other will nest until they hit the 32-level wall -- a design smell worth catching in review.

When Not to Use a Trigger

Triggers are invisible. A developer reading UPDATE orders SET ... has no signal that three triggers, a history insert, and a running-total update are about to happen. Before writing one, check the alternatives:

Temporal tables in particular have replaced the classic hand-rolled audit trigger for most new work since SQL Server 2016 -- less code, no multi-row bugs, queryable with FOR SYSTEM_TIME. Triggers remain the right tool for enforcing rules constraints can't express and for making views updatable.

Common Mistakes

Writing the inserted/deleted join scaffolding is exactly the kind of boilerplate Mako's AI autocomplete produces reliably once it sees your table's key columns.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.