3.23 CREATE TRIGGER文(トリガ定義)

機能

トリガ定義は、データ操作に連動したほかの表へのデータ操作を定義します。

記述形式

構文の構成

参照項番

探索条件 → “2.12 探索条件”
INSERT文 → “3.48 INSERT文”
CALL文 → “3.5 CALL文”
日本語文字列 → “2.1.3 トークン”

権限

トリガ定義の実行者は、以下の条件を満たすことが必要です。
- スキーマに対するCREATE権を持っていることが必要です。
- 表名で指定された表に対するTRIGGER権を持っていることが必要です。
- 被トリガSQL文中に指定された表に対して、実施する操作に必要な各権限を持っていることが必要です。
- 被トリガSQL文中に順序を指定した場合は、順序に対するSELECT権が必要です。
- CALL文を指定する場合は、プロシジャルーチンに対するEXECUTE権が必要です。

一般規則

CREATE TRIGGER文を準備可能文として使用する場合、ホスト変数または動的パラメタを指定できません。

トリガ名

作成するトリガの名前を指定します。
トリガ名には、36文字以内の先頭が英字で始まる英数字、または18文字以内の日本語文字列を指定します。
トリガ名は、スキーマ内で一意の名前であることが必要です。
トリガ定義がスキーマ定義に含まれる場合、トリガ名のスキーマ名の修飾を省略した場合は、スキーマ定義で指定したスキーマ名で修飾したとみなされます。また、スキーマ名で修飾する場合は、スキーマ定義で指定したスキーマ名であることが必要です。

トリガ動作時点

BEFOREを指定した場合:: 表名に指定した表を更新する前に被トリガ動作が実行されます。

AFTERを指定した場合:: 表名に指定した表を更新したあとに被トリガ動作が実行されます。

トリガ事象

INSERTを指定した場合:: INSERT文で表に行を挿入すると、被トリガ動作が実行されます。

DELETEを指定した場合:: DELETE文で表の行を削除すると、被トリガ動作が実行されます。

UPDATEを指定した場合:: UPDATE文で表の列の値を更新すると、被トリガ動作が実行されます。トリガ列リストを指定した場合、指定した列のいずれかを更新すると、被トリガ動作が実行されます。

トリガ列リスト

トリガ列リストは列名をカンマ(,)で区切って指定します。
トリガ列リストの列名は同一の列名を指定することはできません。
トリガ列リストに指定する列は、表名で指定した表に定義されていることが必要です。

表名

トリガを作成する対象の表の名前を指定します。
表名にビュー表を指定することはできません。
表名は、定義するトリガと別のデータベースに属する表を指定することはできません。
表名および被トリガSQL文の表名にOBJECT構造の表を指定することはできません。

REFERENCING句

REFERENCING句のOLDに指定する相関名を“旧値相関名”、NEWに指定する相関名を“新値相関名”と呼びます。
旧または新値別名は、表の状態としてトリガ事象の実行前後を識別するための別名です。旧または新値別名リストは、旧または新値別名の並びです。
REFERENCING句を指定することにより、旧値相関名あるいは新値相関名を、OLDあるいはNEWから別のものに変更することができます。
REFERENCING句を指定する場合、旧値相関名と新値相関名は互いに同じであってはなりません。また、被トリガSQL文中で指定された表名と同じであってはなりません。

相関名

相関名は、36文字以内の先頭が英字で始まる英数字、または18文字以内の日本語文字列指定します。

トリガ事象と、列を修飾する相関名の関係を以下に示します。

トリガ事象	列を修飾する相関名
INSERT	旧値相関名は指定できません。
DELETE	新値相関名は指定できません。
UPDATE	旧値相関名と新値相関名が指定できます。

被トリガ動作

被トリガ動作は、被トリガSQL文を実行する条件を指定します。
WHEN句の探索条件に副問合せおよびROWNUMを指定することはできません。

被トリガSQL文

被トリガSQL文には問合せ指定を含まないINSERT文、または、CALL文を指定することができます。
被トリガSQL文にCALL文を指定することにより、複数の文からなる一連の処理を被トリガSQL文として実行することができます。
被トリガSQL文にCALL文を指定する場合、CALL文で呼び出されるプロシジャルーチンではトランザクション管理文を実行することができません。トランザクション管理文を実行するとプロシジャルーチン中のCOMMIT文やROLLBACK文の実行がエラーになります。
WHEN句の探索条件、あるいは被トリガSQL文に指定したトリガ対象表の列は、それがトリガ事象を実行する前の値か実行したあとの値かを示すため、旧値相関名であるOLD、あるいは新値相関名であるNEWで修飾しなければなりません。
被トリガSQL文にCALL文を指定する場合、CALL文で示すプロシジャルーチンのパラメタのパラメタモードは、INでなければなりません。

使用例

例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

在庫表の在庫数量を0より小さい値に変更しようした場合、SQL文をエラーにする処理をトリガの定義とプロシジャの定義で行います。

プロシジャの定義

CREATE PROCEDURE S1.在庫チェックルーチン(IN 在庫数量Ｖ INT)
BEGIN
  DECLARE SQLSTATE CHAR(5);
  DECLARE SQLMSG   CHAR(256);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    RESIGNAL;
    END;
   -- ルーチン本体
  IF (在庫数量Ｖ < 0) THEN
    SIGNAL SQLSTATE'60001' '在庫数量不当';
  END IF;
END

トリガの定義

CREATE TRIGGER S1.在庫数チェック
  BEFORE UPDATE OF 在庫数量 ON S1.在庫表
  FOR EACH ROW
  CALL S1.在庫チェックルーチン(NEW.在庫数量)