Detect if only one column is changing in a trigger I have an after update trigger that is queuing record ID's into another table for further processing at a later time. It uses inserted to get the ids of the records to process.

I'd now like to exclude certain records being queued to that table if the only change has been to one particular column. If any other columns beyond that one column are changed I still want to queue the record.

How can I identify if only one particular column has been changed?

I've had a look at UPDATE(), but it seems like I'd need to list out all the other 47 columns on the table, which I'd like to avoid.

COLUMNS_UPDATED as used in How to use COLUMNS_UPDATED to check if any of certain columns are updated? seemed promising, but I can't get it to work.
The column I want to exclude has the ColumnID 30. By my figuring that gives me an integer bitmask of 536,870,912 (SELECT POWER(2, 30 - 1)).

The calculator in Windows tells me that is correct to have the 30th bit set.


When I did an UPDATE to just the column I want to exclude COLUMNS_UPDATED() returned 0x000000200040. That to be gives a bit pattern that suggests two completely different columns were updated. Column Id's 7 (2^(7-1)=64) and 22 (2^(22-1)=2,097,152).



I'm not sure it it is worth pursing that approach. It seems like it would be an easy comparison to pickup any changes to other fields.

UPDATE:
I've resorted to using a CHECKSUM comparison other fields between inserted and deleted. I'd prefer not to use this approach as it could cause problems if other fields are added to the table in the future. There is also the chance that a hash collision could occur.
Subject to the limitations mentioned in the other answers, COLUMNS_UPDATED can be used to check if only one (or some) column(s) were targeted by the triggering UPDATE statement.

This can be useful where update statements are written to only mention columns that may be updated. The following script demonstrates:

Table and data

-- A table with an identity key and 48 other integer nullable columns
CREATE TABLE dbo.Test
(
    ID integer IDENTITY PRIMARY KEY,
    col01 integer NULL, col02 integer NULL, col03 integer NULL, col04 integer NULL,
    col05 integer NULL, col06 integer NULL, col07 integer NULL, col08 integer NULL,
    col09 integer NULL, col10 integer NULL, col11 integer NULL, col12 integer NULL,
    col13 integer NULL, col14 integer NULL, col15 integer NULL, col16 integer NULL,
    col17 integer NULL, col18 integer NULL, col19 integer NULL, col20 integer NULL,
    col21 integer NULL, col22 integer NULL, col23 integer NULL, col24 integer NULL,
    col25 integer NULL, col26 integer NULL, col27 integer NULL, col28 integer NULL,
    col29 integer NULL, col30 integer NULL, col31 integer NULL, col32 integer NULL,
    col33 integer NULL, col34 integer NULL, col35 integer NULL, col36 integer NULL,
    col37 integer NULL, col38 integer NULL, col39 integer NULL, col40 integer NULL,
    col41 integer NULL, col42 integer NULL, col43 integer NULL, col44 integer NULL,
    col45 integer NULL, col46 integer NULL, col47 integer NULL, col48 integer NULL
);

-- A single row of sample data
INSERT dbo.Test
(
    col01, col02, col03, col04, col05, col06, col07, col08, col09, col10,
    col11, col12, col13, col14, col15, col16, col17, col18, col19, col20,
    col21, col22, col23, col24, col25, col26, col27, col28, col29, col30,
    col31, col32, col33, col34, col35, col36, col37, col38, col39, col40,
    col41, col42, col43, col44, col45, col46, col47, col48
)
VALUES
(
    01, 02, 03, 04, 05, 06, 07, 08, 09, 10,
    11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
    21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
    31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
    41, 42, 43, 44, 45, 46, 47, 48
);


Trigger

The logic here is to construct the expected value of  COLUMNS_UPDATED when only col30 is targeted by the UPDATE, and to compare that with the value actually encountered. The trigger uses COLUMNPROPERTY to find the id of the column to account for possible future metadata changes. Repeating the logic for col30 would enable other columns to be tested at the same time.

CREATE TRIGGER Test_AU
ON dbo.Test
AFTER UPDATE
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    -- Zero @Mask to the same length as COLUMNS_UPDATED()
    DECLARE @Mask varbinary(128) =
        SUBSTRING(CONVERT(binary(128), 0x), 1, DATALENGTH(COLUMNS_UPDATED()));

    DECLARE 
        @ColumnID integer,  -- metadata ID of the target column
        @Byte integer,      -- byte of @Mask to change
        @Work binary(1);    -- working value of the byte to change

    -- Find the object ID of the trigger's parent table
    DECLARE @OID integer;
    SELECT @OID = T.parent_id FROM sys.triggers AS T WHERE T.[object_id] = @@PROCID;

    /* Set @Mask bits for column(s) of interest */

    -- col30

    -- Find byte, bit, and bit value to change
    SET @ColumnID = COLUMNPROPERTY(@OID, N'col30', 'ColumnId') - 1;
    SET @Byte = 1 + (@ColumnID / 8);

    -- Retrieve the right @Mask byte and set the target bit
    SET @Work = SUBSTRING(@Mask, @Byte, 1);
    SET @Work = @Work | POWER(2, @ColumnID % 8);
    SET @Mask = CONVERT(varbinary(128), STUFF(@Mask, @Byte, 1, @Work));

    -- Test if the resulting mask matches COLUMNS_UPDATED
    -- If not, other columns were updated
    IF @Mask != COLUMNS_UPDATED() PRINT 'Other columns updated';
END;


Tests

Neither of these updates produce a message, since only col30 is targeted by the UPDATE:

UPDATE dbo.Test
SET col30 = col30;

UPDATE dbo.Test
SET col30 = 1;


All of these statements produce the "Other columns updated" message:

UPDATE dbo.Test 
SET col30 = col30, 
    col31 = col31;

UPDATE dbo.Test 
SET col30 = 2, 
    col31 = 3;

UPDATE dbo.Test 
SET col31 = 4;