Symfoware(R) Server RDBユーザーズガイド データベース定義編 - FUJITSU -

2.6.6 トリガ定義

トリガ定義は、CREATE TRIGGER文で行います。トリガ定義では、トリガ事象と、起動する手続きを指定します。トリガ事象は、トリガを起動するきっかけとなる表に対するデータの更新方法を指定します。また、起動する手続きは、処理したい手続きを被トリガSQL文で指定します。

ここでは、トリガの利用目的によって、以下のように分類します。

• 単純なトリガ
• プロシジャルーチンを呼び出すトリガ

■単純なトリガ

表に対する更新を契機にして、別の表にデータを挿入するINSERT文を定義して自動的に実行することができます。

■プロシジャルーチンを呼び出すトリガ

トリガの被トリガSQL文にCALL文を指定して、プロシジャルーチンを呼び出すことができます。

プロシジャルーチンを使用することによって、複数のSQL文で構成する処理手続きを被トリガSQL文として定義することができます。また、業務要件に応じたデータの整合性や一貫性を自由にカストマイズすることができます。

例えば、データの整合性や一貫性を保証する手続きを定義したプロシジャルーチンをトリガにより呼び出し、表間のデータの整合性を保証したり、更新するデータの値をチェックしたりすることができます。

トリガを使用すると、これらの情報の設定をシステムで自動的に実行することができるため、応用プログラムを簡素化でき、信頼性の高いシステムを構築することができます。

以下に、トリガの定義例を示します。

例1 単純なトリガの場合

発注表に追加された行の発注価格が500万を超えている場合、トリガ“発注トリガ”により、その製品の取引先、仕入価格および発注数量を高額発注表に追加します。

トリガ定義例

CREATE TRIGGER　発注トリガ
　AFTER INSERT ON 発注表
　REFERENCING NEW AS NEWREC
　FOR EACH ROW
　　WHEN(NEWREC.仕入価格 * NEWREC.発注数量 > 5000000)
　　INSERT INTO 高額発注表
　　VALUES(NEWREC.取引先,NEWREC.仕入価格,NEWREC.発注数量)

 

例2 プロシジャルーチンを呼び出すトリガの場合

発注表に行を挿入すると、トリガ“発注トリガ２”により、プロシジャルーチン“発注ルーチン”を呼び出します。“発注ルーチン”は、挿入データに対して整合性チェックを行います。そして、発注した製品の在庫数量について在庫表を更新します。

プロシジャルーチン定義例

CREATE PROCEDURE STOCKS.発注ルーチン (
　　IN　ＮＥＷ取引製品　　SMALLINT,
　　IN　ＮＥＷ発注数量　　SMALLINT
)
BEGIN
　　-- SQL変数宣言
　　DECLARE SQLSTATE　　CHAR(5);
　　DECLARE SQLMSG　　　CHAR(256);
　　DECLARE POINT　　　 SMALLINT;
　　DECLARE Ｓ在庫数量　INTEGER;
　　-- 条件宣言
　　DECLARE 入力発注数量不当　　CONDITION FOR SQLSTATE'60001';
　　DECLARE 入力製品番号不当　　CONDITION FOR SQLSTATE'60002';
　　DECLARE 在庫不足　　　　　　CONDITION FOR SQLSTATE'60003';
　　DECLARE その他異常　　　　　CONDITION FOR SQLSTATE'60999';
　　-- ハンドラ宣言
　　DECLARE EXIT HANDLER FOR NOT FOUND
　　BEGIN
　　　　IF (POINT = 20 OR POINT = 30) THEN
　　　　　　RESIGNAL 入力製品番号不当
　　　　　　　　　　 '発注: 入力異常: 取引製品の値が不当';
　　　　ELSE
　　　　　　RESIGNAL その他異常 '発注: 異常: 予想外のデータなし発生';
　　　　END IF;
　　END;
　　DECLARE EXIT HANDLER FOR SQLEXCEPTION
　　BEGIN
　
　
　
　　　　RESIGNAL;　--発生したエラーをそのまま返却する
　　END;
　　-- 本処理
　　-- (1)入力チェック
　　SET POINT = 10;
　　IF (ＮＥＷ発注数量 < 1) THEN
　　　　SIGNAL 入力発注数量不当 '発注: 入力異常: 発注数量の値が不当';
　　END IF;
　　-- (2)在庫数量チェック
　　SET POINT = 20;
　　SELECT 在庫数量 INTO Ｓ在庫数量 FROM STOCKS.在庫表
　　　　WHERE 製品番号 = ＮＥＷ取引製品
　　　　WITH OPTION LOCK_MODE(EXCLUSIVE LOCK);
　　IF (ＮＥＷ発注数量 > Ｓ在庫数量) THEN
　　　　SIGNAL 在庫不足 '発注: 異常: 在庫不足です';
　　END IF;
　　-- (3)発注作業
　　SET POINT = 30;
　　UPDATE STOCKS.在庫表
　　　　SET 在庫数量 = 在庫数量 - ＮＥＷ発注数量
　　　　WHERE 製品番号 = ＮＥＷ取引製品;
END

 

トリガ定義例

CREATE TRIGGER STOCKS.発注トリガ２
　　AFTER INSERT ON STOCKS.発注表
　　FOR EACH ROW
　　CALL STOCKS.発注ルーチン(NEW.取引製品, NEW.発注数量);

上記のように、プロシジャルーチンとトリガを定義して、表を更新するSQL文を実行すると、被トリガSQL文で指定したプロシジャルーチンが自動的に実行され、データの一貫性を損なうような更新を抑止することができます。また、プロシジャルーチン中で、お互いに参照関係にある表の結合列の存在をチェックして表間のデータの整合性を保証したり、親となる表の行を削除する契機で、対応する子となる表のデータを自動的に削除するようなトリガを定義することもできます。

ただし、複雑な業務ロジックは、トリガで定義しないようにしてください。トリガは起動元となった表の更新の延長で動作します。トリガの中でトランザクションを制御することはできません。複雑な業務ロジックはプロシジャルーチンで作成し、応用プログラムから直接プロシジャルーチンをCALLするようにしてください。

■トリガの動作

トリガは以下のように動作します。

• トリガの実行順序は次のようになります。連鎖的に実行されるトリガである場合でも、各トリガは同じルールで行単位に実行されます。
  1. トリガを定義している表を更新するSQL文が実行されます。
  2. SQL文で影響を受ける行をループします。
  3. SQL文がUPDATEまたはDELETEする行を位置づけます。
  4. 定義されているBEFOREトリガを実行します。
  5. 1)のSQL文により、行がUPDATEまたはDELETE、あるいはINSERTされます。
  6. 定義されているAFTERトリガを実行します。
• 同一の表に対して、同一のトリガ事象が複数定義された場合、トリガは順不同に実行されます。同じトリガ動作時点を指定したお互いのトリガは実行順序に影響されないように考慮してください。
• ユーティリティ機能を利用して、データのロード(rdbsloaderコマンド)およびデータベースの再初期化(rdbfmtコマンド)を実行する場合、その実行の対象となる表にトリガが定義されていても、トリガは作用しません。
• トリガを起動する元となったSQL文で更新している表を、被トリガSQL文で更新することはできません。
• 被トリガSQL文で更新する表にトリガが定義されていると、連鎖的にトリガが実行されます。一度実行されたトリガの延長で、再び同じトリガが実行された場合は、エラーになります。
• 被トリガSQL文は、トリガを起動するSQL文を実行したトランザクションと同じトランザクションとして動作します。トリガを起動するSQL文にLOCK_MODEを指定している場合は、被トリガSQL文により実行される各SQLデータ操作文に、暗黙的に同じLOCK_MODEが指定されたものとして動作します。
• 被トリガSQL文で、データベースの更新を行う場合、更新する表に定義されている一意性制約やNOT NULL制約は、SQL文ごとに検査されます。CALL文を指定している場合は、CALL文中の各SQL文ごとに制約が検査されます。
• 被トリガSQL文中でエラーが発生した場合は、トリガを起動する元となったSQL文の実行が取り消され、エラーとなります。被トリガSQL文にCALL文を指定した場合は、呼び出されるプロシジャルーチン内の各SQL文の実行でエラーが発生すると、プロシジャルーチンに指定したエラー処理に従ってSQL文の実行を継続することができます。また、CALL文の実行をエラーにして、トリガを起動する元となったSQL文の実行を取り消し、エラーとすることもできます。
• 被トリガSQL文にCALL文を指定する場合、呼び出したプロシジャルーチン内で、トランザクションロールバックの例外が発生した場合は(SQLSTATEの例外コードが40)、トリガを起動する元となったSQL文を含むトランザクションが自動的にロールバックされます。

■トリガ定義での注意事項

トリガ定義での注意事項を、以下に示します。

• トリガを起動する元となったSQL文に副問合せを指定している場合、副問合せで指定している表を被トリガSQL文で更新することはできません。
• UPDATE文:探索で表の一意性制約の列を更新すると、SQL文で複数の行を更新している時、一時的にデータの一意性を満たしていない状況が発生することがあります。このような場合に実行されるトリガの被トリガSQL文に指定したCALL文の延長で、トリガを起動する元となった更新中の行を参照すると、一時的に一意性を崩している更新中の行を参照できないことがあります。このため、CALL文で呼び出すプロシジャルーチンで、トリガの起動元となった表の更新後の行を参照する必要がある場合には、CALL文の引数で必要な列の値を受け渡すようにしてください。
• トリガ定義時の権限

  トリガの定義者は、トリガを定義するスキーマに対するCREATE権、トリガを定義する表に対するTRIGGER権および被トリガSQL文に指定したSQL操作に対応する権限が必要です。

  • INSERT文を指定する場合：被トリガSQL文で指定する表に対するINSERT権が必要です。
  • CALL文を指定する場合：被トリガSQL文で指定するプロシジャルーチンに対する実行権が必要です。
• 被トリガSQL文実行時の権限チェック

  あるSQL文がトリガ契機となり、被トリガSQL文が実行された場合、その被トリガSQL文の権限チェックは行われません。応用プログラムの実行者は、被トリガSQL文で指定した表に対する権限は必要ありません。