'Function based trigger for audit table

I have 2 tables, one main table and one audit table.

create sequence dbo.users_seq;
create table dbo.users 
(
 id bigint primary key default(next value for dbo.users_seq),
 user_data nvarchar(max) not null check(isjson(user_data) = 1),
 timestamp datetime2 not null default sysdatetime(),
 updated_timestamp datetime2 not null default sysdatetime()
);

create sequence dbo.users_audit_seq;
create table dbo.users_audit
(
 id bigint primary key default(next value for dbo.users_audit_seq),
 users_id bigint not null, --id from `users` table
 old nvarchar(max) not null check(isjson(old) = 1), --original row from `users` table
 new nvarchar(max) not null check(isjson(new) = 1), --new row from `users` table
 updated_by varchar(100) not null, --username info
 timestamp datetime2 not null default sysdatetime()
);

I am looking to create a for update trigger on users main table which calls a generic function that could be used for other tables with similar audit table schema.

The generic function should write to it's audit table (users table should write to users_audit table but the same function should also work for users2 table and write to users2_audit table).



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source