Reschenie problemi ot Microsoft, vrode rabotaet

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