Trigger for capturing changed columns in 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),
 name varchar(100) not null, --user's full name
 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
 query varchar(max) not null, --query used for update
 updated_by varchar(100) not null, --username info
 timestamp datetime2 not null default sysdatetime()
);

I am looking to create an after update trigger on users main table that could be used for capturing changed columns (excluding timestamps) in users_audit table. (Example below)

I am able to manually do this through json_modify() and OPENJSON(@json but unable to get it working automatically through a trigger

Initial insert:

id	name	user_data	timestamp	updated_timestamp
1	John	{"email":"jdoe@abc.com"}	2021-05-08 18:10:02.0474381	2021-05-08 18:10:02.0474381

Sample update:

id	name	user_data	timestamp	updated_timestamp
1	John Doe	{"email":"jdoe@abc.com","address":"123 Main St"}	2021-05-08 18:10:02.0474381	2021-05-08 18:12:06.0474381

After the above update audit table should look like:

id	users_id	old	new	query	updated_by	timestamp
1	1	{"name":"John","user_data":{"email":"jdoe@abc.com"}}	{"name":"John Doe","user_data":{"email":"jdoe@abc.com","address":"123 Main St"}}	update query	username	2021-05-08 18:12:06.0474381

Sample update 2:

id	name	user_data	timestamp	updated_timestamp
1	John	{"email":"jdoe@abc.com","address":"123 Main St"}	2021-05-08 18:10:02.0474381	2021-05-08 18:14:16.0474381

After the above update2 audit table should look like: (old and new not capturing user_data as it hasn't changed)

id	users_id	old	new	query	updated_by	timestamp
1	1	{"name":"John","user_data":{"email":"jdoe@abc.com"}}	{"name":"John Doe","user_data":{"email":"jdoe@abc.com","address":"123 Main St"}}	update query	username	2021-05-08 18:12:06.0474381
2	1	{"name":"John Doe"}	{"name":"John"}	update query	username	2021-05-08 18:14:16.0474381

Note : Temporal tables or SQL Audit approaches won't work

Solution

Here is one way to do it.

The principles are mostly the same as mentioned on an earlier answer. The main differences are:

Get the starting batch using dm_exec_input_buffer. You need server-level permissions for this.
FOR JSON will not show a key which has a NULL value, so we can use SELECT...EXCEPT to remove values which are the same between inserted and deleted.
JSON_QUERY is necessary to prevent double-escaping of existing JSON objects

CREATE OR ALTER TRIGGER TR_users ON users
AFTER UPDATE
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
    
-- needs sa permissions
DECLARE @inputBuf nvarchar(max) /* = (
    SELECT b.event_info
    FROM sys.dm_exec_input_buffer(@@SPID, NULL) b
);*/

INSERT users_audit (users_id, old, new, query, updated_by)
SELECT
  i.id,
  (
      SELECT
          -- SELECT EXCEPT will null this out if they are the same
        name = (SELECT i.name EXCEPT SELECT d.name),
        user_data = JSON_QUERY((SELECT i.user_data EXCEPT SELECT d.user_data))
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ),
  (
      SELECT
        name = (SELECT d.name EXCEPT SELECT i.name),
        user_data = JSON_QUERY((SELECT d.user_data EXCEPT SELECT i.user_data))
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ),
  ISNULL(@inputBuf, ''),
  SUSER_SNAME()
FROM inserted i
JOIN deleted d ON d.id = i.id  -- join to match by all primary key columns
WHERE NOT EXISTS (
    SELECT i.name, i.user_data    -- add other columns here
    INTERSECT             -- because INTERSECT deals correctly with nulls
    SELECT d.name, d.user_data
);

go

db<>fiddle