There are three ways one can check if a column was updated inside a trigger:
- Check for the value of UPDATE(Column_Name)
- Check for the value of COLUMNS_UPDATED() & integer mask for the column updated (also works for more than one column)
- 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.
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.