时间戳

首页 > 解决方案 > SQL Server：JSON 审计

sql - SQL Server：JSON 审计

问题描述

我正在尝试使用触发器构建审计策略。我最初尝试过这个：

基于触发器的变更数据捕获

它工作得很好：它产生了一个格式很好的 JSON，看起来像这样：

{
    "columns" :[
     { 
        "name": "ID",
        "value:" : {
          "old": "1",
          "new": "2"
        }
     },
     {
        "name": "ModifiedOn",
        "value:" : {
          "old": "2020-02-14 16:00:00",
          "new": "2020-02-15 13:00:00"
        }
     } 
    ]
}

它甚至列出了更新触发器中更新了哪些列。它运行良好 - 直到系统达到负载。因为触发器从系统表中选择信息，所以它几乎因为死锁而扼杀了性能。

我修改了脚本以包含 NOLOCK 提示，但我也在为 EF Core 使用批量上传库；它使用临时表，并在技术上“修改”模式 - 所以我仍然遇到 sch-s 和 sch-m 锁的死锁。

我修改了脚本以简单地选择插入/删除到审计表中的值，但它更难阅读。

以下（也在SO上找到）让我很接近：

SELECT [name] = b.[key],
VALUE = CAST(b.[value] as NVARCHAR(MAX))
FROM inserted i,
cross apply
(SELECT * FROM OPENJSON((SELECT * FROM INSERTED FOR JSON PATH,
     WITHOUT_ARRAY_WRAPPER))) B
FOR JSON PATH

但它只获取第一行的值。

有没有人做过类似的事情？
如果我必须对列进行“硬编码”（如有必要），我可以让脚本生成语句。

谢谢。

标签： sqlsql-server

解决方案

declare @inserted table
(
id int primary key clustered,
colA int,
colB varchar(10),
colC uniqueidentifier,
colD date
)

insert into @inserted(id, ColA, colB, colC, colD)
values
(1, 100, 'ABC', newid(), getdate()),
(2, 200, 'DEF', newid(), getdate()),
(3, 300, 'KLM', newid(), getdate()),
(4, 400, 'XYZ', newid(), getdate());


select id, (select i.* for json path) as thejson
from @inserted as i;



declare @deleted table
(
id int primary key clustered,
colA int,
colB varchar(10),
colC uniqueidentifier,
colD date
)

insert into @deleted(id, ColA, colB, colC, colD)
values
(1, 100, 'ABC', newid(), getdate()),
(2, 200, 'DEF', newid(), getdate()),
(3, 300, 'KLM', newid(), getdate()),
(4, 400, 'XYZ', newid(), getdate());


select 
    [name] = o.[key],
    VALUE = CAST(o.[value] as NVARCHAR(MAX))
from
(
select (select d.* for json path, WITHOUT_ARRAY_WRAPPER) as thejson
from @deleted as d
) as src
cross apply openjson(src.thejson) as o;

推荐阅读