Checking if the column was updated inside SQLServer update trigger

There are three ways one can check if a column was updated inside a trigger:

1. Check for the value of UPDATE(Column_Name)
2. Check for the value of COLUMNS_UPDATED() & integer mask for the column updated (also works for more than one column)
3. Check if a column appears in an inserted table - IF EXISTS(SELECT Column_Name FROM inserted)

However these three do not work in the same way.

Example:
We have a table dbo.Customers and we want to check for updates on its first column, LastName. We will create a trigger which will incorporate all three of the methods. It will print a message for each condition that was met:

CREATE TRIGGER [dbo].[CustomersUpdateTrigger]
ON [dbo].[Customers]
AFTER UPDATE
AS
  BEGIN
      IF UPDATE(LastName)
        PRINT 'UPDATE(LastName)'

      IF ( COLUMNS_UPDATED () & 1 = 1 )
        PRINT 'COLUMNS_UPDATED ( )  & 1'

      IF EXISTS(SELECT LastName
                FROM   inserted)
        PRINT 'EXISTS(SELECT LastName FROM inserted)'
  END

Now let's run a rather specific update statement against the Customers table:

UPDATE dbo.Customers
SET    lastname = 'SomeNewLastName'
WHERE  0 = 1  

Clearly, no row is going to be updated. How many messages are we going to see?

UPDATE(LastName)
COLUMNS_UPDATED ( )  & 1

(0 row(s) affected)

Conclusion: Checks for UPDATE() and COLUMNS_UPDATED () return true if a column we check for was in an update list, regardless of an actual data update. For the IF EXISTS(SELECT Column_Name FROM inserted) check we need some actual data to be updated.

Microsoft docs references:
UPDATE()
COLUMNS_UPDATED

Comments

[Jamey Evans]

This was very useful! Thanks for sharing.