How to get names of the 'only updated columns' in a trigger?

Question

I would like to print the names of the 'only updated columns' from inside my trigger.

For instance, my table has three columns - ColA, ColB and ColC.

If I update only ColB, my trigger should print only ColB.

If I update only ColA & ColC, my trigger should print only ColA & ColC.

May I know how to achieve this in a shorter and cleaner way please?

You might be interested in the first link returned if you type -->"SQL SERVER how to determine which columns were updated inside a trigger" into google — Ross Bush, Jan 30 '19 at 17:51
https://dev.to/ravenous_baboon/checking-if-the-column-was-updated-inside-sqlserver-update-trigger I checked it. It was useful. Thank you. — MAK, Jan 30 '19 at 18:33

score 1 · Answer 1 · answered Jan 30 '19 at 18:49

This site helped me - https://ask.sqlservercentral.com/questions/44368/columns-updated-not-returning-the-column-name.html

create table Sample1
(
    a varchar(10),
    b varchar(10),
    c varchar(10)
);

alter trigger TR_Sample1_Update ON Sample1 for update as 
DECLARE @modifiedColumns nvarchar(max) 
SET @modifiedColumns = STUFF((SELECT ',' + name FROM sys.columns WHERE object_id = OBJECT_ID('Sample1') AND COLUMNS_UPDATED() & (POWER(2, column_id - 1)) <> 0 FOR XML PATH('')), 1, 1, '') 
PRINT @modifiedColumns
go

update Sample1 set a = 1 where a = 1
update Sample1 set b = 4 where a = 1
update Sample1 set c = 5 where a = 1
update Sample1 set a = 1, c = 5 where a = 1
update Sample1 set a = 1, b = 4, c = 5 where a = 1

It worked. Please try it in LinqPad.

Not working for me. Overflow exception if many columns. Works this (see all answers): https://stackoverflow.com/a/57349054/2399045 — Pavel Samoylenko, Aug 04 '19 at 18:06

score 0 · Accepted Answer · answered Jan 30 '19 at 17:54

Personally I hate triggers and try to avoid them most of the time ;-)

However, the only way I know of is with the function COLUMNS_UPDATED() inside your trigger code. See also examples like: https://sites.google.com/site/bugsarewelcome/home/columns_updated

How to get names of the 'only updated columns' in a trigger?

2 Answers2