SQL Sqrver 2005でトリガーを利用してデータベースの変更を記録する

SQL Serverであるテーブルの操作のログを取りたいと考えています。

単に「更新されました」とか「削除されました」というログではなく、もともとAというデータがBに変更されましたという具合にデータも含めたログを取ります。

アプリケーション側で実装することもできますが、メンテナンス性が悪いのでSQLのトリガーを使った方法を解説します。

 

データベースの準備

SQL Serverにデータベースを作成します。

テーブルの準備

2つのテーブルを準備します。1つはｔｂｌ社員、もう1つはtbl社員ログとします。

スキーマは簡単に次のようにします。

ｔｂｌ社員	tbl社員ログ
ID （PK、int）	ID （PK、int）
氏名（nvarchar(50))	氏名（nvarchar(50))

トリガーの作成

テーブルtbl社員に行の追加、更新があればｔｂｌ社員ログに更新前のデータと更新後のデータをコピーするようにします。

SQL Server 2005 Management Studioを開いてｔｂｌ社員の+をクリックして展開します。トリガという項目がありますので、右クリックして「新しいトリガ」を選択します。

（ほかにプログラムという項目にデータベーストリガというのがありますが、こちらと間違えないようにします。）

右側ペインにトリガーのテンプレートが表示されますので、すべて削除します。

次のようにトリガーを入力します。

CREATE TRIGGER トリガーサンプル ON tbl社員 AFTER INSERT， UPDATE AS BEGIN INSERT INTO tbl社員ログ（氏名） SELECT 氏名 FROM deleted INSERT INTO tbl社員ログ（氏名） SELECT 氏名 FROM inserted END

入力が完了したら「実行」をクリックします。

「コマンドが正常に終了しました。」となれば完了です。

トリガーの解説

1行目の

「CREATE TRIGGER トリガーサンプル ON tbl社員」はトリガーの作成を指示していて、トリガーの名前とどのテーブルでトリガーを使うかを示しています。

2行目の

「AFTER INSERT， UPDATE」はトリガーの実行されるタイミングを指示しています。（INSERT、UPDATE、DELETEが指定できます。SELECTは指定できません。）

3行目のAS句はお約束で付けます。AS句以降がトリガーの処理になります。

4行目のBEGINはENDとペアになっていて処理をグループ化します。INSERT文が2行ありますが、BEGIN~ENDを使わないと初めのINSERT文しか処理されません。

5行目の

「INSERT INTO tbl社員ログ（氏名） SELECT 氏名 FROM deleted」はSQL文です。SELECT文で射影された値をINSERT文に渡して行の追加を実行しています。

deletedというテーブルは削除される値、更新で上書きされる値が格納されている擬似的なテーブルです。

6行目も5行目と同様です。

insertedというテーブルがありますが、これは追加される値、あるいは更新で上書きする値が格納される擬似的なテーブルです。

deletedとinsertedを利用すると変更前、変更後の値にアクセスができるということです。（deletedがあるおかげでBEGIN UPDATEで事前に別のトリガーを呼び出して変更前のデータを取得しなくてよいので気が利いています。）

その他

トリガーは再起動などしてもいつでも動くようになっています。

INSERT文に項目を追加すれば「追加されました」などのコメントも出力できます。

もっと複雑な処理をするためにAS句以降にストアドプロシージャを実行することもできます。が、あまり複雑なことをするのはメンテナンス性を損なうのでほどほどにというのが良い設計のようです。