[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1030
  • Last Modified:

How to determine witch column was updated during a trigger if the COLUMNS_UPDATED() is larger then the integer value

I have a trigger that runs after update on a table.

Microsoft tells that the way to determine witch column was modified is to use

IF COLUMNS_UPDATED() & ColumnNumber > 0 . My problem is that due to the number of columns on this table the ColumnNumber is 8589934592. This number is bigger than the integer value and if I cast it as a BigInt the bitwise operator & does not work.

Does anybody now a way around this?

Thanks in advance,
Roberto.
0
rmaranhao
Asked:
rmaranhao
1 Solution
 
rafranciscoCommented:
This is from Books Online.  This might help you.

F. Use COLUMNS_UPDATED to test more than 8 columns
If you must test for updates that affect columns other than the first 8 columns in a table, you must use the SUBSTRING function to test the proper bit returned by COLUMNS_UPDATED. This example tests for updates that affect columns 3, 5, or 9 in the Northwind.dbo.Customers table.

USE Northwind
DROP TRIGGER  tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
   IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
      + power(2,(5-1)))
      AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
      )
   PRINT 'Columns 3, 5 and 9 updated'
GO

UPDATE Customers
   SET ContactName=ContactName,
      Address=Address,
      Country=Country
GO

0
 
rmaranhaoAuthor Commented:
For the question log:

An easy way to determine the " power(2, (n-1)) "  value is to print the SUBSTRING(COLUMNS_UPDATED(),n,1 and then convert this hexadecimal value to decimal and then work from there...

The code would look loke this:

IF (SUBSTRING(COLUMNS_UPDATED(),1,1)=20) --  Columns 3 and 5 (2^2 + 2^4)
     
      AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=1) -- Column 9   (Second group, 2^0)
      )


I think it's easier to read this way.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now