Explanation of columns_updated?

[timscronin]

the statement
if ((columns_updated() & 2 + 4 + 8)) > 0) is supposed to tell me if the 2nd, 3rd or 5th columns were updated. My question is, what desginates the column 2,3,5, when 2,4,8 are in the statement?

 

[donutman]

Tim, I haven't found the need to do triggers, but what the statement is doing is a simple math trick. By taking two binary numbers (1's and 0's) and performing an "and" operation on them, you can determine if the value of a particular "bit" is either a 1 or 0.
For example the 8-bit binary number for 4 is 00000100. If you "and" that with any binary number that has a 0 in the 3rd rightmost position, then the result is 00000000. However, if you "and" it with a binary number like 11111111, then you get a binary 00000100.
I'm sure this sounds a little confusing, so the best thing to do is read about binary math. I'm sure a google search would be productive.
BTW, the 2, 4, and 8 tell you about column 2, 3 and 4 not column 5.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JohnDTampaBay]

I assume you mean, the 2nd, 3rd or 4th columns, mirroring the example from BOL, since 2+4+8=14.

[tt] IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/[/tt]

The value returned by COLUMNS_UPDATED is a bitwise representation of the columns in the table: a set of switches turned on or off, 1 or 0, updated or not.

The leftmost column in the table is represented by the leftmost bit position, the next the second position, etc. The value of that position is 2^position.

[tt]Name Col1 Col2 Col3 Col4
Position 1 2 3 4
Value 1 2 4 8[/tt]

The valuation only applied if the bit is turned on, otherwise the value is 0. With COLUMNS_UPDATED, the bit will only be on if the column in that position has been updated. Up to 8 columns can be tested easily. Check out BOL for more details on more than 8 columns.

The ampersand (&) is a bitwise operator that looks at the value of each bit.

[tt](COLUMNS_UPDATED() & 14) = 14[/tt]
could be written
[tt](COLUMNS_UPDATED() & 2+4+8) = 14[/tt]

Since the values 2, 4, and 8 represent positions 2, 3, and 4 respectively, I read this script as, "Have columns 2, 3, and 4 ALL been updated?"

Note that your example above is slightly different than BOL:

[tt]if ((columns_updated() & 2 + 4 + 8)) > 0)[/tt]

I read this script as, "Have ANY of columns 2, 3, and 4 been updated?"

I hope this helps. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)