SQL Server Trigger to Check if Value Has Changed (UPDATE() vs inserted/deleted)

UPDATE(CreditLimit) returned 1 when I set CreditLimit to its own value. For a sql server trigger to check if value has changed, join inserted to deleted on the key and keep rows where the new value IS DISTINCT FROM the old one.

Quick answer: put the comparison in the trigger’s WHERE clause, not in an IF:

SELECT i.CustomerID, d.CreditLimit AS OldLimit, i.CreditLimit AS NewLimit
FROM inserted AS i
JOIN deleted  AS d ON d.CustomerID = i.CustomerID
WHERE i.CreditLimit IS DISTINCT FROM d.CreditLimit;   -- SQL Server 2022+

On SQL Server 2019 or older, swap the WHERE for EXISTS (SELECT i.CreditLimit EXCEPT SELECT d.CreditLimit). That’s my verdict after seven test scripts; the proof for each claim is below.

How I Tested: One Customers Table, Seven Trigger Scripts

Tested on SQL Server 2025 (17.0.1000.7) Standard Developer, sqlcmd in the VS Code terminal. Every script below starts from this exact table (my generator drops the triggers and rebuilds it first), so the outputs are comparable.

If triggers are new to you, skim this SQL Server trigger overview first.

CREATE TABLE dbo.Customers (
    CustomerID  int           PRIMARY KEY,
    FullName    nvarchar(20)  NOT NULL,
    City        nvarchar(12)  NOT NULL,
    CreditLimit decimal(10,2) NOT NULL,
    Phone       varchar(12)   NULL,
    ModifiedAt  datetime2(0)  NULL
);
INSERT INTO dbo.Customers (CustomerID, FullName, City, CreditLimit, Phone) VALUES
 (1, N'Hannah Brooks',  N'Denver',   5000.00, '303-555-0142'),
 (2, N'Tyler Reed',     N'Austin',   2500.00, NULL),
 (3, N'Grace Miller',   N'Columbus', 7500.00, '614-555-0188'),
 (4, N'Marcus Bennett', N'Tampa',    3000.00, '813-555-0127'),
 (5, N'Olivia Hayes',   N'Portland', 4000.00, NULL),
 (6, N'Ethan Cole',     N'Raleigh',  6000.00, '919-555-0163');

CREATE TABLE dbo.CustomerHistory (
    HistoryID  int IDENTITY PRIMARY KEY,
    CustomerID int           NOT NULL,
    OldLimit   decimal(10,2) NULL,
    NewLimit   decimal(10,2) NULL,
    ChangedAt  datetime2(0)  NOT NULL DEFAULT SYSDATETIME()
);

UPDATE() Only Checks the SET List, Not the Value

Here’s the claim most old tutorials get wrong, including the previous version of this page. UPDATE(column) doesn’t compare anything. I set CreditLimit to itself, then to a new number:

SET NOCOUNT ON;
GO
CREATE OR ALTER TRIGGER dbo.trg_Customers_Check ON dbo.Customers
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @real int = (SELECT COUNT(*) FROM inserted AS i
        JOIN deleted AS d ON d.CustomerID = i.CustomerID
        WHERE i.CreditLimit <> d.CreditLimit);
    PRINT CONCAT('UPDATE(CreditLimit) = ', IIF(UPDATE(CreditLimit), 1, 0),
                 ' | rows really changed = ', @real);
END;
GO
UPDATE dbo.Customers SET CreditLimit = CreditLimit WHERE CustomerID = 1;
UPDATE dbo.Customers SET CreditLimit = 5500 WHERE CustomerID = 1;

sqlcmd printed:

UPDATE(CreditLimit) = 1 | rows really changed = 0
UPDATE(CreditLimit) = 1 | rows really changed = 1

Both statements got 1. Only the join saw that the first one changed nothing. Microsoft’s reference agrees: the function is true whenever the column is in the statement, even if the value stays the same.

So IF UPDATE(col) answers “was this column written to?”, which is what an AFTER UPDATE trigger needs for cheap filtering and nothing more.

Two more facts from the docs: it’s also true for every column during an INSERT (explicit or implicit NULL), and computed columns can’t be passed to it.

Compare inserted and deleted on the Key to Catch Real Changes

This is the trigger I’d ship. It audits only rows whose CreditLimit really moved into CustomerHistory. The test runs a City-only update, then one statement that touches four customers but raises only two:

SET NOCOUNT ON;
GO
CREATE OR ALTER TRIGGER dbo.trg_Customers_Audit ON dbo.Customers
AFTER UPDATE
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;        -- no rows
    SET NOCOUNT ON;
    IF NOT UPDATE(CreditLimit) RETURN;   -- not in SET
    INSERT INTO dbo.CustomerHistory
           (CustomerID, OldLimit, NewLimit)
    SELECT i.CustomerID, d.CreditLimit, i.CreditLimit
    FROM inserted AS i
    JOIN deleted AS d ON d.CustomerID = i.CustomerID
    WHERE i.CreditLimit IS DISTINCT FROM d.CreditLimit;
END;
GO
UPDATE dbo.Customers SET City = N'Boulder' WHERE CustomerID = 1;
UPDATE dbo.Customers
SET CreditLimit = IIF(CustomerID <= 2, CreditLimit + 500,
                      CreditLimit)
WHERE CustomerID <= 4;
SELECT HistoryID, CustomerID, OldLimit, NewLimit
FROM dbo.CustomerHistory;

History table after both updates:

HistoryID   CustomerID  OldLimit     NewLimit    
----------- ----------- ------------ ------------
          1           2      2500.00      3000.00
          2           1      5000.00      5500.00

Two history rows for four updated rows: customers 1 and 2. The City-only update logged nothing. HistoryID order follows whatever order SQL Server read inserted, not CustomerID, so don’t rely on it.

deleted holds the old row and inserted the new one; this walk-through on reading inserted values in a trigger covers both tables in detail.

Skip the Work Early With IF NOT UPDATE(col) RETURN

Here UPDATE() earns its keep. If CreditLimit isn’t in the SET list, nothing can have changed, so IF NOT UPDATE(CreditLimit) RETURN; exits before the join.

Microsoft also recommends IF ROWCOUNT_BIG() = 0 RETURN; as the first line, since a DML trigger fires even when the statement matches zero rows. For branching on several columns, use the IF…ELSE patterns in a trigger.

NULL-Safe Trigger Comparison: IS DISTINCT FROM vs EXCEPT

Phone allows NULL, and that’s where your plain <> quietly fails. One statement clears customer 1’s phone, fills customer 2’s and leaves customer 3 alone. The trigger counts the changes three ways:

SET NOCOUNT ON;
GO
CREATE OR ALTER TRIGGER dbo.trg_Customers_Check ON dbo.Customers
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ne int, @dist int, @exc int;
    SELECT @ne   = SUM(IIF(i.Phone <> d.Phone, 1, 0)),
           @dist = SUM(IIF(i.Phone IS DISTINCT FROM d.Phone,
                           1, 0)),
           @exc  = SUM(x.Changed)
    FROM inserted AS i
    JOIN deleted AS d ON d.CustomerID = i.CustomerID
    CROSS APPLY (SELECT IIF(EXISTS (SELECT i.Phone
                                    EXCEPT SELECT d.Phone),
                            1, 0) AS Changed) AS x;
    PRINT CONCAT('<> : ', @ne, ' | IS DISTINCT FROM : ', @dist,
                 ' | EXISTS...EXCEPT : ', @exc);
END;
GO
UPDATE dbo.Customers
SET Phone = CASE CustomerID WHEN 1 THEN NULL
                            WHEN 2 THEN '512-555-0119' ELSE Phone END
WHERE CustomerID IN (1, 2, 3);

The trigger printed:

<> : 0 | IS DISTINCT FROM : 2 | EXISTS...EXCEPT : 2

Two phones changed. The <> test found zero, because comparing anything with NULL is unknown, not true. Here’s the summary from this run:

Test	Changes found (2 real)	Works on
i.Phone <> d.Phone	0	All versions, but wrong with NULL
i.Phone IS DISTINCT FROM d.Phone	2	SQL Server 2022 (16.x)+, Azure SQL
EXISTS (SELECT i.Phone EXCEPT SELECT d.Phone)	2	All supported versions

I’d use IS DISTINCT FROM when every server is 2022 or newer. EXCEPT has one advantage: it compares a whole list of columns in one predicate, which the ModifiedAt trigger below takes advantage of.

Common Trigger Mistakes: The Single-Row Variable Bug and Msg 512

A trigger fires once per statement, not once per row. That breaks the pattern of copying inserted into variables. Three customers get a $250 raise here:

SET NOCOUNT ON;
GO
CREATE OR ALTER TRIGGER dbo.trg_Customers_Audit ON dbo.Customers
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @id int, @old decimal(10,2), @new decimal(10,2);
    SELECT @id = i.CustomerID, @old = d.CreditLimit, @new = i.CreditLimit
    FROM inserted AS i JOIN deleted AS d ON d.CustomerID = i.CustomerID;
    IF @old <> @new
        INSERT INTO dbo.CustomerHistory (CustomerID, OldLimit, NewLimit)
        VALUES (@id, @old, @new);
END;
GO
UPDATE dbo.Customers SET CreditLimit = CreditLimit + 250
WHERE City IN (N'Denver', N'Austin', N'Tampa');
SELECT CustomerID, OldLimit, NewLimit FROM dbo.CustomerHistory;

CustomerHistory now holds:

CustomerID  OldLimit     NewLimit    
----------- ------------ ------------
          1      5000.00      5250.00

Three updates, one history row. The variables kept whichever row came last, and nothing warned me. You won’t see an error.

No other bug here is this easy to miss, which is why I’d check every old trigger for SELECT @var = ... FROM inserted. If you need to step through one, see how to debug a SQL Server trigger.

The other variant fails loudly. Assigning with a subquery raises Msg 512 and rolls the whole UPDATE back:

SET NOCOUNT ON;
GO
CREATE OR ALTER TRIGGER dbo.trg_Customers_Audit ON dbo.Customers
AFTER UPDATE
AS
BEGIN
    DECLARE @old decimal(10,2) = (SELECT CreditLimit FROM deleted);
END;
GO
BEGIN TRY
    UPDATE dbo.Customers SET CreditLimit = 9000 WHERE CustomerID IN (3, 4);
END TRY
BEGIN CATCH
    PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;
SELECT CustomerID, CreditLimit FROM dbo.Customers WHERE CustomerID IN (3, 4);

Caught error and the rows afterward:

Msg 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
CustomerID  CreditLimit 
----------- ------------
          3      7500.00
          4      3000.00

Both limits are unchanged, because the error inside the trigger undid the statement that fired it. A ROLLBACK in a trigger has a related trap, Msg 3609, the transaction ended in the trigger.

Update a ModifiedAt Column Only When a Value Changes

The other common request: stamp ModifiedAt only for rows that actually changed. Join the table back to inserted and deleted (the same idea as an UPDATE with a JOIN) and compare all tracked columns in one EXCEPT:

SET NOCOUNT ON;
GO
CREATE OR ALTER TRIGGER dbo.trg_Customers_Modified ON dbo.Customers
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE c SET ModifiedAt = SYSDATETIME()
    FROM dbo.Customers AS c
    JOIN inserted AS i ON i.CustomerID = c.CustomerID
    JOIN deleted AS d ON d.CustomerID = i.CustomerID
    WHERE EXISTS (SELECT i.FullName, i.City, i.CreditLimit, i.Phone
                  EXCEPT
                  SELECT d.FullName, d.City, d.CreditLimit, d.Phone);
END;
GO
UPDATE dbo.Customers SET City = City WHERE CustomerID IN (5, 6);
UPDATE dbo.Customers SET City = N'Eugene' WHERE CustomerID = 5;
SELECT CustomerID, City, ModifiedAt FROM dbo.Customers WHERE CustomerID IN (5, 6);
SELECT is_recursive_triggers_on FROM sys.databases WHERE name = DB_NAME();

Result:

CustomerID  City         ModifiedAt                            
----------- ------------ --------------------------------------
          5 Eugene                          2026-10-31 07:48:41
          6 Raleigh                                        NULL
is_recursive_triggers_on
------------------------
                       0

Customer 5 got a timestamp; customer 6, whose City was set to itself, stayed NULL. The trigger’s own UPDATE didn’t fire it again because is_recursive_triggers_on is 0, the default. Turn that on and this design needs a guard; the recursive trigger guide shows one.

COLUMNS_UPDATED() Bitmask: Which Columns Were in the SET List

COLUMNS_UPDATED() returns a varbinary where bit 1 is column 1. Get the bit from COLUMNPROPERTY(..., 'ColumnID'), not from ORDINAL_POSITION. It has the same blind spot as UPDATE():

SET NOCOUNT ON;
GO
CREATE OR ALTER TRIGGER dbo.trg_Customers_Bits ON dbo.Customers
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @bit int = POWER(2, COLUMNPROPERTY(OBJECT_ID(N'dbo.Customers'),
                                N'CreditLimit', 'ColumnID') - 1);
    PRINT CONCAT('COLUMNS_UPDATED() = ', CONVERT(varchar(10), COLUMNS_UPDATED(), 1),
                 ' | CreditLimit bit = ', IIF(COLUMNS_UPDATED() & @bit > 0, 1, 0));
END;
GO
UPDATE dbo.Customers SET CreditLimit = CreditLimit WHERE CustomerID = 3;
UPDATE dbo.Customers SET City = N'Dayton' WHERE CustomerID = 3;
INSERT INTO dbo.Customers (CustomerID, FullName, City, CreditLimit)
VALUES (7, N'Chloe Ward', N'Boise', 1500.00);

Three statements, three bitmasks:

COLUMNS_UPDATED() = 0x08 | CreditLimit bit = 1
COLUMNS_UPDATED() = 0x04 | CreditLimit bit = 0
COLUMNS_UPDATED() = 0x3F | CreditLimit bit = 1

CreditLimit is column 4, so its bit is 0x08, and it’s set even though the value didn’t change. The INSERT set all six bits (0x3F), the same reason an AFTER INSERT trigger sees every column as updated.

I’d only reach for the bitmask when testing many columns at once; past eight columns you also need SUBSTRING.

Where the Answer Flips: Which Trigger Check Should You Use?

Method	Same-value SET	NULL change	Multi-row safe
IF UPDATE(col)	Reports a change	Not checked	Yes
COLUMNS_UPDATED()	Reports a change	Not checked	Yes
i.col <> d.col	Correct	Missed	Yes, in a join
IS DISTINCT FROM / EXCEPT	Correct	Correct	Yes, in a join
Variables from inserted	Correct	Depends on test	No

My rule of thumb: use IF NOT UPDATE(col) RETURN as a cheap gate, then let a NULL-safe inserted/deleted join decide which rows changed.

The answer flips in one case. If you want to block any attempt to write a column, say a primary key, UPDATE() alone is right, because the attempt is the event.

For logging into a separate table, the same join pattern appears in inserting trigger data into another table.

After the Change-Detection Trigger: Where to Go Next

Next step: capture deletes the same way with a trigger on delete that inserts into another table. On MariaDB, compare OLD.col and NEW.col row by row; see MariaDB CREATE TRIGGER.

Other trigger chores you’ll likely hit soon:

• Before you deploy: create a trigger only if it doesn’t exist
• Disable a trigger during bulk loads
• To block bad writes instead of logging them, read about INSTEAD OF triggers

Frequently Asked Questions