Microsoft KB Archive/232195

= INF: Proper Use of the COLUMNS_UPDATED Function =

Article ID: 232195

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q232195



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: kbinfo KB232195

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.