September 17, 2008 at 7:10 am
To test for updates that affect columns other than the first eight columns in a table, use the SUBSTRING function to test the correct bit returned by COLUMNS_UPDATED. The following example tests for updates that affect columns 3, 5, and 9 in the AdventureWorks.Person.Contact table.
Copy Code
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
I read the above text.
But not able to understand how to check columns 11 and 12 are updated?
Pls clarify
September 17, 2008 at 8:13 am
Read how this function works: http://www.microsoft.com/technet/abouttn/flash/tips/tips_112304.mspx
It returns a bitmask of the columns. So image you have 8 columns (numbered in the order they exist in the table). You get 1 byte back. If you OR that to check value of bit 1, it will be 0 if column 1 was not updated, 1 if it is.
September 17, 2008 at 8:25 am
I would recommend reading the article on this but here's a quick overview. The SUBSTRING of COLUMNS_UPDATE() function operates as follows:
SUBSTRING(
COLUMNS_UPDATED(), -- Bitmask values
1, -- 1 for first 8 columns, 2 for second 8 columns (incl. 11 and 12)
1 -- always 1 to return the character for the 8 column section
)
So for column 11 you'd want something like:
IF(SUBSTRING(COLUMNS_UPDATED(), 2, 1) & 4) = 4
PRINT 'Column 11 updated'
Hth,
September 17, 2008 at 10:29 pm
When i update 5th and 11th coulmn, print print COLUMNS_UPDATED() displays "0x100400".
What it means?
September 18, 2008 at 2:35 am
If you take a look at the results of these statements:
SELECT SUBSTRING(0x100400, 1, 1) & 16
SELECT SUBSTRING(0x100400, 2, 1) & 4
They return 16 and 4 respectively.
You can think of the returned bitmask a string containing one character per 8 columns of the table. If you update any of the columns from 1 to 8 inclusive, you should use SUBSTRING(COLUMNS_UPDATED(), 1, 1) with a bitwise and operator (&). For columns 9-16 you would check the second character, 17-24 the third and so on. If you were checking that the following columns were updated you would use the corresponding statment:
column 1: SUBSTRING(COLUMNS_UPDATED(), 1, 1) & 1 = 1
column 2: SUBSTRING(COLUMNS_UPDATED(), 1, 1) & 2 = 2
column 3: SUBSTRING(COLUMNS_UPDATED(), 1, 1) & 4 = 4
...
column 8: SUBSTRING(COLUMNS_UPDATED(), 1, 1) & 128 = 128
column 9: SUBSTRING(COLUMNS_UPDATED(), 2, 1) & 1 = 1
...
column 28: SUBSTRING(COLUMNS_UPDATED(), 4, 1) & 8 = 8
Notice how for column 9 you check for the second character in the COLUMNS_UPDATED() result and restart the bit counter to 1; and for the 28th column you're now in the 4th batch of 8 columns and are checking the 4th character of the string. Using the bitwise & to see if the 4th bit contains the correct values as you are looking at the 4th column in that 4th batch
Remember that the return value itself is a bitmask and is not supposed to be human-readable. You can, however, use it's value to check which columns have been updated using the statements shown above.
Checkout BOL for a much better description of the bitwise and (&) operator.
Hope this makes things a bit clearer. 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply