Triggers, inserted/deleted Tables, Multi-Row Correctness, Auditing, and Recursion

Learning outcomes

ServiceHub adds a trigger that writes an audit row whenever a work order changes. Unit tests update one row and pass. The first bulk correction updates 800 work orders and the trigger either records one row, throws a scalar-subquery error, or performs 800 cursor iterations. The failure comes from the wrong mental model: a SQL Server DML trigger fires once per statement, not once per row.

DML triggers run in the transaction of the statement that fired them. SQL Server exposes the affected rowsets through the special inserted and deleted tables. That gives triggers power—but also means hidden trigger work increases statement latency, lock duration, logging and failure scope.

1. Build a disposable trigger target

USE master;GOIF DB_ID(N'ServiceHubProgrammabilityLab') IS NULL  CREATE DATABASE ServiceHubProgrammabilityLab;GOALTER DATABASE ServiceHubProgrammabilityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubProgrammabilityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab13') IS NULL EXEC(N'CREATE SCHEMA lab13 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrder',N'U') IS NULLBEGIN  CREATE TABLE ops.WorkOrder  (    work_order_id bigint IDENTITY(1001,1) NOT NULL      CONSTRAINT PK_ops_WorkOrder PRIMARY KEY,    customer_code varchar(16) NOT NULL,    region_code char(3) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    description nvarchar(400) NULL,    CONSTRAINT CK_ops_WorkOrder_status      CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),    CONSTRAINT CK_ops_WorkOrder_priority CHECK (priority BETWEEN 1 AND 5)  );  ;WITH n AS  (    SELECT TOP (30000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT ops.WorkOrder(customer_code,region_code,status,priority,opened_at,closed_at,amount,description)  SELECT CONCAT('CUST-',RIGHT('000000'+CONVERT(varchar(6),n%9000),6)),         CASE n%3 WHEN 0 THEN 'N01' WHEN 1 THEN 'W02' ELSE 'E03' END,         CASE WHEN n%23=0 THEN 'ESCALATED'              WHEN n%5=0 THEN 'CLOSED'              WHEN n%2=0 THEN 'ASSIGNED' ELSE 'OPEN' END,         CONVERT(tinyint,1+n%5),         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n%5=0 THEN DATEADD(minute,n+60,'2026-01-01T00:00:00') END,         CAST(25+(n%25000)/10.0 AS decimal(12,2)),         CONCAT(N'ServiceHub work order ',n)  FROM n;  CREATE INDEX IX_ops_WorkOrder_region_status_opened    ON ops.WorkOrder(region_code,status,opened_at)    INCLUDE(customer_code,priority,amount,closed_at);END;GO

USE ServiceHubProgrammabilityLab;GODROP TABLE IF EXISTS lab13.TriggerAudit;DROP TABLE IF EXISTS lab13.TriggerSource;GOCREATE TABLE lab13.TriggerSource(  id int NOT NULL CONSTRAINT PK_lab13_TriggerSource PRIMARY KEY,  status varchar(16) NOT NULL,  amount decimal(12,2) NOT NULL);CREATE TABLE lab13.TriggerAudit(  audit_id bigint IDENTITY PRIMARY KEY,  id int NOT NULL,  old_status varchar(16) NULL,  new_status varchar(16) NULL,  changed_at datetime2(0) NOT NULL CONSTRAINT DF_lab13_TriggerAudit_changed DEFAULT SYSUTCDATETIME());INSERT lab13.TriggerSource(id,status,amount)VALUES (1,'OPEN',10),(2,'OPEN',20),(3,'OPEN',30),(4,'OPEN',40);GO

For INSERT, new rows appear in inserted. For DELETE, old rows appear in deleted. For UPDATE, old versions appear in deleted and new versions in inserted. These are special trigger rowsets maintained by SQL Server; you cannot create indexes on them, and a large affected set can itself make trigger joins expensive.

2. Deliberately broken: code that assumes inserted contains one row

CREATE OR ALTER TRIGGER lab13.trg_TriggerSource_BrokenON lab13.TriggerSourceAFTER UPDATEASBEGIN  SET NOCOUNT ON;  DECLARE @id int=(SELECT id FROM inserted);  DECLARE @old varchar(16)=(SELECT status FROM deleted);  DECLARE @new varchar(16)=(SELECT status FROM inserted);  INSERT lab13.TriggerAudit(id,old_status,new_status)  VALUES(@id,@old,@new);END;GOBEGIN TRY  UPDATE lab13.TriggerSource SET status='CLOSED' WHERE id IN (1,2);END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS expected_error,ERROR_MESSAGE() AS expected_message;END CATCH;SELECT * FROM lab13.TriggerSource ORDER BY id;SELECT * FROM lab13.TriggerAudit ORDER BY audit_id;GO

The scalar subqueries return more than one row, so SQL Server raises an error. Because the trigger executes inside the firing DML transaction, the original UPDATE is rolled back. This is a correctness failure, not just a trigger-performance issue.

3. Repair it with a set-based inserted/deleted join

CREATE OR ALTER TRIGGER lab13.trg_TriggerSource_BrokenON lab13.TriggerSourceAFTER UPDATEASBEGIN  SET NOCOUNT ON;  IF NOT UPDATE(status) RETURN;  INSERT lab13.TriggerAudit(id,old_status,new_status)  SELECT i.id,d.status,i.status  FROM inserted AS i  JOIN deleted AS d ON d.id=i.id  WHERE EXISTS  (    SELECT d.status EXCEPT SELECT i.status  );END;GOUPDATE lab13.TriggerSource SET status='CLOSED' WHERE id IN (1,2,3);SELECT * FROM lab13.TriggerAudit ORDER BY audit_id;GO

The trigger now treats the transition as two rowsets joined by the key. The EXCEPT comparison is NULL-safe if the audited column later becomes nullable. The trigger inserts exactly one audit row per changed row without a cursor.

4. Trigger work is part of the caller’s latency and locks

An AFTER trigger executes after the base DML has logically succeeded but before the transaction commits. If the trigger performs slow joins, waits on another resource, or raises an error, the calling statement remains in the same transaction. That can extend lock duration and create blocking/deadlock paths that the application does not see in its SQL text.

SELECT tr.name,tr.is_disabled,tr.is_instead_of_trigger,       OBJECT_NAME(tr.parent_id) AS parent_object,       OBJECT_DEFINITION(tr.object_id) AS trigger_definitionFROM sys.triggers AS trWHERE tr.object_id=OBJECT_ID(N'lab13.trg_TriggerSource_Broken');GOSELECT OBJECT_NAME(object_id,database_id) AS trigger_name,       execution_count,total_worker_time,total_elapsed_time,last_execution_timeFROM sys.dm_exec_trigger_statsWHERE database_id=DB_ID()  AND object_id=OBJECT_ID(N'lab13.trg_TriggerSource_Broken');GO

The trigger-stats DMV is transient cache evidence, not history. Query Store/Extended Events/application telemetry may be needed for incident timelines. Also remember that hidden side effects complicate retries: an application may retry the outer statement without realizing the trigger invokes downstream logic.

5. Nesting and recursion must be explicit architecture decisions

If a trigger changes another table that has a trigger, trigger execution can nest. SQL Server supports nested DML/DDL triggers up to 32 levels. AFTER-trigger direct recursion is governed by the database RECURSIVE_TRIGGERS option; indirect nesting also depends on the server nested triggers configuration. INSTEAD OF triggers have their own nesting behavior. The safe rule is not “turn recursion off and forget it”; document whether any chain is intentional, keep it short, and monitor it.

SELECT name,is_recursive_triggers_onFROM sys.databasesWHERE name=DB_NAME();GOSELECT name,value_in_useFROM sys.configurationsWHERE name=N'nested triggers';GOSELECT TRIGGER_NESTLEVEL() AS current_trigger_nest_level;GO

6. Triggers are not tamper-proof audit logs

A trigger-written audit table records changes only while the trigger exists, is enabled, and has permission to write. A sufficiently privileged principal can disable or alter the trigger, update/delete audit rows, restore a database, or bypass assumptions through another administrative path. Therefore a DML trigger can be useful operational history, but it is not inherently an immutable compliance audit trail.

Change Data Capture (CDC), Change Tracking, SQL Server Audit, temporal tables, replication and application event logs solve different problems. A trigger should not duplicate all of them. Replication can also interact with trigger execution; NOT FOR REPLICATION is a specific choice that changes behavior for replication agents and must be deliberate. CDC does not need your business trigger to capture log changes, and a trigger can increase write cost alongside CDC.

7. Cleanup

DROP TRIGGER IF EXISTS lab13.trg_TriggerSource_Broken;DROP TABLE IF EXISTS lab13.TriggerAudit;DROP TABLE IF EXISTS lab13.TriggerSource;GO

In production, dropping a trigger is a release change: capture its definition, dependencies, expected business invariant and rollback script first. A trigger may be invisible to application code but still essential to correctness.

Production judgment

Use constraints for invariants that fit declarative constraints. Use procedures when an explicit write API is clearer. Use triggers when the action genuinely must run for every qualifying table modification regardless of caller and the hidden coupling is acceptable. Keep trigger bodies set-based, small and transaction-aware; avoid network calls, long loops and broad queries. Test single-row, multi-row and zero-effective-change cases under concurrency.

Mandatory labs require free SQL Server 2025 Developer/Express and ordinary DDL/DML permissions in the disposable database. No server setting is changed. Replication/CDC examples are conceptual boundaries, not required infrastructure.

Authoritative references

• Use inserted and deleted tables — rowset semantics and performance considerations
• Create DML triggers to handle multiple rows — set-based multi-row trigger design
• Create nested triggers — nesting, recursion and 32-level limit
• CREATE TRIGGER — trigger syntax and NOT FOR REPLICATION
• SQL Server 2025 build versions — servicing baseline