"Returns a varbinary bit pattern that indicates the columns in a table or view that were inserted or updated. COLUMNS_UPDATED is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions. "
Everything looks easy and cool until you have very wide table and you want to know exactly which column was changed.
COLUMNS_UPDATED() will return strange binary numbers, which represent updated column IDs.
Here I'll present a script for a trigger to get a list of all updated columns:
|CREATE TRIGGER <test_trigger> ON <test_Table>|
AFTER UPDATE AS
DECLARE @i SMALLINT = 1;
DECLARE @k TINYINT = 1;
DECLARE @b TINYINT = 8;
DECLARE @m SMALLINT = 1;
DECLARE @t TABLE(Column_ID INT)
/* Here you capture Binary coded list of changed columns */
DECLARE @Changed_Columns BINARY(128) = COLUMNS_UPDATED();
WHILE @k < 128
WHILE @b > 0
IF CAST(SUBSTRING(@Changed_Columns,@k,1) as SMALLINT) & @m = @m
INSERT INTO @t(Column_ID) VALUES (@i);
SELECT @i += 1, @b -= 1, @m *= 2;
SELECT @b = 8, @m = 1, @k += 1;
/* Here you extract list of fields from the schema */
SELECT c.name as Modified_Column
FROM sys.triggers as r
INNER JOIN sys.columns as c
ON c.object_id = r.parent_id
INNER JOIN @t as t ON t.column_id = c.column_id
WHERE r.object_id = @@PROCID;
Note: Because SQL Server supports up to 1024 columns in single table my assumption is that output of COLUMNS_UPDATED() can not be larger than 128 bytes.