トリガーについて

1 トリガーについて

INSERT、UPDATE、DELETEの３つのDML文が表に対して実行されたとき、その変更処理をきっかけに自動的に実行される特殊なストアドプロシージャのことをトリガーといいます。

1.トリガーとは何か？

INSERT、UPDATE、DELETEの３つの SQL 文は表のデータをそれぞれ追加更新削除することによって表の内容を変更するデータ操作文 DMLです。これらの DML文が実行され表に対して何らかの変更が加えられたときその変更処理をきっかけとして自動的に実行される特殊なストアドプロシージャのことをトリガーと呼びます。
トリガーとはもともと剣銃の引き金という意味です。この場合には表に対する変更処理が引き金となってトリガーというオブジェクトが作動することになります。トリガーを作成する場合には通常変更処理の対象となる表の変更処理の内容、変更処理をきっかけとして自動的に実行する処理実行のタイミングなどを指定します。トリガーは指定された表を見張り続け指定された内容の変更処理が行われると、指定されたタイミングで指定された処理を自動的に実行します

2.トリガーの用途と機能

①.複雑な妥当性チェック

通常列の値の妥当性をチェックするには、チェック制約を使います。しかし、チェック制約で対応できないような複雑な妥当性チェックを行う場合には、トリガーを利用します。特に他の行、あるいは他の表に含まれる行の値と比較しながらその関連性の妥当性を検討するような場合、チェック制約では対応できないことがあります。例えば複数のテーブルをまたいだチェックなどは check 制約では対応できないのでこのような場合にトリガーを使用します。

②.複雑な初期値設定

通常、表に行を追加したときの列の初期値を設定するには、 DEFAULT制約を使います。しかし DEFAULT制約では対応できないような複雑な初期値を設定する場合には、トリガーを利用します。特に他の行、あるいは他の表に含まれる列の値を使って演算し、その結果から初期値を求めるような場合、デフォルト制約では対応できないことがあります。

③.複雑な参照整合性チェック

通常、他の表に含まれる値との整合性をチェックするには、参照整合性制約を使います。参照整合性制約は外部キーと主キーの間のデータの整合性をチェックすることによって関連する表のリレーションを維持します。しかし参照整合性制約では対応できないような標準機能以外の整合性チェックを行う場合にはトリガーを使用します。

3.トリガーの作成の構文

ここでは、SQL Serverでのトリガーの作成を説明します。

①.トリガーの作成

構文は以下のとおりとなります。

CREATE TRIGGER <トリガー名>
ON <表名>
FOR {INSERT | UPDATE | DELETE} [,INSERT | UPDATE | DELETE] AS
<処理内容>
RETURN

4.トリガーのプログラミング

今回は、UPDATE文で起動するトリガーを作成してみます。まず次のようなテーブルを用意します。

テスト用テーブル
Table1
CREATE TestDB.TABLE TABLE2
(
ID INT,DATA_KBN INT,VAL INT,RND INT
)

データ
INSERT INTO TABLE1 VALUES(1,1,100,0);
INSERT INTO TABLE1 VALUES(2,2,90,0);
INSERT INTO TABLE1 VALUES(3,3,75,0);
INSERT INTO TABLE1 VALUES(4,1,28,0);
INSERT INTO TABLE1 VALUES(5,2,228,0);
INSERT INTO TABLE1 VALUES(6,3,1278,0);
INSERT INTO TABLE1 VALUES(7,1,4,0);
INSERT INTO TABLE1 VALUES(8,2,2800,0);
INSERT INTO TABLE1 VALUES(9,3,88,0);

実際のテーブルの中身
Table2

次にトリガーを作成します。コードは以下のとおりとなります。このトリガーは、Table2が更新されると、ID=１のレコードのRNDを999に更新するというシンプルなトリガーになります。

トリガー名：SP_SAMPLE2

実際に実行して動作を確認してみます。作成したら、SQLの実行画面でこのコードを実行し、トリガーを登録します。登録したらこのトリガーを実行してみます。このトリガーを実行すには、このテーブルに更新をかけると、それが引き金（トリガー）となり実行されますので、何かこのテーブルの別のレコードに更新をかけてみます。
今回は、ID=7のレコードに対して、VALの値を4,000に更新してみます。

UPDATE TABLE2 SET VAL = 4000 WHERE ID = 7

実行結果のメッセージは以下のとおり出力されました。おそらく、このUPDATE文をトリガーにして、上記のトリガー(SP_SAMPLE2)が実行されたと想定されます。

続いて、対象のテーブル(Table2)の中身を確認してみます。
Table2（UPDATE実行後）

トリガーで設定したとおり、ID=1のレコードのRNDが999に更新されています。ですのでトリガーは正常に起動し、ID=1のレコードが更新されました。

5.トリガーの有効化／無効化

今回、Table2に対してトリガーを設定しましたが、例えば、データを初期状態に戻すなどでUPDATE文を実行したとしたら問題が発生します。それは、UPDATE文の実行でトリガーが起動されてしまうので、常にID=1のレコードのRNDは999になってしまうことです。DELETEでこの行を削除し、再度INSERT文を発行し戻すこともできますが、このトリガーを一時的に無効化し起動しないように変えることができます。構文は以下のとおりとなります。(SQL Server版)

【トリガーの無効化】
ALTER TABLE TABLE2 DISABLE TRIGGER SP_SAMPLE2

【トリガーの有効化】
ALTER TABLE TABLE2 ENABLE TRIGGER SP_SAMPLE2

例えば、データをトリガー実行前に戻すには、以下のSQLを順に発行すれば元に戻すことができます。

①.トリガーの無効化
ALTER TABLE TABLE2 DISABLE TRIGGER SP_SAMPLE2

②.UPDATE文の実行
UPDATE TABLE2 SET VAL = 100,RND = 0 WHERE ID = 1;

③.トリガーの有効化
ALTER TABLE TABLE2 ENABLE TRIGGER SP_SAMPLE2

実際に実行した結果が以下のとおりとなります。

トリガー実行前の状態に戻りました。このようにトリガーの設定されているテーブルを更新する際は、一旦、トリガーの無効化をすることで更新処理を正しく実行することができるようになります。

トリガーについて