🗄️ Fix a Trigger Firing Twice on One Merge Statement
An `AFTER INSERT, UPDATE` trigger on a table targeted by a `MERGE` statement fires once per statement by default, not once per row — but when a `MERGE`’s `WHEN MATCHED` clauses are written with multiple conditions that could each independently match the same source row against the same target row, and the trigger’s own logic assumes a strict 1:1 relationship between a logical change and a trigger firing (a cursor, or a join back to the base table), a row that satisfies more than one `WHEN MATCHED` branch’s combined row set in ways the author didn’t realize overlapped can get double-counted or double-processed by that row-by-row logic.
🔎 The Problem
MERGE INTO Accounts AS target
USING StagingChanges AS source ON target.Id = source.Id
WHEN MATCHED AND source.Balance <> target.Balance THEN
UPDATE SET target.Balance = source.Balance
WHEN MATCHED AND source.Status <> target.Status THEN
UPDATE SET target.Status = source.Status;
-- a row where BOTH Balance and Status changed matches both branches -
-- the trigger's row-by-row logic can see it twice in the same statement
✅ Fix: Make the WHEN MATCHED Branches Exclusive
- Audit every `WHEN MATCHED` clause’s conditions for actual mutual exclusivity — add an explicit `AND NOT (<earlier condition>)` to each subsequent clause, or restructure the logic into a single `WHEN MATCHED THEN UPDATE` with a `CASE`, rather than assuming separate clauses never overlap for the same row.
- Inside the trigger itself, always query `inserted`/`deleted` as sets — a single set-based `UPDATE … FROM inserted` — rather than row-by-row with a cursor, so the trigger’s own logic can’t double-process a row even if the statement’s conditions somehow produce it twice.
- Add a test `MERGE` with source data deliberately crafted to match more than one `WHEN MATCHED` condition for the same row, and assert the trigger’s side effects happened exactly once per logical row — this is the one test that exposes the overlap.
🚨 Why This Is Dangerous
- A trigger that silently processes one row’s change twice — two notification emails, two audit rows, a double-incremented counter — produces no error of any kind, so the bug is usually discovered by a downstream team noticing duplicate data long after the `MERGE` has already run thousands of times.
A trigger fires once per statement by rule – it’s your own WHEN MATCHED clauses that decide how many rows that statement actually changed.

