DEV Community

Ravenous Baboon
Ravenous Baboon

Posted on


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.

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]
      IF UPDATE(LastName)
        PRINT 'UPDATE(LastName)'

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

                FROM   inserted)
        PRINT 'EXISTS(SELECT LastName FROM inserted)'
Enter fullscreen mode Exit fullscreen mode

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

UPDATE dbo.Customers
SET    lastname = 'SomeNewLastName'
WHERE  0 = 1  
Enter fullscreen mode Exit fullscreen mode

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


(0 row(s) affected)
Enter fullscreen mode Exit fullscreen mode

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:

Top comments (1)

jameyevans profile image
Jamey Evans

This was very useful! Thanks for sharing.

Super Useful CSS Resources

A collection of 70 hand-picked, web-based tools which are actually useful.
Each will generate pure CSS without the need for JS or any external libraries.