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/.
]]>