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;