11.5.2 トリガの定義
- 〈この項の構成〉
(1) 定義の準備
トリガを定義すると,指定したトリガSQL文を基にトリガ動作手続きのSQLオブジェクトが自動的に作成され,データディクショナリLOB用RDエリアに格納されます。そのため,トリガを定義する場合,データディクショナリLOB用RDエリアに十分な容量を確保しておく必要があります。データディクショナリLOB用RDエリアの容量の見積もりについては,「データディクショナリLOB用RDエリアの容量の見積もり」を参照してください。
また,トリガ契機となるSQLを実行するためには,SQLオブジェクト用バッファ長を指定するときにトリガのSQLオブジェクトについても考慮しておく必要があります。SQLオブジェクト用バッファ長の見積もりについては,マニュアル「HiRDB システム定義」を参照してください。
(2) 定義方法
トリガの定義,SQLオブジェクトの再作成,及び削除には次の定義系SQLを使用します。
-
トリガを定義します。トリガは所有する表にだけ定義でき,他ユーザが所有する表には定義できません。次の項目を指定します。
-
トリガ動作の実行時期
トリガ動作は表への操作の前(BEFORE),又は後(AFTER)に実行できます。なお,トリガ動作時期にBEFOREを指定したトリガをBEFOREトリガ,AFTERを指定したトリガをAFTERトリガといいます。
-
トリガ動作の実行契機
トリガ動作を実行する契機には,INSERT文,DELETE文,又はUPDATE文の実行があります。
-
トリガを定義する表
トリガは実表にだけ定義できます。
-
トリガ契機となるSQLで更新する行に,SQL実行前の名称(旧値相関名),又は実行後の名称(新値相関名)を指定します。ここで指定した名称を使ってトリガの動作内容を指定できます。
-
トリガ動作
トリガ動作には次の三つの要素があります。
・トリガSQL文(自動的に実行させるSQL文)
・トリガ動作の探索条件(トリガSQL文が実行される条件)
・動作が実行されるのが行単位か文単位かの種別
トリガSQL文はトリガ動作の探索条件を満たす場合にだけ実行され,条件を省略した場合はトリガ契機となるSQLが実行されると常にトリガSQL文が実行されます。
-
-
既に定義されているトリガのSQLオブジェクトを再作成します。定義系SQLのALTER ROUTINEでも再作成できます。
-
トリガを削除します。
(3) トリガの定義例
(a) トリガを使用した例
商品管理表の価格が更新された場合,変更前に比べ変更後の価格上昇が10,000円を超えるとき,商品管理履歴表に更新前後の価格を挿入するトリガの定義例とトリガ動作を次に示します。
CREATE TRIGGER TR1 …トリガ名 AFTER …トリガ動作の実行時期 UPDATE OF 価格 …トリガ動作の実行契機 ON 商品管理表 …トリガを定義する表 REFERENCING OLD ROW AS X1 …更新前の行に指定する名称 NEW ROW AS Y1 …更新後の行に指定する名称 FOR EACH ROW …行単位か文単位かの種別 WHEN(Y1.価格 - X1.価格 > 10000) …トリガ動作の探索条件 INSERT INTO 商品管理履歴表 VALUES …トリガSQL文 (X1.品番, X1.価格, Y1.価格)
(b) トリガ動作にSQL制御文(代入文)を使用した例
代入文は,指定した値を指定した列に代入するSQLです。トリガでは表に対する操作の前に動作するトリガ動作で代入文を使用できます。トリガ動作に代入文を使用すると列と列の関係付けができます。
社員表の職種列が更新されると,更新された内容によって手当て列の値を変更するトリガの定義例とトリガ動作を次に示します。
-
社員表に挿入される行に対して,職種がAの場合は給料の8%,Bの場合は10%,AとB以外の場合は0%を手当ての列に設定するトリガ
CREATE TRIGGER 手当て設定トリガ1 BEFORE INSERT ON 社員表 REFERENCING NEW ROW AS X1 FOR EACH ROW SET X1.手当て=CASE X1.職種 WHEN 'A' THEN X1.給料*0.08 WHEN 'B' THEN X1.給料*0.1 ELSE 0 END
-
職種と給料が更新された行に対して,職種がAの場合は給料の8%,Bの場合は10%,AとB以外の場合は0%を手当ての列に設定するトリガ
CREATE TRIGGER 手当て設定トリガ2 BEFORE UPDATE OF 職種,給料 ON 社員表 REFERENCING NEW ROW AS X1 FOR EACH ROW SET X1.手当て=CASE X1.職種 WHEN 'A' THEN X1.給料*0.08 WHEN 'B' THEN X1.給料*0.1 ELSE 0 END
- 〔説明〕
-
INSERT文が契機となり,手当て設定トリガ1が実行され,行が追加されました。INSERT文では手当てに0を設定していますが,代入文の結果が格納されます。
次に,UPDATE文が契機となり,手当て設定トリガ2が実行され,手当てが0に変更されました。
(c) トリガ動作にSQL制御文(複合文)を使用した例
複合文は,複数のSQLをまとめて一つのSQLとして実行するSQLです。ある表に対する更新を契機に複数の表を更新する場合,トリガ動作に複合文を使用すると一つのトリガを定義するだけで複数の表を更新できます。
在庫マスタ表が更新された後,その更新内容を広島在庫及び仙台在庫に反映するトリガの定義例を次に示します。複合文を使用しない場合,トリガを二つ定義しなければなりません。
CREATE TRIGGER 地方在庫表更新トリガ AFTER UPDATE OF 在庫数 ON 在庫マスタ REFERENCING NEW ROW 更新後 OLD ROW 更新前 BEGIN UPDATE 広島在庫 SET 在庫数=更新後.在庫数 WHERE 商品コード=更新前.商品コード; UPDATE 仙台在庫 SET 在庫数=更新後.在庫数 WHERE 商品コード=更新前.商品コード; END
(d) トリガ動作にSQL診断文(SIGNAL文)を使用した例
SIGNAL文はエラーを発生させるSQLです。表への操作の前にSIGNAL文を指定したトリガ動作を実行させれば,操作が不正な場合にSIGNAL文を実行してその操作を抑止できます。
社員情報表を更新する前に,自分の情報以外を更新しようとした場合にSIGNAL文でエラーを発生させ,更新を抑止するトリガの定義例を次に示します。
CREATE TRIGGER 更新抑止トリガ BEFORE UPDATE ON 社員情報 REFERENCING OLD ROW AS X1 WHEN(X1.社員名<>USER) SIGNAL SQLSTATE '99001'