Triggers That Assume One Row: Fixing Them for Multi-Row Updates

Testing a trigger with one row leaves its biggest assumption untested. Triggers built around a scalar variable lose information during multi-row updates. The fix starts by treating inserted and deleted as tables.

Build a Disposable Audit Example

An audit trigger looks reliable when its first test changes one row. That test leaves the central assumption untouched. The inserted table contains the new versions of every affected row. The deleted table contains their previous versions. Neither table promises one row or a meaningful row order.

I test a trigger with zero, one, and several affected rows. That small habit catches mistakes a successful single-row update cannot reveal. A variable has room for one value, regardless of how confidently the trigger was written. The audit table needs an explicit row for each change the business wants recorded.

Create these objects in a disposable database. The source table has an immutable primary key and a nullable status. The audit table records the key and both versions of the status. An identity column supplies an audit identifier, not a guaranteed business ordering across concurrent transactions.

CREATE TABLE dbo.TriggerRowsDemo
(
    ItemID int NOT NULL PRIMARY KEY,
    StatusCode varchar(20) NULL
);
CREATE TABLE dbo.TriggerAuditDemo
(
    AuditID bigint IDENTITY PRIMARY KEY,
    ItemID int NOT NULL,
    OldStatus varchar(20) NULL,
    NewStatus varchar(20) NULL,
    ChangedAt datetime2(7) NOT NULL
);
INSERT dbo.TriggerRowsDemo VALUES
(1, 'Open'), (2, 'Open'), (3, NULL);
GO

Expose the Single-Row Assumption in Multi-Row Updates

The following trigger is intentionally incomplete. It assigns multiple input rows to scalar variables. SQL Server keeps one assigned value, without a useful ordering guarantee. The subsequent INSERT therefore records one item, even when the triggering statement changed several items.

The module starts its own batch. Keep the GO separator when pasting the script into SSMS. Test code belongs after the module definition, not inside its CREATE statement. This avoids turning a trigger demonstration into an accidental syntax lesson.

CREATE TRIGGER dbo.tr_TriggerRowsDemo_Audit
ON dbo.TriggerRowsDemo
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ItemID int, @NewStatus varchar(20);
    SELECT @ItemID = ItemID, @NewStatus = StatusCode
    FROM inserted;
    INSERT dbo.TriggerAuditDemo
        (ItemID, OldStatus, NewStatus, ChangedAt)
    SELECT @ItemID, d.StatusCode, @NewStatus, SYSUTCDATETIME()
    FROM deleted d
    WHERE d.ItemID = @ItemID;
END;
GO
UPDATE dbo.TriggerRowsDemo SET StatusCode = 'Closed';
SELECT s.ItemID, s.StatusCode, a.AuditID
FROM dbo.TriggerRowsDemo s
LEFT JOIN dbo.TriggerAuditDemo a ON a.ItemID = s.ItemID
ORDER BY s.ItemID;

Read the returned rows on your server. Missing audit identifiers expose the coverage problem. The identity of the retained item is irrelevant. Adding ORDER BY to choose one item would only make the wrong behavior more predictable.

Join the Two Rowsets by a Stable Key

Replace the scalar assignments with INSERT SELECT. Join the two transition tables using the unchanged primary key. Each input pair contributes one audit row when the status actually differs. The comparison explicitly handles transitions into or out of NULL.

ALTER TRIGGER dbo.tr_TriggerRowsDemo_Audit
ON dbo.TriggerRowsDemo
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM inserted) RETURN;
    INSERT dbo.TriggerAuditDemo
        (ItemID, OldStatus, NewStatus, ChangedAt)
    SELECT i.ItemID, d.StatusCode, i.StatusCode, SYSUTCDATETIME()
    FROM inserted i
    JOIN deleted d ON d.ItemID = i.ItemID
    WHERE i.StatusCode <> d.StatusCode
       OR (i.StatusCode IS NULL AND d.StatusCode IS NOT NULL)
       OR (i.StatusCode IS NOT NULL AND d.StatusCode IS NULL);
END;
GO

The early return handles a statement that affects no rows. UPDATE(StatusCode) can tell you the column was targeted, but it does not prove its value changed. A statement can set a column to its existing value. Compare old and new values when the audit contract records changes rather than attempted assignments.

An updateable primary key needs another matching design. Joining on a key that changes loses the correspondence between versions. Prefer a stable identifier for this audit example, and document that requirement in the real schema.

Verify Multi-Row Updates and No-Change Behavior

The repaired trigger now sees three existing Closed values. Change them to Reviewed and inspect only that new transition. This avoids mixing the intentionally defective audit entry with the fixed trigger's result. The sample remains readable without deleting earlier evidence.

UPDATE dbo.TriggerRowsDemo SET StatusCode = 'Reviewed';
SELECT ItemID, COUNT_BIG(*) AS AuditEntries
FROM dbo.TriggerAuditDemo
WHERE OldStatus = 'Closed' AND NewStatus = 'Reviewed'
GROUP BY ItemID ORDER BY ItemID;
UPDATE dbo.TriggerRowsDemo
SET StatusCode = StatusCode;
UPDATE dbo.TriggerRowsDemo
SET StatusCode = 'Unused'
WHERE ItemID = -1;
SELECT ItemID, OldStatus, NewStatus
FROM dbo.TriggerAuditDemo
ORDER BY AuditID;

Check that unchanged assignments add no transition entries. Then test NULL to value, value to NULL, and NULL to NULL. Include a predicate that touches only part of the table. Does every changed key have exactly the audit coverage your requirement promises?

I keep those cases beside the trigger definition during review. They describe observable behavior rather than just repeating the implementation. A multi-row INSERT or DELETE trigger needs a related design using the transition table appropriate to that operation.

Account for the Caller Transaction

An AFTER trigger runs inside the transaction that changed the table. Audit writes participate in the same commit or rollback. If audit insertion fails, the caller's operation can fail too. That coupling is useful for a mandatory audit, but it deserves an explicit application contract.

Do not add network calls, long loops, or large unrelated scans to this path. Every writer would inherit that work and its locking. Keep the audit table's indexes narrow and justified. Additional indexes also require maintenance for every inserted audit row.

Test rollback in the disposable database by wrapping an update in a transaction. Query the audit table before rollback, then query again afterward. The new entries should follow the same transaction outcome as the source change. Keep application error handling aware of trigger errors instead of treating the audit as an independent background task.

Review the Audit Meaning Before Deployment

The example records status transitions only. A real audit can require actor identity, application context, and a correlation identifier. Decide which connection identity is trustworthy, especially when individual users share one application login. A timestamp alone does not identify the person behind a change.

Review existing triggers on the table and any downstream operations they invoke. Nested trigger behavior and recursion settings affect the complete write path. Deploy the corrected module through the normal approved change process, retaining its previous definition. Then verify the same multi-row cases against a controlled copy of the production schema.

Set-based code solves the row-count assumption. It does not replace decisions about audit retention, access permissions, or transaction cost. Those decisions determine whether the audit remains useful after the first successful test.

Include Failed Multi-Row Updates in the Review

An audit table can reject a row through a constraint or an unavailable allocation. Test one deliberate failure in the disposable database and inspect the caller's transaction state. The application needs to handle that error consistently with any other failed write. Silencing the trigger error would break a mandatory audit promise.

Also test a statement that targets the status column while changing only a different column's value. Column-target checks and value-change checks answer different questions. Decide whether an attempted assignment itself belongs in the audit. Keep that choice explicit so readers do not mistake this example for a complete security audit. The set-based rewrite guarantees coverage of the defined status transitions. It does not capture SELECT activity, changes made while a trigger is disabled, or every action performed by a connection. Multi-row updates belong in the trigger test contract from the beginning. Confirm that audit coverage stays complete after multi-row updates and follows the caller transaction outcome.

Related reading on this blog: How to Avoid Triggers for Multiple Row Operations in a Table and UPDATE() in a Trigger Is True Even When Nothing Changed.

A trigger invocation is not a single-row promise, it is a statement-level event whose rowsets need complete handling.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.