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 aNULL
value, so we can useSELECT...EXCEPT
to remove values which are the same betweeninserted
anddeleted
.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