UPDATE() in a Trigger Is True Even When Nothing Changed

An audit row appears even though the price never moved. UPDATE() in a trigger reports that the SET list included a column, not that its value changed. Compare inserted and deleted rows before recording a real change.

Watch UPDATE() in a Trigger Report a Non-Change

Create a product table and an audit table in a disposable database. The first trigger deliberately uses the common shortcut. Update a row to the same value and inspect the audit. SQL Server reports the column as updated because the statement mentioned it, even though the value did not move.

CREATE TABLE dbo.PriceDemo
(
    ProductID int NOT NULL PRIMARY KEY,
    Price decimal(10,2) NULL
);
CREATE TABLE dbo.PriceAudit
(
    ProductID int NOT NULL,
    OldPrice decimal(10,2) NULL,
    NewPrice decimal(10,2) NULL,
    ChangedAt datetime2(0) NOT NULL
);
INSERT dbo.PriceDemo VALUES (1,10.00),(2,NULL),(3,20.00);
GO
CREATE OR ALTER TRIGGER dbo.tr_PriceDemo_Audit
ON dbo.PriceDemo AFTER UPDATE AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(Price)
       INSERT dbo.PriceAudit
           (ProductID,OldPrice,NewPrice,ChangedAt)
       SELECT i.ProductID,d.Price,i.Price,SYSDATETIME()
       FROM inserted AS i
       JOIN deleted AS d ON d.ProductID = i.ProductID;
END;
GO
UPDATE dbo.PriceDemo SET Price = Price WHERE ProductID = 1;
SELECT * FROM dbo.PriceAudit;

The audit now contains a row for an unchanged price. I use this controlled failure before rewriting the trigger because it establishes the required behavior. Which audit consumer expects a statement attempt, and which expects an actual data change? Those are separate event definitions.

Know What UPDATE() Tests in a Trigger

UPDATE(column) is a statement-level test inside an INSERT or UPDATE trigger. An explicit SET of the current value makes it true. On an INSERT, columns can also count as updated because new rows receive explicit or implicit values. It is useful as a cheap guard to skip work when Price was not in the statement. It is not enough as the final filter for an audit of changes.

COLUMNS_UPDATED() has a similar limitation: it reports which columns were targeted, not whether values differ. Do not switch functions and expect a value comparison. The comparison belongs between the deleted and inserted logical tables. A trigger fires once per statement, which can affect many rows, so scalar-variable code is unsafe for this audit.

Compare Old and New Rows

On SQL Server 2022, IS DISTINCT FROM expresses the NULL-safe difference directly. Join inserted and deleted by a stable primary key, then filter to changed prices. Keep IF UPDATE(Price) as an optional guard; the WHERE predicate performs the actual test. Replace the trigger in the disposable example and repeat the same-value update.

CREATE OR ALTER TRIGGER dbo.tr_PriceDemo_Audit
ON dbo.PriceDemo AFTER UPDATE AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(Price)
       INSERT dbo.PriceAudit
           (ProductID,OldPrice,NewPrice,ChangedAt)
       SELECT i.ProductID,d.Price,i.Price,SYSDATETIME()
       FROM inserted AS i
       JOIN deleted AS d ON d.ProductID = i.ProductID
       WHERE i.Price IS DISTINCT FROM d.Price;
END;
GO
TRUNCATE TABLE dbo.PriceAudit;
UPDATE dbo.PriceDemo SET Price = Price WHERE ProductID = 1;
SELECT * FROM dbo.PriceAudit;

The result should be empty. SQL Server versions before 2022 need an explicit NULL branch or the EXCEPT pattern. Do not write i.Price <> d.Price alone; it ignores a move between NULL and a value. The trigger writes in the same transaction as the UPDATE, so a rollback removes the audit row too. That is usually correct for a committed-change audit.

Test NULL Transitions

The same-value update above already covered 10 to 10. The block below covers the rest of a small matrix in one statement. Product 1 moves from 10 to 12, product 2 from NULL to 5, and product 3 from 20 to NULL. The revised trigger should record all three. The set-based query handles every row in one trigger call.

UPDATE dbo.PriceDemo
SET Price = CASE ProductID
              WHEN 1 THEN 12.00
              WHEN 2 THEN 5.00
              WHEN 3 THEN NULL
            END
WHERE ProductID IN (1,2,3);
SELECT ProductID,OldPrice,NewPrice
FROM dbo.PriceAudit ORDER BY ProductID;

The example records three changes from its initial state. Run it once after resetting the demo tables; repeated runs will produce a different expected set because the rows are already changed. I write expected old and new values down before each trigger test. That prevents an empty audit from being mistaken for a correct trigger.

Support Older SQL Server Versions

The explicit equivalent checks inequality plus the two one-NULL cases. It is longer but clear and compatible. Keep parentheses around the OR branches when adding other filters. A set operator such as EXCEPT treats NULLs as equal too, but a direct predicate is easier to read for one audited column.

CREATE OR ALTER TRIGGER dbo.tr_PriceDemo_Audit
ON dbo.PriceDemo AFTER UPDATE AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(Price)
        INSERT dbo.PriceAudit
            (ProductID,OldPrice,NewPrice,ChangedAt)
        SELECT i.ProductID,d.Price,i.Price,SYSDATETIME()
        FROM inserted AS i
        JOIN deleted AS d ON d.ProductID = i.ProductID
        WHERE i.Price <> d.Price
           OR (i.Price IS NULL AND d.Price IS NOT NULL)
           OR (i.Price IS NOT NULL AND d.Price IS NULL);
END;

That complete trigger replaces the SQL Server 2022 version on an older engine. In my test it skipped NULL to NULL and a same-value update, and recorded NULL to 7. The inserted and deleted tables exist in its trigger context. When auditing several columns, apply a NULL-safe comparison to each and OR the results, or store a clear before and after record for columns that changed.

Account for a Multi-Row UPDATE in the Trigger

A single UPDATE against a thousand products invokes the trigger once with a thousand inserted rows and a thousand deleted rows. Avoid selecting values into scalar variables. The join must use a key that cannot change in the same statement. If ProductID itself can change, choose another immutable identifier or design the audit to account for key changes explicitly.

I test a multi-row update with mixed cases: some changed, some unchanged, and some NULL transitions. Then I compare source row count with audit count. Auditing every targeted row can inflate storage and make incident review misleading. Auditing only actual changes preserves a more useful history.

Keep the Audit Trustworthy

Think about precision and normalization in the comparison. A decimal price is straightforward, but a text field can compare equal under a case-insensitive collation even when its bytes differ. Decide whether the audit means a SQL comparison change or an exact representation change. For exact binary differences, use an appropriate binary comparison rather than assuming ordinary string equality captures them.

Avoid expensive per-row work inside the trigger. The trigger runs inside the caller transaction, so slow logging extends locks and response time. Index the audit table for its read pattern, keep inserted payload limited to required fields, and test a large multi-row update. I compare statement duration with and without the trigger in a restored test copy.

Audit tables need retention, access controls, and a definition of committed versus attempted actions. A trigger sees the transaction's changes, and its writes roll back with that transaction. If the requirement is to record failed or rolled-back attempts, use an appropriate server-level audit path instead of relying on this row-change trigger.

The simplest review test is an UPDATE that sets Price to itself. If the audit records a change, the trigger is answering the SET-list question rather than the value question. I keep that test in the deployment script alongside the NULL matrix and a multi-row case.

Related reading on this blog: Types of Triggers and How to Capture Deleted Rows Without Trigger? Interview Question of the Week #297.

UPDATE() is not a value comparison, it is a SET-list test followed by an inserted-deleted check.

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