Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help understanding COLUMNS_UPDATED

Posted on 2001-06-20
3
Medium Priority
?
536 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

715 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