INF: Proper Use of the COLUMNS_UPDATED() Function

• Microsoft SQL Server version 7.0

SUMMARY

The COLUMNS_UPDATED() clause in a CREATE TRIGGER or ALTER TRIGGER statement can be used to check which columns in a table were updated by an INSERT or UPDATE statement. This clause returns a varbinary indicating the columns that have changed and you can use an integer bitmask to specify the columns to test.

MORE INFORMATION

The example provided in SQL Server Books Online of the use of the COLUMNS_UPDATED() clause in a CREATE TRIGGER statement is correct if the table on which the trigger is placed has no more than eight columns. The bits within a byte of the varbinary returned by COLUMNS_UPDATED() are to be read from right to left. So, if you wish to check if columns 2, 3, or 4 have been updated, the correct bitmask to use is 0x0E (binary 00001110, decimal 14).

However, if there are more than eight columns, the COLUMNS_UPDATED() function returns the bytes in order from left to right, with the least significant byte being the leftmost. The leftmost byte will contain information about columns 1 through 8, the second byte will contain information about columns 9 through 16, and so on. If there were nine columns in the table and you want to check if columns 2, 3, or 4 have been updated, the correct bitmask to use is 0x0E00 (decimal 3584).

Since the bitwise operator only works on 32-bit integers, you may have difficulty checking a table with more than 32 columns. The correct bitmask to check if columns 3, 5, and 9 have changed when there are 16 columns or less is 0x1401 (decimal 5121). The correct bitmask is 0x140100 if there are 24 columns or less, 0x14010000 if 32 columns or less, and so on.

Therefore, if there are more than eight columns, you will need to use SUBSTRING to extract the bytes separately:

USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))+ power(2,(5-1))) 
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))) 
PRINT 'Columns 3, 5 and 9 updated'
GO

UPDATE Customers 
SET ContactName=ContactName,
Address=Address,
Country=Country
GO 

Additional query words: bit mask audit literal hex

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo