Procedure based trigger for audit table

Question

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 procedure that could be used for other tables with similar audit table schema.

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

Note : Temporal tables approach won't work

Answer 1

I will preface by saying that Temporal Tables or SQL Audit are far better for this kind of thing, and you are basically reinventing the wheel.

---

Be that as it may, the below should give you a good model for a trigger

CREATE OR ALTER TRIGGER TR_users ON users
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;  -- prevent issues with bad client drivers

IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    RETURN;  -- early bail-out

INSERT users_audit (users_id, old, new, updated_by)
SELECT
  ISNULL(i.id, d.id),
  i.user_data,
  d.user_data,
  SUSER_SNAME()
FROM inserted i
FULL JOIN deleted d ON d.id = i.id  -- full join to match by all primary key columns
WHERE NOT EXISTS (
    SELECT i.user_data    -- add other columns here
    INTERSECT             -- because INTERSECT deals correctly with nulls
    SELECT d.user_data
);

go

If you want this code for each table, I strongly suggest that instead of trying to write a single dynamic trigger, you instead write a tool that can generate specific triggers and audit tables for each table. Note that primary keys may contain multiple columns and you need to match all of them.