Help understanding COLUMNS_UPDATED

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.
LVL 1
falkorAsked:
Who is Participating?
 
nigelrivettConnect With a Mentor Commented:
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
 
nigelrivettCommented:
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
 
falkorAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.