SQL Server データ監査のコンテキスト トリガーの使用
SQL Server 2008、簡単なデータ監査 - トリガーとコンテキストを使用して SQL テーブルへの更新をトレースする
標的
データの監査は、時には悪夢のようなものになることがあります。最近、自動ダイヤラー用のデータベースをセットアップしました。複数のソースから更新する必要がありました。つまり、エラーが発生した場合、どのプロセスが変更を行ったかを確認するのが非常に困難でした。
私は最近、SQL のプロセス間でデータを渡す方法として Context を使用することについて読みましたが、運が良ければ、これが必要であり、トリガーも必要でした。
最初に 2 つのテーブルを作成します。1 つは必要なデータを含み、もう 1 つは同じ列と ID 列、日時、プロセス名を持ち、すべての変更を保存します。
SQL
CREATE TABLE Audit(AuditID INT IDENTITY(1,1) CONSTRAINT PK_AuditID PRIMARY KEY,AuditData NVARCHAR(100))CREATE TABLE AuditAudit(AuditAuditID INT IDENTITY(1,1) CONSTRAINT PK_AuditAuditID PRIMARY KEY,AuditAuditDateTime DATETIME DEFAULT GETDATE(),AuditAuditProcess NVARCHAR(128),AuditID INT,AuditData NVARCHAR(100))標的
次に、テーブルにトリガーを作成します。挿入または更新の後に実行したいと考えています。このトリガーは、AuditAudit テーブルに行を追加し、新しいデータの内容、変更の日時、およびプロセス (入力した場合) または接続 ID を記録します。トリガーの詳細については、 MSDNを参照してください。
SQL
CREATE TRIGGER AuditUpdated ON Audit AFTER INSERT, UPDATE AS BEGINDECLARE @Cont VARCHAR(128) =(SELECT CAST(CONTEXT_INFO() as varchar(128)))--Retrieve Context InfoINSERT INTO AuditAudit(AuditAuditProcess,AuditID,AuditData)SELECT ISNULL(@Cont,CAST(@@Spid AS VARCHAR(20))),AuditID,AuditData FROM Inserted--Insert process id if there is not context addedENDGO標的
これで、いくつかのステートメントを使用してテーブルを更新できます。最初に通常の挿入を実行し、次にコンテキストが入力された状態で挿入と更新を実行します。
SQL
--insert with no contextINSERT INTO Audit(AuditData)SELECT '1'--insert with contextGODECLARE @Context_Info varbinary(128)SELECT @Context_Info = CAST('Insert'+SPACE(128) AS VARBINARY(128))--Declare the name for the contextSET CONTEXT_INFO @Context_Info--Update Context info to aboveINSERT INTO Audit(AuditData)--Run your codeSELECT '2'--update with contextGODECLARE @Context_Info varbinary(128)SELECT @Context_Info = CAST('Update'+SPACE(128) AS VARBINARY(128))--Declare the name for the contextSET CONTEXT_INFO @Context_Info--Update Context info to aboveUPDATE Audit SET AuditData='3' WHERE AuditID=1--Run your code--Select Records from the audit tableGOSELECT * FROM AuditAuditResults
Your select statement should have three rows as per below.
| AuditID | AuditDateTime | AuditProcess | AuditId | AuditData |
| 1 | 2013-01-29 18:21:23.097 | 51 | 1 | 1 |
| 2 | 2013-01-29 18:21:27.433 | Insert | 2 | 2 |
| 3 | 2013-01-29 18:21:30.710 | Update | 1 | 3 |
I found the easiest way to use this was to attach unique process names to stored procedures that update the tables, that way you could track where the data changes came from.
