Detect if only one column is changing in a trigger

Question

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.

Answer 1

You should be able to create two temp tables (#deleted, #inserted) using SELECT * INTO syntax and then alter the temp tables dropping the column you don't care about. Then use EXCEPT to find if other columns have changed.

set nocount on
IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL 
  DROP TABLE dbo.TestTable; 
Create table dbo.TestTable (Id int, Col1 varchar(10), Col2 varchar(10), Col3 varchar(10))

insert into dbo.TestTable (Id, Col1, col2, col3) values(1,'one','one','one')
insert into dbo.TestTable (Id, Col1, col2, col3) values(1,'two','two','two')
go
create TRIGGER dbo.AfterUpdateTrigger ON dbo.TestTable
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * INTO #deleted FROM deleted
    SELECT * INTO #inserted FROM inserted

    ALTER TABLE #deleted DROP COLUMN col2
    ALTER TABLE #inserted DROP COLUMN col2

    select * from #deleted
    except 
    select * from #inserted

END
GO

update dbo.TestTable set col2 = 'three'
update dbo.TestTable set Col1 = 'three'

Answer 2

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;

Answer 3

A comparison of the values in the inserted and deleted tables is your only option, because UPDATE() and COLUMNS_UPDATED will be true even if the value hasn't actually changed (i.e. has simply been overwritten with the previous value).

To (largely) future-proof your trigger you could dump inserted and deleted into temp tables, and generate dynamic SQL for the comparison based on the temp table structures (in conjunction with Update()) which you can obtain with this:

Select st.name, sc.* From tempdb.sys.columns sc
join sys.types st on sc.system_type_id=st.system_type_id AND sc.user_type_id=st.user_type_id
Where object_id=OBJECT_ID('tempdb.dbo.#TempTableName');

You have to consider whether or not you actually want your trigger doing all that though, and I can't speak to the stability.

For the comparisons, any particular reason why CHECKSUM instead of say i.colname=d.colname OR (i.colname IS NULL AND d.colname IS NULL) ?

Answer 4

Using some of the advice from the above answers (thanks in particular to @Paul White).

I put together this function:

DROP FUNCTION IF EXISTS dbo.fn_OnlyColumnUpdated
GO

/*
 Function that can be used to pass in a column number and the COLUMNS_UPDATED() bitmap (in a trigger)
 to check if a particular column was the only one updated.
 Usage example (in an update trigger):
 -- check if MyDB.dbo.MyTable.MyColumn was only column in update statement*
    IF MySharedDB.dbo.fn_OnlyColumnUpdated(COLUMNPROPERTY(OBJECT_ID('dbo.MyTable'), MyColumn', 'ColumnId'), COLUMNS_UPDATED()) = 1
        RETURN
*/
CREATE FUNCTION dbo.fn_OnlyColumnUpdated
(
    @ColumnID INT,  /* when used from within a trigger, use the Column_ID.  When used from replication use the Column Ordinal number */
    @ColumnUpdateBitmap VARBINARY(128)
)
RETURNS BIT
AS
BEGIN
    /* if MyColumn only column updated don't do anything else */
    DECLARE 
        @Mask varbinary(128),
        @ColumnByte BINARY(1),
        @ByteOrdinal INT,
        @AppliedList VARBINARY(128),
        @result BIT

    SELECT 
        /* find the column ID for the AccessLevel column in this table */
        /* build a varbinary variable of same length as COLUMNS_UPDATED() */
        @Mask = SUBSTRING(CONVERT(binary(128), 0x), 1, DATALENGTH(@ColumnUpdateBitmap)),
        /* Assign to a BINARY(1) the byte value representing that column being the only one used */
        @ColumnByte = POWER(2, (@ColumnID - (((@ColumnID / 8)-1) * 8) -1)),
        /* identify the array position holding the MyColumn Byte */
        @ByteOrdinal = @ColumnID / 8,
        /* now rebuild the varbinary representation of what columns are set (stuff the @ColumnByte into the right position in the array of bytes) */
        @AppliedList = CONVERT(varbinary(128), STUFF(@Mask,@ByteOrdinal,1,@ColumnByte ))

    SELECT 
        @result = 
            CASE WHEN @ColumnUpdateBitmap = @AppliedList THEN 1
                ELSE 0
            END
    RETURN @result
END
GO

This can then be used within your trigger (or within a replication update stored procedure) to check if that was the only column included in the update statement*

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

    IF MySharedDB.dbo.fn_OnlyColumnUpdated(COLUMNPROPERTY(OBJECT_ID('dbo.MyTable'), MyColumn', 'ColumnId'), COLUMNS_UPDATED()) = 1
    BEGIN
        PRINT 'Only MyColumn has been updated'
    END
    ELSE
    BEGIN
        PRINT 'Other columns have been updated'
    END
END;
GO

---

Important note: COLUMNS_UPDATED() from within a trigger includes the list Columns that have been included in the SET, it is NOT restricted to the columns that had changed values, ie:

UPDATE dbo.MyTable
SET MyColumn = 'test',
    AnotherColumn = AnotherColumn
WHERE ID = 1

Will produce a value in COLUMNS_UPDATED() showing that both columns have been updated!