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 an OUTPUT Clause That Returns the Wrong Values Because a Trigger Already Fired Before It Could Capture the Rows

- 10.10.26 - ErcanOPAK

🗄️ The OUTPUT That Captured a Trigger’s Work, Not Yours

The OUTPUT clause on an INSERT, UPDATE, or DELETE statement captures the affected rows as they exist at the moment that statement’s own data change is applied – it is not documented to reflect any further change an AFTER trigger on the same table makes to those same rows once the trigger fires. When a trigger recalculates a column the OUTPUT clause is also trying to capture, the values an application receives back from OUTPUT can be the pre-trigger values, not the final, post-trigger state of the row, even though both happen within the same statement and the same transaction. Nothing errors and nothing warns – the captured values are simply not the ones a developer who assumed OUTPUT meant ‘the final row’ was expecting.

🔎 The Problem

CREATE TRIGGER trg_Orders_RecalcTotal ON Orders
AFTER UPDATE AS
BEGIN
    -- Recalculates ShippedTotal based on current inventory pricing
    -- AFTER the UPDATE statement that fired this trigger has
    -- already applied its own changes and captured its OUTPUT.
    UPDATE o
    SET o.ShippedTotal = inv.CurrentPrice * o.Quantity
    FROM Orders o
    JOIN Inventory inv ON inv.OrderId = o.OrderId
    WHERE o.OrderId IN (SELECT OrderId FROM inserted);
END;

-- The application expects @Result to hold the FINAL ShippedTotal,
-- but OUTPUT captured it before trg_Orders_RecalcTotal ever ran.
DECLARE @Result TABLE (Status NVARCHAR(50), ShippedTotal DECIMAL(10,2));

UPDATE Orders
SET Status = 'Shipped'
OUTPUT inserted.Status, inserted.ShippedTotal INTO @Result
WHERE OrderId = @OrderId;

✅ Fix: Don’t Rely on OUTPUT for Anything a Trigger Sets Afterward

  • Read the row back with a separate SELECT after the statement completes whenever a trigger-derived column matters to the caller, instead of trusting OUTPUT to reflect any value a trigger sets after the statement’s own change is applied.
  • Move value computation that must appear correctly in OUTPUT into the statement itself, or into a computed column, rather than into a trigger, whenever the order in which values get captured actually matters to a consumer.
  • Document on any trigger that modifies the same table whether its downstream consumers expect OUTPUT to reflect its changes, and add a test that updates a row with the trigger active and asserts OUTPUT’s captured value against the row’s true post-trigger state.

⚠️ Why This Is Easy to Miss

  • The documented behavior – that OUTPUT doesn’t account for AFTER trigger changes on the same table – is easy to skip past, because OUTPUT’s basic usage otherwise looks and behaves exactly like ‘give me back the final row’.
  • Works correctly in any schema where the trigger happens not to touch the specific columns an OUTPUT clause is capturing, so the bug only becomes visible the moment a trigger is added or changed to modify exactly those columns.

OUTPUT hands you back the row as your own statement left it – whatever a trigger does to that same row after the fact was never part of that promise.

— Database Engineer

Related posts:

SQL: Data Migration Strategies

SQL: Data Migration Strategies

SQL Server: Fix a Columnstore Index Query Running Slower Than a Rowstore Scan Because Too Many Rows ...

SQL Server: Fix a Columnstore Index Query Running Slower Than a Rowstore Scan Because Too Many Rows ...

Capitalize words in SQL (Display data first letter uppercase rest lowercase)

Capitalize words in SQL (Display data first letter uppercase rest lowercase)

Views: 6

Post navigation

Photoshop: Fix Exported Slices That Land in the Wrong Position After the Artboard’s Origin Shifts During a Canvas Resize
Photoshop: Fix a Content-Aware Fill Preview That Looks Clean but Leaves Visible Seams Only After You Apply It at Full Resolution

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,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

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

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

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