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
Last Modified: 2012-05-05
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,
Question by:rmaranhao
    LVL 28

    Accepted Solution

    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
    CREATE TRIGGER tr1 ON Customers
       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'

    UPDATE Customers
       SET ContactName=ContactName,

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    I recently came across an interesting Question In EE ( 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.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now