`columns_updated()` demo

demo-alter-trigger.sql

use [master]
go
drop database if exists TriggerDemo;
go
create database TriggerDemo;
go
use TriggerDemo
go
create table dbo.foo (
    id int not null primary key,
    a char(1),
    b char(1),
    c char(1),
    d char(1),
    e char(1),
    f char(1),
    g char(1), -- 8
    updated datetime2(0) -- 9
);
go
create trigger dbo.foo_trigger 
    on dbo.foo
    instead of update
as
begin
    set nocount on;

    update f 
    set 
        f.a = i.a,
        f.b = i.b,
        f.c = i.c,
        f.d = i.d,
        f.e = i.e,
        f.f = i.f,
        f.g = i.g,
        f.updated = getdate()
    from dbo.foo as f
    join inserted as i on i.id = f.id;
end
go
insert foo 
    (id, a, updated) -- 0xFF01 
values
    (1, 'a', getdate());
go
select * 
from foo;
go
update foo 
set b = 'b'
go
alter trigger dbo.foo_trigger 
    on dbo.foo
    after insert, update
as
begin
    set nocount on;

    declare @timestamp_is_updated bit = (
        select iif(columns_updated() & c.[column_id] = 1,1,0)
        from sys.columns as c
        where c.[object_id] = object_id('dbo.foo')
            and c.[name] = 'updated'
    );
    if(@timestamp_is_updated = 0)
    begin
        update f
        set f.updated = getdate()
        from dbo.foo as f
        join inserted as i on i.id = f.id;

        declare @msg nvarchar(max);

        select 
            @msg = concat(
                'timestamp updated from trigger for rows "',
                string_agg(id,',') within group (order by id asc),
                '".'
            )
        from inserted;

        print @msg;
    end;
end;
go
update foo 
set c = 'c';

insert foo
    (id, d) 
values
    (3,'d');
go
insert foo
    (id, e) 
values
    (4,'e');
go
update foo 
set f = 'f'
where id % 2 = 1;

demo-columns_updated.sql

use [master]
go
drop database if exists TriggerDemo;
go
create database TriggerDemo;
go
use TriggerDemo
go
create table dbo.foo (
    id int not null primary key,
    a char(1),
    b char(1),
    c char(1),
    d char(1),
    e char(1),
    f char(1),
    g char(1), -- 8
    updated datetime2(0) -- 9
);
go
alter trigger dbo.foo_trigger 
    on dbo.foo
    after insert, update
as
begin
    print columns_updated();

    declare @timestamp_is_updated bit = (
        select iif(columns_updated() & c.[column_id] = 1,1,0)
        from sys.columns as c
        where c.[object_id] = object_id('dbo.foo')
            and c.[name] = 'updated'
    );
    if(@timestamp_is_updated = 1)
    begin
        print 'timestamp was updated';
    end;
end;
go
insert foo 
    (id, a, updated) -- 0xFF01 
values
    (1, 'a', getdate());
go
update foo 
set updated = getdate(); -- 0x0001

update foo 
set a = 'b'; -- 0x0200

update foo 
set id = 2; -- 0x0100
go
select 0x0001 & 9
select 0xFF01 & 9
go
insert foo -- insert is always a full mask
    (id, a) 
values
    (3,'c');
go