Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Trigger Columns Updated Function


By:   |   Last Updated: 2019-03-22   |   Comments (3)   |   Related Tips: More > Triggers

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.

Column Col07 Col06 Col05 Col04 Col03 Col02 Col01
Place 64 32 16 8 4 2 1

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'			
The column colxx was originally column_id 6.  It was dropped, but the subsequent columns are still 7 and 8.  There is no column_id 6 anymore.

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 test now returns a value indicating that the trigger recognizes the change to col07.

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.

This output from sp_help suggests that col07 is the 7th column, but this is misleading and inaccurate for use with COLUMNS_UPDATED.

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 9th 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.

Column_id 8 7 6 5 4 3 2 1 16 15 14 13 12 11 10 9
Bit value 128 64 32 16 8 4 2 1 128 64 32 16 8 4 2 1
Byte 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2

If there was a column_id of 17 it would be represented by the 1 bit of the 3rd byte. If there was a column_id of 61 it would be represented by the 16 bit of the 8th 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.

This chart mirrors the table above where column_ids 8-16 appear on byte 2 and column_id 17 is the 1 bit on byte 3.

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

SUBSTRING(COLUMNS_UPDATED(), [Enter the byte value], 1) & BitValue = BitValue

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			
The output shows that all 4 updated columns were noticed by the trigger.

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		
The trigger output shows that both col1 and col2 were updated.
Next Steps


Last Updated: 2019-03-22


get scripts

next tip button



About the author
MSSQLTips author Eric Blinn Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, March 30, 2019 - 3:32:12 PM - Ray Herring Back To Top

The Columns_Updated function is so "brittle" that I can't think of even one or two reasonable use cases for it.  I guess if the return value is > 0 it tells you something was updated but presumably that is the reason the trigger is executing anyway.

The Update() function is much safer and much more explicit when you are troubleshooting.  Even nest, multi-level logic with the Update() function is still legible.

Trying to parse the Columns_Updated() output at 2AM is not something I want to have in my future.  Actually, troubleshooting a trigger at 2AM is not something I want either :).


Saturday, March 23, 2019 - 2:45:38 PM - Eric Blinn Back To Top

@Jeff

The remaining column_id values will hold and the value for the deleted column will forever return a 0 in the bit map. 


Friday, March 22, 2019 - 8:08:27 PM - jmoden Back To Top

Good article.  Thanks for writing it.

I have to ask because I've not tried it... what happens to the numbering if you delete a column?


Learn more about SQL Server tools