SQL Server Trigger Columns Updated Function

Problem

I read in the previous tip about COLUMNS_UPDATED when using a SQL Server trigger, can you further explain how this can be used, so I can understand it better.

Solution

COLUMNS_UPDATED is a very powerful function that can be called within a SQL Server trigger. It allows the trigger to identify which column or columns were updated in the TSQL statement that caused the trigger to execute. The function works by returning a VARBINARY value where each bit corresponds to a column. A bit value of 1 indicates the column was included in the update statement and a value of 0 indicates that the corresponding column was not included. For a more detailed look at the basics of COLUMNS_UPDATED read this previous post.

It is important to know that the bit value of 1 does not necessarily mean that the value in that column was changed. It only means that the column was part of the update statement. For instance, a call to COLUMNS_UPDATED in a trigger called by this statement, UPDATE table1 SET col1 = col1, would return a value of 1 for the col1 bit even though the value was not changed. It would do so because the column was part of the SET statement.

COLUMNS_UPDATED is a simple function, but it does have some strange properties that can throw a programmer for a loop. This post intends to reveal these properties by manufacturing such situations and subsequently offering solutions.

Possible Issue with SQL Server COLUMNS_UPDATED

For this first test, create the following tables and put a row into one of them.

DROP TABLE IF EXISTS dbo.Results;
DROP TABLE IF EXISTS dbo.ThereIsATriggerOnThisTable;
CREATE TABLE dbo.Results (Results VARCHAR(4000));
CREATE TABLE dbo.ThereIsATriggerOnThisTable (
  col01 VARCHAR(100) PRIMARY KEY
, col02 VARCHAR(100) NOT NULL DEFAULT(NEWID())
, col03 VARCHAR(100) NULL
, col04 VARCHAR(100) NULL
, col05 VARCHAR(100) NULL
, colxx VARCHAR(100) NULL
, col06 VARCHAR(100) NULL
, col07 VARCHAR(100) NULL)
GO
INSERT INTO dbo.ThereIsATriggerOnThisTable(col01) VALUES ('BEFORE');

The column colxx is clearly a mistake. Drop that column from the table.

ALTER TABLE dbo.ThereIsATriggerOnThisTable DROP COLUMN colxx;

Now there is a need to create a trigger that will only execute when column Col07 is updated. Looking at the columns we can see that Col07 is the seventh column in the table and as such desire to check for the value of the 64 bit.

Using this information, the trigger is created and then tested.

CREATE TRIGGER dbo.UpdateTrigger ON dbo.ThereIsATriggerOnThisTable AFTER UPDATE AS
BEGIN
  --No sense in continuing to check if no rows were updated.
  IF @@ROWCOUNT = 0 RETURN;
  IF COLUMNS_UPDATED() & 64 = 64 INSERT INTO dbo.Results (RESULTS) VALUES ('col07 was updated');  
END;
GO
--Test the trigger
TRUNCATE TABLE dbo.Results;
UPDATE dbo.ThereIsATriggerOnThisTable SET col07 = 'AFTER';
SELECT * FROM dbo.Results;
SELECT col07 FROM dbo.ThereIsATriggerOnThisTable;
GO

Perhaps unexpectedly, the trigger has failed to alert that col07 was updated. The reason for this goes back to the dropped column, colxx. The bit value to be checked is not based on the fact that col07 is currently the seventh column in the table, but rather that it was the eighth column created and therefore holds the column_id of 8. The query below shows the columns with their corresponding column_id and bit value. 1 must be removed from the column_id since it is 1-based and the bit values are 0-based.

SELECT col.name, col.column_id, POWER(2, col.column_id - 1) BitValue
FROM sys.tables tbl INNER JOIN sys.columns col ON tbl.object_id = col.object_id
WHERE tbl.name = 'ThereIsATriggerOnThisTable'

Conspicuously absent is the column_id of 6 and the corresponding bit value of 32. That pair of values is forever lost on this table. This also shows that the trigger that was just tested is actually checking for an update of col06, not col07 as desired. A quick change to this trigger will fix it.

ALTER TRIGGER dbo.UpdateTrigger ON dbo.ThereIsATriggerOnThisTable AFTER UPDATE AS
BEGIN
  --No sense in continuing to check if no rows were updated.
  IF @@ROWCOUNT = 0 RETURN;
  IF COLUMNS_UPDATED() & 128 = 128 INSERT INTO dbo.Results (RESULTS) VALUES ('col07 was updated');  
END;
GO

Running the same test code now returns a much better result.

The lesson here is to not count on column names or a row counter from sp_help, as seen in the screenshot below, or a similar tool. The metadata must be inspected to determine the appropriate column_id value — without forgetting to subtract 1.

Identifying Which Columns Changed with SQL Server COLUMNS_UPDATED

Each example shown during this article or the previous article on the basics of COLUMNS_UPDATED have had 8 or fewer columns. Something strange happens when there are more than 8 columns. The ninth column of a table, or more accurately, column_id 9, does not correspond to the value 256 as would be expected. Instead SQL Server breaks the binary result into 8 bit pieces and the 9^th column becomes the 1 bit of the second byte. The tenth column becomes the 2 bit of the second byte, and so on. This can be confusing. Hopefully this chart will help clear things up.

If there was a column_id of 17 it would be represented by the 1 bit of the 3^rd byte. If there was a column_id of 61 it would be represented by the 16 bit of the 8^th byte. Luckily there is a formula for all of this!

To see that formula in action, first set up a table with more than 8 columns.

DROP TABLE IF EXISTS dbo.Results;
DROP TABLE IF EXISTS dbo.ThereIsATriggerOnThisTable;
CREATE TABLE dbo.Results (Results VARCHAR(4000));
CREATE TABLE dbo.ThereIsATriggerOnThisTable (
  col01 VARCHAR(100) PRIMARY KEY
, col02 VARCHAR(100) NOT NULL DEFAULT(NEWID())
, col03 VARCHAR(100) NULL
, col04 VARCHAR(100) NULL
, col05 VARCHAR(100) NULL
, colxx VARCHAR(100) NULL
, col06 VARCHAR(100) NULL
, col07 VARCHAR(100) NULL
, col08 VARCHAR(100) NULL
, col09 VARCHAR(100) NULL
, col10 VARCHAR(100) NULL
, col11 VARCHAR(100) NULL
, col12 VARCHAR(100) NULL
, col13 VARCHAR(100) NULL
, col14 VARCHAR(100) NULL
, col15 VARCHAR(100) NULL
, col16 VARCHAR(100) NULL)
GO
INSERT INTO dbo.ThereIsATriggerOnThisTable(col01) VALUES ('BEFORE');
GO

Once again, drop that pesky colxx that doesn’t belong.

ALTER TABLE dbo.ThereIsATriggerOnThisTable DROP COLUMN colxx;

Now, run this query to determine the appropriate bit and byte for every column in the table.

SELECT col.name, col.column_id, (col.column_id-1)/8+1 AS ByteValue, POWER(2,((col.column_id-1)%8)) BitValue
FROM sys.tables tbl INNER JOIN sys.columns col ON tbl.object_id = col.object_id
WHERE tbl.name = 'ThereIsATriggerOnThisTable'

The results should mirror the table above.

Each byte of the binary value returned by COLUMNS_UPDATED must be checked independently. The bytes are isolated using the SUBSTRING function.

This trigger is set up to check each column independently. Remember that multiple columns can be checked within each byte by adding their bit values together.

CREATE TRIGGER dbo.UpdateTrigger ON dbo.ThereIsATriggerOnThisTable AFTER UPDATE AS
BEGIN
  IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) &     1 =     1 INSERT INTO dbo.Results (Results) VALUES ('col01 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) &     2 =     2 INSERT INTO dbo.Results (Results) VALUES ('col02 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) &     4 =     4 INSERT INTO dbo.Results (Results) VALUES ('col03 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) &     8 =     8 INSERT INTO dbo.Results (Results) VALUES ('col04 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) &    16 =    16 INSERT INTO dbo.Results (Results) VALUES ('col05 was updated');  
  --This column was dropped in our demo.  There is nothing to check.
--IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) &    32 =    32 INSERT INTO dbo.Results (Results) VALUES ('colxx was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) &    64 =    64 INSERT INTO dbo.Results (Results) VALUES ('col06 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) &   128 =   128 INSERT INTO dbo.Results (Results) VALUES ('col07 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 2, 1) &     1 =     1 INSERT INTO dbo.Results (Results) VALUES ('col08 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 2, 1) &     2 =     2 INSERT INTO dbo.Results (Results) VALUES ('col09 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 2, 1) &     4 =     4 INSERT INTO dbo.Results (Results) VALUES ('col10 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 2, 1) &     8 =     8 INSERT INTO dbo.Results (Results) VALUES ('col11 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 2, 1) &    16 =    16 INSERT INTO dbo.Results (Results) VALUES ('col12 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 2, 1) &    32 =    32 INSERT INTO dbo.Results (Results) VALUES ('col13 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 2, 1) &    64 =    64 INSERT INTO dbo.Results (Results) VALUES ('col14 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 2, 1) &   128 =   128 INSERT INTO dbo.Results (Results) VALUES ('col15 was updated');  
  IF SUBSTRING(COLUMNS_UPDATED(), 3, 1) &     1 =     1 INSERT INTO dbo.Results (Results) VALUES ('col16 was updated');  
END;
GO

This is easy enough to test. The following test will update 4 columns. The output shows that the trigger was able to uniquely identify each one.

TRUNCATE TABLE dbo.Results;
UPDATE dbo.ThereIsATriggerOnThisTable SET col01 = 'AFTER', col09 = '', col13 = '', col16 = '';
SELECT * FROM dbo.Results;
GO

Some Columns Always Looked Updated using SQL Server COLUMNS_UPDATED

There are some columns that will always report as having updated even when they aren’t referenced in the update statement. Imagine that a trigger needs to execute for any update statement other than one that simply updates a column for the last updated date or last update user. If another column is constantly reporting as having updated this can throw a wrench into the works. There isn’t a way to change this behavior so the only thing to do is to be aware and prepare.

These columns can be identified as having a timestamp or rowversion data type. The following demo will create a table with a timestamp column, create a row in it, then update it.

DROP TABLE IF EXISTS dbo.Results;
DROP TABLE IF EXISTS dbo.ThereIsATriggerOnThisTable;
CREATE TABLE dbo.Results (Results VARCHAR(4000));
CREATE TABLE dbo.ThereIsATriggerOnThisTable (
  col1 VARCHAR(100) PRIMARY KEY
, col2 TIMESTAMP)
INSERT INTO dbo.ThereIsATriggerOnThisTable(col1) VALUES ('BEFORE');
GO
CREATE TRIGGER dbo.UpdateTrigger ON dbo.ThereIsATriggerOnThisTable AFTER INSERT,UPDATE,DELETE AS
BEGIN
       IF COLUMNS_UPDATED() & 3 = 3 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1 AND col2');  
  ELSE IF COLUMNS_UPDATED() & 2 = 2 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col2');  
  ELSE IF COLUMNS_UPDATED() & 1 = 1 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1');
END;
GO

Then run this test. Even though only col1 is part of the statement the trigger reports that both columns were changed. It will always report that col2 has updated because it is a timestamp column. The test would work the same if col2 was changed to a rowversion.

TRUNCATE TABLE dbo.Results;
UPDATE dbo.ThereIsATriggerOnThisTable SET col1 = 'AFTER';
SELECT * FROM dbo.Results;
GO

Next Steps

• Check out the following resources:
  • Learn more about inserted and deleted
  • Working with triggers
  • More SQL Server Trigger tips