Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a Trigger Firing Twice on One Merge Statement

- 19.11.26 - ErcanOPAK

🗄️ 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.

— Database Engineer

Related posts:

SQL: Use EXPLAIN ANALYZE to Understand Slow Queries

SQL: Use EXPLAIN ANALYZE to Understand Slow Queries

SQL: Use STRING_AGG to Concatenate Rows into Comma-Separated List

SQL: Use STRING_AGG to Concatenate Rows into Comma-Separated List

SQL Server: Fix a Scalar Function Making a Set-Based Query Run Row by Row Because Scalar UDF Inlinin...

SQL Server: Fix a Scalar Function Making a Set-Based Query Run Row by Row Because Scalar UDF Inlinin...

Views: 4

Post navigation

HTML5: Fix a Form’s required Attribute Skipped on Enter
Asp.Net Core: Fix Output Caching Serving a Stale Authorized Page

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (658)
  • Add Constraint to SQL Table to ensure email contains @ (613)
  • Average of all values in a column that are not zero in SQL (571)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (549)
  • Find numbers with more than two decimal places in SQL (477)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK
  • Git: Fix a Worktree Locking a Branch Needed Somewhere Else
  • Asp.Net Core: Fix a Background Service Leaking Scoped Dependencies
  • SQL Server: Fix a Columnstore Index Fragmenting After Bulk Deletes
  • Docker: Fix a Container Losing Logs After a Restart Policy
  • Asp.Net Core: Fix Output Caching Serving a Stale Authorized Page

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com