How to check if only specified column was updated?

by

I try to check in a trigger, if only specific column was updated. When only this column, I want to exit the trigger immediataly.

Here is my Code:

DECLARE @colId int;
SET @colId = COLUMNPROPERTY(OBJECT_ID('dbo.COMMON_T230'), 'F230_100', 'ColumnID');
DECLARE @powerResult bigint;
SET @powerResult = POWER(CAST(2 AS BIGINT), CAST(@colId - 1 as BIGINT));

print @colId;
print COLUMNS_UPDATED();
print @powerResult;
print COLUMNS_UPDATED() & @powerResult;

IF (COLUMNS_UPDATED() & @powerResult != 0)
BEGIN
    RETURN; -- Trigger beenden, wenn NUR F230_100 aktualisiert wird
END

But it is not working.
when I do

update common_t230 set F230_100 = getdate() where F230_id = 1

I am getting the following outputs:

33
0x0000000001
4294967296
0
2

Why this is not working?

  • 2

    Note: “If a trigger applies to a column, the COLUMNS_UPDATED returns as true or 1, even if the column value remains unchanged. This is by-design, and the trigger should implement business logic that determines if the insert/update/delete operation is permissible or not.”

    – 

  • 1

    “But it is not working” What does “not working” mean; that isn’t a helpful problem statement if you don’t explain what it means.

    – 

It’s not enough to test if that specific column was updated, you also need to test that other columns where not updated. For that you need to add another condition – COLUMNS_UPDATED() & ~@powerResult = 0 – the ~ operator is a bitwise not – so this condition make sure no other column was updated.
Note that if your table have column sets, things can get a bit more tricky.

-- I've taken the liberty to shorten your calculation here
DECLARE @colId int = 
  POWER(2, COLUMNPROPERTY(OBJECT_ID('dbo.T'), 'C', 'ColumnID')-1);

-- the condition
IF COLUMNS_UPDATED() & @colId > 0 AND COLUMNS_UPDATED() & ~@colId = 0
    RETURN; -- Trigger beenden, wenn NUR F230_100 aktualisiert wird

-- rest of the trigger code here

You can see a live demo on db<>fiddle

Also, as I wrote in the comments – The fact that a column tests positive in the Columns_Updated() function doesn’t actually means its value was changed. You should write logic to verity that if it’s important for you to know.

From the remarks section:

If a trigger applies to a column, the COLUMNS_UPDATED returns as true or 1, even if the column value remains unchanged. This is by-design, and the trigger should implement business logic that determines if the insert/update/delete operation is permissible or not.

Leave a Comment