x
Solved

# Help understanding COLUMNS_UPDATED

Posted on 2001-06-20
Medium Priority
538 Views
Ok, I've read the books online and knowledge base descriptions.  Call me dense, but I still need an english translation of how to use this function.  I have a 15 column table, and in my trigger I need to check to see if columns 7 to 14 (inclusive) have been updated.

OK, this is what I'm thinking:

IF SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(7-1)) --col 7
SELECT @RollBackCntr = @RollBackCntr + 1
IF SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)) --col 9
SELECT @RollBackCntr = @RollBackCntr + 1
IF SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(3-1)) --col 11
SELECT @RollBackCntr = @RollBackCntr + 1
IF SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(5-1)) --col 13
SELECT @RollBackCntr = @RollBackCntr + 1
IF SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(8-1)) --col 8
SELECT @RollBackCntr = @RollBackCntr + 1
IF SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(2-1)) --col 10
SELECT @RollBackCntr = @RollBackCntr + 1
IF SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(4-1)) --col 12
SELECT @RollBackCntr = @RollBackCntr + 1
IF SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(6-1)) --col 14
SELECT @RollBackCntr = @RollBackCntr + 1

Well, this seems to work, but only if one of the fields in question is modified.  If I use an update query and update more then one, @RollBackCntr will = 0.  Can anyone tell me why this is?  If I use the update query and change only one of these fields, @RollBackCntr will = 1.
0
Question by:falkor
• 2

LVL 18

Accepted Solution

nigelrivett earned 200 total points
ID: 6211173
Because columns_updated is a bit mask.
You need

if COLUMNS_UPDATED() & power(2,(7-1) <> 0
...

or you could
if COLUMNS_UPDATED() & power(2,(7-1) =  power(2,(7-1)

if you just want to check if any of the columns have been updated just sum the bits and use the <> 0 option.
0

LVL 18

Expert Comment

ID: 6211186
I see why you have the substring (ish)

col 7   power(2,7-1)
col 8   power(2,8-1)
col 9   power(2,9-1)

I think.

0

LVL 1

Author Comment

ID: 6211450
Thank you.  Based on your respones, this works for my problem:

IF COLUMNS_UPDATED() & power(1,(7-1)) <> 0 --col 7
SELECT @RollBackCntr = @RollBackCntr + 1;
IF COLUMNS_UPDATED() & power(2,(9-1)) <> 0 --col 9
SELECT @RollBackCntr = @RollBackCntr + 1;
IF COLUMNS_UPDATED() & power(2,(11-1)) <> 0 --col 11
SELECT @RollBackCntr = @RollBackCntr + 1;
IF COLUMNS_UPDATED() & power(2,(13-1)) <> 0 --col 13
SELECT @RollBackCntr = @RollBackCntr + 1;
IF COLUMNS_UPDATED() & power(1,(8-1)) <> 0 --col 8
SELECT @RollBackCntr = @RollBackCntr + 1;
IF COLUMNS_UPDATED() & power(2,(10-1)) <> 0 --col 10
SELECT @RollBackCntr = @RollBackCntr + 1;
IF COLUMNS_UPDATED() & power(2,(12-1)) <> 0 --col 12
SELECT @RollBackCntr = @RollBackCntr + 1;
IF COLUMNS_UPDATED() & power(2,(14-1)) <> 0 --col 14
SELECT @RollBackCntr = @RollBackCntr + 1;

It seems that because columns 7 and 8 are in the 1st byte returned, they have to be accessed by power(1, ...), and the rest of the columns are in the second byte, accessed by power(2, ...).

I think I understand it now, thank you.
-falkor
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.