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