Go Premium for a chance to win a PS4. Enter to Win


Help understanding COLUMNS_UPDATED

Posted on 2001-06-20
Medium Priority
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.
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.
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.


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.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

916 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