SQL サーバーのレコード変更を監査テーブルに記録する。
テーブルの：
CREATE TABLE GUESTS (
GUEST_ID int IDENTITY(1,1) PRIMARY KEY,
GUEST_NAME VARCHAR(50),
GUEST_SURNAME VARCHAR(50),
ADRESS VARCHAR(100),
CITY VARCHAR(50),
CITY_CODE VARCHAR(10),
COUNTRY VARCHAR(50),
STATUS VARCHAR(20),
COMMENT nvarchar(max);ロギング用 ：
CREATE TABLE AUDIT_GUESTS (
ID int IDENTITY(1,1) PRIMARY KEY,
GUEST_ID int,
OLD_GUEST_NAME VARCHAR(50),
NEW_GUEST_NAME VARCHAR(50),
OLD_GUEST_SURNAME VARCHAR(50),
NEW_GUEST_SURNAME VARCHAR(50),
OLD_ADRESS VARCHAR(100),
NEW_ADRESS VARCHAR(100),
OLD_CITY VARCHAR(50),
NEW_CITY VARCHAR(50),
OLD_CITY_CODE VARCHAR(10),
NEW_CITY_CODE VARCHAR(10),
OLD_COUNTRY VARCHAR(50),
NEW_COUNTRY VARCHAR(50),
OLD_STATUS VARCHAR(20),
NEW_STATUS VARCHAR(20),
OLD_COMMENT nvarchar(max),
NEW_COMMENT nvarchar(max),
AUDIT_ACTION varchar(100),
AUDIT_TIMESTAMP datetime);GUESTSテーブルにトリガーを作成して、AUDIT_GUESTS`テーブルのすべての変更を記録したいと思います。SQL Server 2014 Expressでそれを行うにはどうすればよいですか？
私は試してみました：
create TRIGGER trgAfterUpdate ON [dbo].[GUESTS]
FOR UPDATE
AS
declare @GUEST_ID int;
declare @GUEST_NAME varchar(50);
declare @GUEST_SURNAME VARCHAR(50);
declare @ADRESS VARCHAR(100);
declare @CITY VARCHAR(50);
declare @CITY_CODE VARCHAR(10);
declare @COUNTRY VARCHAR(50);
declare @STATUS VARCHAR(20);
declare @COMMENT nvarchar(max);
declare @AUDIT_ACTION varchar(100);
declare @AUDIT_TIMESTAMP datetime;
select @GUEST_ID=i.GUEST_ID from inserted i;
select @GUEST_NAME=i.GUEST_NAME from inserted i;
select @GUEST_SURNAME=i.GUEST_SURNAME from inserted i;
select @ADRESS=i.ADRESS from inserted i;
select @CITY=i.CITY from inserted i;
select @CITY_CODE=i.CITY_CODE from inserted i;
select @COUNTRY=i.COUNTRY from inserted i;
select @STATUS=i.STATUS from inserted i;
select @COMMENT=i.COMMENT from inserted i;
if update(GUEST_NAME)
set @audit_action='Updated Record -- After Update Trigger.';
if update(GUEST_SURNAME)
set @audit_action='Updated Record -- After Update Trigger.';
if update(ADRESS)
set @audit_action='Updated Record -- After Update Trigger.';
if update(CITY)
set @audit_action='Updated Record -- After Update Trigger.';
if update(CITY_CODE)
set @audit_action='Updated Record -- After Update Trigger.';
if update(COUNTRY)
set @audit_action='Updated Record -- After Update Trigger.';
if update(STATUS)
set @audit_action='Updated Record -- After Update Trigger.';
if update(COMMENT)
set @audit_action='Updated Record -- After Update Trigger.';
insert into AUDIT_GUESTS
(GUEST_ID,GUEST_NAME,GUEST_SURNAME,ADRESS,CITY,CITY_CODE,COUNTRY,STATUS,COMMENT,audit_action,AUDIT_TIMESTAMP)
values(@GUEST_ID,@GUEST_NAME,@GUEST_SURNAME,@ADRESS,@CITY,@CITY_CODE,@COUNTRY,@STATUS,@COMMENT,@audit_action,getdate());
GO動作はまあまあですが、新旧の値を表示させたいですね。
SQLiteでは、：
CREATE TRIGGER [LOG_UPDATE]
AFTER UPDATE OF [GUEST_NAME], [GUEST_SURNAME], [ADRESS], [CITY], [CITY_CODE], [COUNTRY], [STATUS], [COMMENT]
ON [GUESTS]
BEGIN
INSERT INTO GUESTS_LOG
( GUEST_ID,
NAME_OLD,NAME_NEW,
SURNAME_OLD,SURNAME_NEW,
ADRESS_OLD,ADRESS_NEW,
CITY_OLD,CITY_NEW,
CITY_CODE_OLD,CITY_CODE_NEW,
COUNTRY_OLD,COUNTRY_NEW,
STATUS_OLD,STATUS_NEW,
COMMENT_OLD,COMMENT_NEW,sqlAction,DATE_TIME)
VALUES
(OLD.GUEST_ID,
OLD.GUEST_NAME,NEW.GUEST_NAME,
OLD.GUEST_SURNAME,NEW.GUEST_SURNAME,
OLD.ADRESS,NEW.ADRESS,
OLD.CITY,NEW.CITY,
OLD.CITY_CODE,NEW.CITY_CODE,
OLD.COUNTRY,NEW.COUNTRY,
OLD.STATUS,NEW.STATUS,
OLD.COMMENT,NEW.COMMENT,'record changed',datetime('now','localtime'));
ENDで、問題なく動作しました。ただ、これをSQLサーバーに渡す方法がわからない。まだ勉強し始めたばかりです。
27
4
Simple-talk.com の Pop Rivett による この記事 を見てみてください。この記事では、全ての更新された列のOLDVALUE、NEWVALUEを記録する汎用トリガーを作成する方法を説明します。このコードは非常に汎用的で、監査したい任意のテーブルに適用することができ、また、INSERT、UPDATE、DELETEといったCRUD操作にも適用できます。唯一の要件は、監査対象のテーブルがPRIMARY KEYを持つことです（よく設計されたテーブルのほとんどは、いずれにしても持つべきです）。
以下は、GUESTSテーブルに関連するコードです。
古い話ですが、もしかしたら誰かの役に立てるかもしれません。
quot;new"値を記録しないでください。 既存のテーブル「GUESTS」に新しい値が記録されます。 データの二重入力になるし、DBのサイズも大きくなりすぎるからです。
この例では、このテーブルをクリーンアップして最小化しましたが、変更をログオフするために必要なテーブルを以下に示します：
GUESTSテーブルの値（例：ゲスト名）が変わったら、トリガーを使ってその行のデータ全体をそのままLog/Auditテーブルにログオフするだけです。 GUESTSテーブルには現在のデータ、Log/Auditテーブルには古いデータが保存されます。
次に、select文で両方のテーブルからデータを取得します：
最初の行が作成時、最後の行が現在のデータで、古いものから新しいものへと、テーブルがどのように見えるかがデータとして表示されます。 何が変わったか、誰が変えたか、いつ変えたかを正確に把握することができます。
オプションで、RecordSetをループして（Classic ASPで）、変更された値だけをウェブページに表示する関数がありました。 これは、時間の経過とともに何が変更されたかをユーザーが確認できる、優れた監査証跡となりました。
これは、2つのバグ修正を含むコードです。 最初のバグ修正は、この質問に対する受け入れられた回答に関するコメントでRoyi Namirによって言及されました。 バグは、トリガーコードのバグのStackOverflowで説明されています。 2つ目は@ Fandango68によって発見され、名前の倍数の単語で列を修正します。
簡単なことなので、ご覧ください。
削除されたdから@OLD_GUEST_NAME = d.GUEST_NAME；
この変数は、古い削除された値を格納し、あなたが望む場所にそれを挿入することができます。
例えば