Home > Software engineering >  Trigger for capturing changed columns in audit table
Trigger for capturing changed columns in audit table

Time:05-09

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":"[email protected]"} 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":"[email protected]","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":"[email protected]"}} {"name":"John Doe","user_data":{"email":"[email protected]","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":"[email protected]","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":"[email protected]"}} {"name":"John Doe","user_data":{"email":"[email protected]","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

CodePudding user response:

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

  • Related