Decode COLUMNS_UPDATED ()

SQL Server has the ability to provide the information on  specific columns which  been modified by triggers, by using the COLUMNS_UPDATED function. COLUMNS_UPDATED is an in-built function of SQL server that helps to identify the columns in the table or view that were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern showing which columns were inserted or updated. It can be used anywhere inside the body of triggers. COLUMNS_UPDATED function can be use to check the single column or the set of columns updated or inserted.

Usually to decode the varbinary bit mask involves using the substring function and bitwise operator to test the correct bit returned by COLUMNS_UPDATED. The below example tests for updates that affect columns 3, 5, and 9 in the AdventureWorks.Person.Contact table.

USE AdventureWorks;
GO IF OBJECT_ID (N'uContact2', N'TR') IS NOT NULL DROP TRIGGER Person.tr1;
GO CREATE TRIGGER uContact2 ON Person.Contact AFTER UPDATE AS IF (
(SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
AND (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) )
PRINT 'Columns 3, 5 and 9 updated';
GO   UPDATE Person.Contact SET Title=Title, MiddleName=MiddleName,
EmailPromotion=EmailPromotion; GO 

So many questions come in mind, when I first worked with COLUMN_UPDATED function…But remember answer lies with SQL Server only.

From SQL Server 2005 onward, we have a function to decode the varbinary pattern i.e. fn_IsBitSetInBitmask. This function accepts the varbinary value and the Column ID as shown in the below example with which we can return the column names which are inserted or updated in specific table. I wrote below function to utilize this  in the trigger:

    CREATE FUNCTION udf_DecodeBitmask(
      @Tablename        VARCHAR(100),
      @ColumnsUpdated VARBINARY(255)
)
RETURNS @tblColumns TABLE ( ColumnsUpdated      VARCHAR(255) )
AS
BEGIN
      DECLARE @x XML
 
      SET @x = (
      SELECT      COLUMN_NAME
      FROM  INFORMATION_SCHEMA.COLUMNS Field
      WHERE       TABLE_NAME = @Tablename AND
                  sys.fn_IsBitSetInBitmask(
                  @ColumnsUpdated,
                  COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')
                  ) <> 0      FOR XML AUTO, ROOT('Fields')
      )
 
      INSERT      @tblColumns
      SELECT      REPLACE(REPLACE(CAST(T.c.query('.') AS varchar(100)), '', '') AS ColumnsUpdated
      FROM  @x.nodes('/Fields/Field') T(c);     
 
      RETURN      
 
END

To test this function you can use the below script. This will return the list of columns that were updated.

    USE AdventureWorks;
GO
IF OBJECT_ID (N'uContact2_Test', N'TR') IS NOT NULL
    DROP TRIGGER Person.uContact2_Test;
GO
CREATE TRIGGER uContact2_Test ON Person.Contact
AFTER UPDATE AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @ColumnsUpdated VARBINARY(1000) = COLUMNS_UPDATED()
 
 
      SELECT * FROM dbo.udf_DecodeBitmask('Contact', @ColumnsUpdated)
END
GO
 
UPDATE Person.Contact
   SET Title=Title,
      MiddleName=MiddleName,
      EmailPromotion=EmailPromotion;
GO

I hope this function will make your life simpler working with triggers  in SQL Server. Thanks to SQL Server team.

———————-

By Abhijit D. More

I am currently with Capgemini India as a consultant. My core proficiency is Microsoft SQL Server 2005 / 2008 technology in Development and DBA activities – Data modeling, Database Object Creation and modifications, Backup and Recovery, Security Administration, Troubleshooting, Performance Monitoring and Tuning, Database Propagation, Capacity Planning, Product evaluation and Consultation with developers on Technical issues and mainly writing blogs.

I hold the certifications MCITP- Database Developer (2005), MCP, MCTS-SQL Server 2005 implementation and Maintenance.My personal blog is at  http://abhijitmore.wordpress.com/.