SQL Server 7.0 でトリガを作成する
MINI SERIES
はじめに
Microsoft SQL Server 7.0 の機能の一つであるトリガについてのプログラミングに関するお話を書いてみます。
グラフィカルな管理ツール "Enterprise Manager" にはなぜかトリガに関する操作を行う機能が欠けているようなので、プログラミング以外のことも軽く触れてみます。
トリガ
トリガとは、レコードを操作した際にあらかじめ定めておいたプログラム （Transact-SQL） を自動的に実行させるための仕組みです。これを用いることによって、データの矛盾、プログラムの負担などを抑制することができます。
トリガを用意できる操作は INSERT, UPDATE, DELETE クエリに対してで、これらのクエリがサーバに発行されると、サーバは関連付けられたトリガを自動的に実行してくれます。
使用するツール
Microsoft SQL Server 7.0 に付属している "Enterprise Manager" にはトリガを作成、変更する機能が備わっていないようなので、トリガを作成するには、これも Microsoft SQL Server 7.0 に付属している "クエリアナライザー" というツールを利用します。
"Enterprise Manager" から連動して起動することが可能で、その場合は 「ツール」 から 「SQL Server クエリアナライザ」 を選択します。
トリガの作成
CREATE TRIGGER 構文
トリガの作成には CREATE TRIGGER 構文を利用します。
構文の詳細および細かなオプションなどは Microsoft SQL Server 7.0 に付属している "Transact-SQL のヘルプ" を見るとわかるのですけど、大雑把に書くと次のような感じになります。
CREATE TRIGGER trigger-name
ON table-name
FOR INSERT, UPDATE
NOT FOR REPLICATION
AS
RETURN
trigger-name の部分には定義するトリガの名前を、table-name にはこのトリガを関連付けたいテーブル名を指定します。このとき、FOR で指定されているクエリ、ここでは INSERT と UPDATE が table-name に対して発行されると、このトリガが自動的に実行されます。
実際に実行されるプログラムは、上記では何も書いてませんが AS と RETURN ではさまれた部分です。ここに Transact-SQL にて実行したいプログラムを記述します。
トリガ内で利用できる特別なテーブルおよび関数
"inserted" と "deleted"
トリガの処理中では、"inserted" と "deleted" という２つの特別なテーブルを参照することができます。これらを利用することで、更新前および更新後のデータ状態を把握することが可能になります。
これらの意味は発行したクエリによって次のような感じになります。
| INSERT | UPDATE | DELETE | |
|---|---|---|---|
| inserted | 挿入された新しい行 | 更新された新しい行 | - |
| deleted | - | 更新前の古い行 | 削除された行 |
UPDATE() 関数
更新されたレコードとみなす条件を指定します。これを使用することによって、特定のカラム （複数可） が更新されたときにのみ処理を行う、と言うような指定が可能になります。
トリガの実行タイミング
Microsoft SQL Server 7.0 の場合、一度のクエリ発行に対して１度だけ実行されることになるそうです。（文レベルトリガ）
なので一度のクエリで複数行の変更が生じる可能性がある場合には、"inserted" や "deleted" テーブルに複数のレコードが存在するものとしてコーディングする必要があります。
簡単なトリガの例
役に立つたたないは別として、とりあえず思い当たる例を挙げてみようと思います。雰囲気だけとらえて、いろいろと応用してやってくださいませ。
ここでは次ぎのような２つのテーブルを用意したとして、これらに関連したトリガを用意してみる、と言う感じで話を進めます。
■ BBS_USER
| ユーザ ID | INT |
|---|---|
| ハンドル名 | NVARCHAR(50) |
| パスワード | CHAR(10) |
| 最終書き込み日 | DATETIME |
| 通算書き込み回数 | INT |
| BBS 利用権限 | BIT |
■ BBS_WRITE
| BBS_ID | INT |
|---|---|
| ユーザ ID | INT |
| 件名 | NVARCHAR(50) |
| 内容 | NVARCHAR(400) |
| 最終更新日 | DATETIME |
履歴を記録する
たとえば BBS を登録制で運用していたとして、書き込みとは別に、各ユーザの最終書き込み日時および通算書き込み回数を別テーブルに一括して管理するような場合を考えて見ます。
既に BBS_USER テーブルに登録してあるユーザのみが BBS へ書き込むことができるとして、そのときに BBS_WRITE へ書き込み内容を保存する他、BBS_USER の方の基本情報も更新するとします。
いろいろと手段はあると思いますけど、これをトリガでやってみると…。
まず引き金になるのが BBS_WRITE に対して書き込みを行ったとき。新たに書き込んだ場合 （INSERT） のみで処理を行うことにします。既存の書き込みに対する更新には反応しないものとします。
INSERT に関するトリガをはって、トリガ内の処理としては、[最終書き込み日] を現在の日付に設定し、[通算書き込み回数] の値を 1 増加させる、、、そんな感じで作ってみると、次のような感じのトリガになると思います。
CREATE TRIGGER [tr_BBS_WRITE]
ON [BBS_WRITE]
FOR INSERT
NOT FOR REPLICATION
AS
UPDATE [BBS_USER]
SET [最終書き込み日] = GETDATE(), [通算書き込み回数] = [通算書き込み回数] + 1
WHERE [ユーザ ID] IN (SELECT DISTINCT [ユーザ ID] FROM inserted)
RETURN
こうすることによって、単純に BBS_WRITE テーブルに対して INSERT クエリを発行するだけで、自動的に BBS_USER の内容も更新されるようになります。
書き込みが編集された際に更新日を修正する
いったん書き込んだ内容を変更した際に、自動的に [最終更新日] が新しい日付に置き換わるようなトリガを用意してみます。
この場合、引き金になるのは UPDATE クエリが処理される場合。そして [件名] と [内容] のどちらかが変更された場合を、編集されたとみなすならば、次のようなトリガになると思います。
CREATE TRIGGER [tr_BBS_UPDATE]
ON [BBS_WRITE]
FOR UPDATE
NOT FOR REPLICATION
AS
-- [内容] か [件名] のどちらか、または両方が変更されたかを調べる
IF UPDATE([内容]) OR UPDATE([件名])
BEGIN
-- 最終更新日を新しくする
UPDATE [BBS_WRITE]
SET [最終更新日] = GETDATE()
WHERE [BBS_ID] IN (SELECT [BBS_ID] FROM inserted)
END
RETURN
こうすることによって、単純に [内容] と [件名] に対する UPDATE クエリを発行するだけで、それが実行された日付が [最終更新日] に記録されることになります。
書き込みを制限する
たとえば何らかの理由で書き込みを禁止させたいとします。
これもトリガで制御してみようとすると、今回の場合は [BBS 利用権限] が与えられていないユーザからの書き込み （INSERT） があった場合には、その処理を無効にする、と言う方法で対処することが可能です。
CREATE TRIGGER [tr_BBS_DISABLE]
ON [BBS_WRITE]
FOR INSERT
NOT FOR REPLICATION
AS
-- [BBS 利用権限] が与えられていないかを調べる
IF (SELECT [BBS 利用権限] FROM [BBS_USER] WHERE [ユーザ ID] IN (SELECT [ユーザ ID] FROM inserted)) = 0
BEGIN
-- 権限がない旨のエラーを返し、処理の取り消し（ロールバック）を行う
RAISERROR ('権限の与えられていないユーザからの書き込みがありました', 15, 1)
ROLLBACK TRAN
END
RETURN
なお、RAISERROR 関数は、RAISERROR(エラーメッセージ, 重要度（0-18), 任意の整数値(既定 1)) と言う感じで利用するようです。３番目の引数の意味が良くわからないですが…。
またここの例では上のほうでも INSERT トリガをひとつ定義していますが、このようなトリガを両方利用するような場合は、分ける必要性がない限りはひとつのトリガに両方の処理を詰め込んでしまった方が安全そうな気がします。
トリガの管理
登録されているトリガ名を確認する
既に登録されているトリガ名を調べたい場合には、調べたいデータベースへ接続した上で "sysobjects" システムテーブルを参照します。
SELECT * FROM sysobjects WHERE xtype='TR'
この際、トリガには xtype="TR" が設定されているので、検索条件としてこれを指定します。 こうすることでトリガの一覧だけが取り出せます。
登録されているトリガのコードを確認する
トリガの設定 （ソースコード） を調べたい場合には、調べたいトリガが定義されているデータベースへ接続した上で "sp_helptext" ストアドプロシージャを呼び出します。
EXEC sp_helptext 'tr_TestTrigger1'
このようにすることで、トリガ名 tr_TestTrigger1 を宣言した際のソースコードを参照することができます。ただし暗号化されていない場合に限ります。
トリガを削除するには
既に登録してあるトリガを削除したい場合には、削除したいトリガが定義されているデータベースへ接続した状態で "DROP TRIGGER" 命令を実行します。
DROP TRIGGER tr_TestTrigger1
このようにすることで、トリガ tr_TestTrigger1 を削除することができます。