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.
SQL Server 2005 onward, we have a function to decode the varbinary pattern i.e.
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);
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.
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/.