我有 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()
);
我希望after update在主表上創建一個觸發器，該觸發器users可用于捕獲表中更改的列（不包括時間戳）users_audit。（以下示例）
我可以通過手動執行此操作json_modify()，OPENJSON(@json但無法通過觸發器使其自動作業
初始插入：
| ID | 姓名 | 用戶資料 | 時間戳 | 更新時間戳 |
|---|---|---|---|---|
| 1 | 約翰 | {"電子郵件":"[email protected]"} | 2021-05-08 18:10:02.0474381 | 2021-05-08 18:10:02.0474381 |
示例更新：
| ID | 姓名 | 用戶資料 | 時間戳 | 更新時間戳 |
|---|---|---|---|---|
| 1 | 約翰·多伊 | {"email":"[email protected]","address":"123 Main St"} | 2021-05-08 18:10:02.0474381 | 2021-05-08 18:12:06.0474381 |
上述更新后審計表應如下所示：
| ID | users_id | 老的 | 新的 | 詢問 | 更新人 | 時間戳 |
|---|---|---|---|---|---|---|
| 1 | 1 | {"name":"John","user_data":{"email":"[email protected]"}} | {"name":"John Doe","user_data":{"email":"[email protected]","address":"123 Main St"}} | 更新查詢 | 用戶名 | 2021-05-08 18:12:06.0474381 |
示例更新 2：
| ID | 姓名 | 用戶資料 | 時間戳 | 更新時間戳 |
|---|---|---|---|---|
| 1 | 約翰 | {"email":"[email protected]","address":"123 Main St"} | 2021-05-08 18:10:02.0474381 | 2021-05-08 18:14:16.0474381 |
在上面的 update2 審計表應該如下所示：（old并且new沒有捕獲user_data因為它沒有改變）
| ID | users_id | 老的 | 新的 | 詢問 | 更新人 | 時間戳 |
|---|---|---|---|---|---|---|
| 1 | 1 | {"name":"John","user_data":{"email":"[email protected]"}} | {"name":"John Doe","user_data":{"email":"[email protected]","address":"123 Main St"}} | 更新查詢 | 用戶名 | 2021-05-08 18:12:06.0474381 |
| 2 | 1 | {"name":"John Doe"} | {“名稱”：“約翰”} | 更新查詢 | 用戶名 | 2021-05-08 18:14:16.0474381 |
注意：臨時表或SQL 審計方法不起作用
uj5u.com熱心網友回復：
這是一種方法。
這些原則與之前的答案中提到的基本相同。主要區別在于：
- 使用
dm_exec_input_buffer. 為此，您需要服務器級別的權限。 FOR JSON不會顯示具有NULL值的鍵，因此我們可以使用它來洗掉和SELECT...EXCEPT之間相同的值。inserteddeletedJSON_QUERY有必要防止現有 JSON 物件的雙重轉義
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<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/471030.html