Solved

Help understanding COLUMNS_UPDATED

Posted on 2001-06-20
3
532 Views
Last Modified: 2007-12-19
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
Comment
Question by:falkor
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 50 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

by:nigelrivett
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

by:falkor
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question