SQL Serverでテーブルの監査ログを記録する方法

この記事は公開から3年以上経過しています。

SQL Serverでテーブル操作履歴(監査ログ)を記録したいとき、CHANGE_TRACKINGではなくトリガーを使って実現する方法を紹介します。

ここで紹介する方法は全レコードの変更内容をJSON文字列に変換して記録を行うため、同等の方法を業務で利用する場合は非機能要件を満たせるかなども含めて充分な検証を行ってください(At your own risk)。

DBスキーマ/トリガー定義

監査対象となるテーブル

CREATE TABLE TRIGGERED_TBL(ID INT PRIMARY KEY, NAME NVARCHAR(50), AGE TINYINT);

監査ログ記録テーブル

CREATE TABLE AUDIT_TBL(
  ID INT IDENTITY(1, 1) PRIMARY KEY,
  DATETIME DATETIME2,
  TYPE TINYINT,
  DATA NVARCHAR(MAX)
);

監査ログ取得用トリガー

CREATE OR ALTER TRIGGER AUDIT_TRIGGER 
    ON TRIGGERED_TBL AFTER 
INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON; 

DECLARE @EVENT_TYPE INT = 0
IF EXISTS (SELECT * FROM INSERTED)
    SET @EVENT_TYPE = @EVENT_TYPE + 1
IF EXISTS (SELECT * FROM DELETED)
    SET @EVENT_TYPE = @EVENT_TYPE + 2

DECLARE @RECORD NVARCHAR(MAX) 
IF @EVENT_TYPE = 2
    SELECT @RECORD = (SELECT * FROM DELETED FOR JSON AUTO, INCLUDE_NULL_VALUES, ROOT('changes')); 
ELSE
    SELECT @RECORD = (SELECT * FROM INSERTED FOR JSON AUTO, INCLUDE_NULL_VALUES, ROOT('changes')); 

INSERT AUDIT_TBL VALUES (CURRENT_TIMESTAMP, @EVENT_TYPE, JSON_MODIFY( @RECORD, '$.table','TRIGGERED_TBL')); 

END

トリガー内で’SET NOCOUNT ON’を行わないとトリガー内で処理を行った行数が影響を受けた行数(例えば.NETのExecuteNonQueryメソッドの戻り値)に加算されます。

トリガー対象テーブルでロールバックが行われた場合には監査ログへの記録もロールバックされます。また、サンプルはトリガーでエラートラップを行っていないため、トリガーでエラーが発生した場合はトランザクションの状態によってはメインテーブルのロールバックが発生することに注意が必要です。

動作検証

以下のSQLを実行して監査ログ対象テーブルに対する操作を行います。
(Insert/Update/Delete/複数行Insert/複数行Delete)

-- 単一行挿入
INSERT INTO TRIGGERED_TBL VALUES (1, 'Foo', 10);
INSERT INTO TRIGGERED_TBL VALUES (2, 'Bar', 20);
INSERT INTO TRIGGERED_TBL VALUES (3, 'Baz', null);

-- 単一行更新
UPDATE TRIGGERED_TBL SET NAME = 'FOO' WHERE ID = 1;
UPDATE TRIGGERED_TBL SET AGE = 21 WHERE ID = 2;

-- 単一行削除
DELETE TRIGGERED_TBL WHERE ID = 3;

-- 複数行挿入
INSERT INTO TRIGGERED_TBL VALUES(3, 'Qux', 40),(4, 'Quxx', 50);

-- 複数行削除
DELETE TRIGGERED_TBL WHERE ID IN (1, 3);

-- 複数行更新
UPDATE TRIGGERED_TBL SET AGE='99' WHERE ID IN (2, 4);

実行結果

実行後の最終的な監査対象テーブル内容は以下のとおりです。

ID	NAME	AGE
2	Bar	99
4	Quux	99

監査対象テーブル操作後に記録される監査ログ内容は以下のとおりです。
(TYPE列=1:Insert/2:Delete/3:Update)

ID	DATETIME	TYPE	DATA
1	2021/08/26 18:20:06.043	1	{"changes":[{"ID":1,"NAME":"Foo","AGE":10}],"table":"TRIGGERED_TBL"}
2	2021/08/26 18:20:06.910	1	{"changes":[{"ID":2,"NAME":"Bar","AGE":20}],"table":"TRIGGERED_TBL"}
3	2021/08/26 18:20:07.733	1	{"changes":[{"ID":3,"NAME":"Baz","AGE":null}],"table":"TRIGGERED_TBL"}
4	2021/08/26 18:20:08.710	3	{"changes":[{"ID":1,"NAME":"FOO","AGE":10}],"table":"TRIGGERED_TBL"}
5	2021/08/26 18:20:09.540	3	{"changes":[{"ID":2,"NAME":"Bar","AGE":21}],"table":"TRIGGERED_TBL"}
6	2021/08/26 18:20:10.463	2	{"changes":[{"ID":3,"NAME":"Baz","AGE":null}],"table":"TRIGGERED_TBL"}
7	2021/08/26 18:20:11.420	1	{"changes":[{"ID":4,"NAME":"Quux","AGE":50},{"ID":3,"NAME":"Qux","AGE":40}],"table":"TRIGGERED_TBL"}
8	2021/08/26 18:20:12.970	2	{"changes":[{"ID":3,"NAME":"Qux","AGE":40},{"ID":1,"NAME":"FOO","AGE":10}],"table":"TRIGGERED_TBL"}
9	2021/08/26 18:20:15.396	3	{"changes":[{"ID":4,"NAME":"Quux","AGE":99},{"ID":2,"NAME":"Bar","AGE":99}],"table":"TRIGGERED_TBL"}

ちなみにSQL Serverの場合マジックテーブル(Inserted/Updated)をストアド側へ引き渡す方法やトリガーの情報を取得する特殊変数がないため、PostgreSQLのようにログ記録処理だけをストアド化して複数のトリガーから呼び出すといったスマートで効率の良い対応ができないようです(今後良い案が思いついたら修正するかもしれません)。

以上です。