Solved

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

Posted on 2005-04-22
1,015 Views
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
Question by:rmaranhao

LVL 28

Accepted Solution

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

LVL 5

Author Comment

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

I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.