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

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

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?

Roberto.
0
rmaranhao
1 Solution

Commented:

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,
Country=Country
GO

0

Author 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

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