触发用于捕获审计表中更改的列的触发器

Question

我有2个桌子，一张主桌和一张审核表。

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()
);

我希望在用户主表上创建 触发器之后，可用于捕获 users_audit 表中的更改列（不包括时间戳）。 （下图）

我能够通过 json_modify（）和 openjson（@json ）手动执行此操作，但无法通过trigger

Initial insert:

id	name	user_data	timestamp	updated_timestamp
1	John	{"email":"[email protected ] “}	2021-05-08 18：10：10：02.0474381	2021-05-08 18：10：10：02.0474381

示例更新：

ID	名称	user_data	timestamp timestamp	Updated_timestamp updated_timestamp
1	john doe	{ CDN-CGI/L/电子邮件保护“ class =” __ cf_email__“ data-cfemail =” 503A343F3510313132337EE3337E333333F3D“> [emagy＆nbsp; protiveed]	：10：10：02.0474381	2021-05-08 18：12：12：06.0474381

审核表应像：

ID	USER_ID USER_ID	旧	之后的上述更新	Query	UPDATED_BY	TIMESTAMP
1	1	{“ NAME”：“：”：“ JOHN”，“ JOHN”，“ USER_DATA”，“ USER_DATA”： emagy＆nbsp; procected]	：“ [ doe”，“ user_data”：{“ email”：“ “，”，“地址”：“ 123 main st”}}}	更新查询	用户名	2021-05-08 18：12：06.0474381

示例更新2：

id	name	user_data	timestamp Updated_timestamp	Updated_timestamp Updated_timestamp
1	John	{“电子邮件”：“	2021-05-08 18：10：10：02.0474381	2021-05-08 18：14：16.0474381

之后，上述更新2审核表应该看起来像： 没有

	new	旧	（	）	和	更改
捕获 user_data		未 John”，“ user_data”：{“电子邮件”：“ >“}}}	{“ name”：“ John Doe”，“ user_data”：{“ email”：“ [email＆nbsp; protected	FB919F949EBB9A9998D5989496	“	] ：06.0474381
2	1	{“ name”：“ John Doe”}	{“ name”：“ John”}	update QUERY QUERY	用户名	2021-05-08 18：14：16.04744381

注意：时间表或 sql审核无法正常工作

Answer 1

这是一种方法。

原理与早期答案中提到的主要相同。主要区别是：

• 使用 dm_exec_input_buffer 获取起始批次。您需要服务器级的权限。
• for JSON 将不会显示具有 null 值的密钥，因此我们可以使用 select 以删除相同的值在插入和删除之间。
• 双重排列是必要的

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

JSON_QUERY 对于防止现有JSON对象进行 > db＆lt;＆gt; fiddle