SQL 解説書

CREATE TRIGGER

CREATE TRIGGER ステートメントは、データベースにトリガーを定義します。

呼び出し

このステートメントは、アプリケーション・プログラムに組み込むか、 または動的 SQL ステートメントを使用して発行することができます。 このステートメントは、動的に準備可能な実行可能ステートメントです。 しかし、バインド・オプション DYNAMICRULES BIND を適用する場合、 ステートメントを動的に準備することはできません (SQLSTATE 42509)。

許可

トリガーを作成する際に、 このステートメントの許可 ID には以下の特権が少なくとも 1 つ含まれている必要があります。

このステートメントの許可 ID に SYSADM 権限または DBADM 権限がない場合には、 トリガーが存在する限り、 ステートメントの許可 ID が持つ特権 (PUBLIC 特権またはグループ特権は考慮に入れない) に 以下のすべてが含まれている必要があります。

SYSADM 権限を所持しているために、 トリガーの定義者がトリガーの作成しか行えないような場合、 その定義者には、トリガーを作成できるようにする目的で明示的な DBADM 権限が付与されます。

構文

>>-CREATE TRIGGER--trigger-name----+-NO CASCADE BEFORE-+-------->
                                   '-AFTER-------------'
 
>-----+-INSERT-----------------------------+--ON--table-name---->
      +-DELETE-----------------------------+
      '-UPDATE--+------------------------+-'
                |    .-,--------------.  |
                |    V                |  |
                '-OF----column-name---+--'
 
>-----+----------------------------------------------------------------------+>
      |              .----------------------------------------------------.  |
      |              V   (1)    (2)          .-AS-.                       |  |
      '-REFERENCING-------------------+-OLD--+----+--correlation-name--+--+--'
                                      |     .-AS-.                     |
                                      +-NEW-+----+--correlation-name---+
                                      |           .-AS-.               |
                                      +-OLD_TABLE-+----+--identifier---+
                                      |           .-AS-.               |
                                      '-NEW_TABLE-+----+--identifier---'
 
>-----+-FOR EACH ROW---------------+--MODE DB2SQL--------------->
      |  (3)                       |
      '--------FOR EACH STATEMENT--'
 
>-----| triggered-action |-------------------------------------><
 
triggered-action
 
|--+-------------------------------+---------------------------->
   '-WHEN--(--search-condition--)--'
 
>-----+-triggered-SQL-statement--------------------------------+-|
      |               .-------------------------------.        |
      |               V                               |        |
      '-BEGIN ATOMIC-----triggered-SQL-statement--;---+---END--'
 

注:

  1. OLD と NEW は、それぞれ一度だけ指定できます。

  2. OLD_TABLE と NEW_TABLE は、それぞれ一度だけ、 AFTER トリガーに対してのみ指定できます。

  3. FOR EACH STATEMENT は、BEFORE トリガーには指定できません。

説明

trigger-name
トリガーの名前を指定します。 暗黙のスキーマ名または明示スキーマ名を含む名前は、 カタログにすでに記述されているトリガーを指定するものであってはなりません (SQLSTATE 42710)。 2 つの部分からなる名前を指定する場合、 "SYS" で始まるスキーマ名は使用できません (SQLSTATE 42939)。

NO CASCADE BEFORE
対象となる表の実際の更新によって生じる変更がデータベースに適用される前に、 関連するトリガー・アクションを適用することを指定します。 また、このトリガーのトリガー・アクションが、 他のトリガーを活動化することがないことも指定します。

AFTER
対象となる表の実際の更新によって生じる変更がデータベースに適用された後で、 関連するトリガー・アクションを適用することを指定します。

INSERT
指定した基礎表に INSERT 操作が適用される場合には必ず、 このトリガーに関連するトリガー・アクションを実行することを指定します。

DELETE
指定した基礎表に DELETE 操作が適用される場合には必ず、 このトリガーに関連するトリガー・アクションを実行することを指定します。

UPDATE
指定した列または暗黙に指定される列に従って、 指定した基礎表に UPDATE 操作が適用される場合には必ず、 このトリガーに関連するトリガー・アクションを実行することを指定します。

オプションの column-name のリストの指定がない場合、 暗黙に表のすべての列が指定されます。 したがって、column-name リストを省略すると、 表の列のいずれかの更新によってトリガーが活動化されることが暗に指定されます。

OF column-name,...
指定する各 column-name (列名) は、 基礎表の列でなければなりません (SQLSTATE 42703)。 トリガーが BEFORE トリガーである場合、指定される column-name は、 識別列以外の生成列ではありません (SQLSTATE 42989)。 column-name リストの 1 つの column-name を 複数回指定することはできません (SQLSTATE 42711)。 トリガーは、column-name リストに指定した列が更新される場合にのみ 活動化されることになります。

ON table-name
トリガー定義の対象となる表を指定します。 table-name に指定する名前は、基礎表、 または解決結果が基礎表になる別名でなければなりません (SQLSTATE 42809)。 この名前に、カタログ表 (SQLSTATE 42832)、要約表 (SQLSTATE 42997)、 宣言された一時表 (SQLSTATE 42995)、 あるいはニックネーム (SQLSTATE 42809) を指定することはできません。

REFERENCING
変換変数 の相関名と変換表 の表名を指定します。 相関名には、トリガーとなる SQL 操作によって影響を受ける一連の行の中の特定の行を指定します。 表名には、影響を受ける行の集合全体を指定します。 トリガーとなる SQL 操作によって影響を受ける各行をトリガー・アクションで使用するには、 次のようにして指定される correlation-name (相関名) によって列を修飾します。

OLD AS correlation-name
トリガーとなる SQL 操作の前の時点での行の状態を指定する相関名を指定します。

NEW AS correlation-name
トリガーとなる SQL 操作によって変更された行が、 すでに実行された BEFORE トリガーの SET ステートメントによってさらに変更された時の状態を指定する 相関名を指定します。

トリガーとなる SQL 操作によって影響を受ける行全体の集合をトリガー・アクションで使用するには、 次のように指定される一時表名を使用します。

OLD_TABLE AS identifier
影響を受ける行の集合の、 トリガーとなる SQL 操作の前のものを指定する一時表名を identifier に指定します。

NEW_TABLE AS identifier
トリガーとなる SQL 操作による影響を受けた行が、 すでに実行された BEFORE トリガーによってさらに変更された状態を指定する 一時表名を identifier に指定します。

REFERENCING 文節には、次の規則が適用されます。

FOR EACH ROW
対象となる表の行で、 トリガーとなる SQL 操作によって影響を受ける各行ごとに、 トリガー・アクションが一度ずつ適用されるよう指定します。

FOR EACH STATEMENT
トリガー・アクションが、 ステートメント全体で一度だけ適用されることを指定します。 このタイプのトリガー細分性は、BEFORE トリガーには指定できません (SQLSTATE 42613)。 指定すると、 トリガーとなる UPDATE または DELETE ステートメントによって影響を受ける行がない場合でも、 UPDATE トリガーまたは DELETE トリガーが活動化されることになってしまいます。

MODE DB2SQL
この文節は、トリガーのモードを指定するために使用します。 これは、現在サポートされている唯一有効なモードです。

triggered-action
トリガーが活動化された時に実行されるアクションを指定します。 1 つのトリガー・アクションは、 1 つまたはいくつかの triggered-SQL-statement と、 その triggered-SQL-statement が実行されるためのいくつかのオプション条件によって構成されます。 指定するトリガー・アクションに複数の triggered-SQL-statement がある場合、 これらを BEGIN ATOMIC キーワードと END キーワードで囲んでセミコロンで区切る必要があります。 83 それらは、指定された順序で実行されます。

WHEN (search-condition)
真、偽、または未知となる条件を指定します。 search-condition (探索条件) を使うことによって、 特定のトリガー・アクションを実行するかどうかを決めることができます。

関連するアクションは、指定した探索条件が真と評価される場合にのみ実行されます。 WHEN 文節を省略すると、 関連する triggered-SQL-statement は常に実行されることになります。

triggered-SQL-statement
トリガーが BEFORE トリガーの場合、トリガーにより実行される SQL ステートメントは、 次のいずれかでなければなりません (SQLSTATE 42987)。
  • 全選択84
  • SET 変換変数 SQL ステートメント
  • SIGNAL SQLSTATE ステートメント

トリガーが AFTER トリガーの場合、トリガーにより実行される SQL ステートメントは、 次のいずれかでなければなりません (SQLSTATE 42987)。

  • INSERT SQL ステートメント
  • 探索 UPDATE SQL ステートメント
  • 探索 DELETE SQL ステートメント
  • SIGNAL SQLSTATE ステートメント
  • 全選択84

triggered-SQL-statement で、未定義の変換変数 (SQLSTATE 42703) や、 宣言された一時表 (SQLSTATE 42995) を参照することはできません。

BEFORE トリガーの triggered-SQL-statement は、 REFRESH IMMEDIATE を定義した要約表を参照できません (SQLSTATE 42997)。

BEFORE トリガーの triggered-SQL-statement は、 新しい変換変数で識別列以外の生成列を参照することはできません (SQLSTATE 42989)。

例 1: 会社が管理する従業員の数の自動追跡を実行する 2 つのトリガーを作成します。 このトリガーは、次の表に作用します。

EMPLOYEE 表 (列は ID、NAME、ADDRESS、および POSITION)
COMPANY_STATS 表 (列は NBEMP、NBPRODUCT、および REVENUE)

最初のトリガーは、 新しい従業員を採用するたびに (つまり EMPLOYEE 表に新しい行が挿入されるたびに)、 従業員数に 1 を加算します。

   CREATE TRIGGER NEW_HIRED
      AFTER INSERT ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

2 番目のトリガーは、 従業員が会社を退職するたびに (つまり EMPLOYEE 表から行が削除されるたびに)、 従業員数から 1 を減算します。

   CREATE TRIGGER FORMER_EMP
      AFTER DELETE ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1

例 2: 部品のレコードが更新されると、 以下の検査と (必要ならば) アクションを実行するトリガーを作成します。

手持ち数量 (ON_HAND) が最大在庫量 (MAX_STOCKED) の 10% 未満になった場合、 その部品の品目数として最大在庫量から手持ち数量を引いた数を指定した出荷依頼書を発行します。

このトリガーは、PARTNO、DESCRIPTION、ON_HAND、MAX_STOCKED、 および PRICE の列を含む PARTS 表に作用します。

ISSUE_SHIP_REQUEST は、追加部品の注文書を、発注先に送るユーザー定義関数です。

	CREATE TRIGGER REORDER
     AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
     REFERENCING NEW AS N
     FOR EACH ROW MODE DB2SQL
     WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
     BEGIN ATOMIC
     VALUES(ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO));
     END

例 3: 更新の結果、現行の給与の 10 % を超える昇給になった場合にエラーを生じさせるトリガーを作成します。

   CREATE TRIGGER RAISE_LIMIT
     AFTER UPDATE OF SALARY ON EMPLOYEE
     REFERENCING NEW AS N OLD AS O
     FOR EACH ROW MODE DB2SQL
     WHEN (N.SALARY > 1.1 * O.SALARY)
            SIGNAL SQLSTATE '75000' ('Salary increase>10%')

例 4: 株価の変更を記録し追跡するアプリケーションについて考えます。 データベースには、CURRENTQUOTE および QUOTEHISTORY という 2 つの表が含まれています。

表:     CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
        QUOTEHISTORY (SYMBOL, QUOTE, QUOTE_TIMESTAMP)

CURRENTQUOTE の QUOTE (相場) 列が更新されると、 新しい相場とタイム・スタンプを QUOTEHISTORY 表にコピーするようにします。 CURRENTQUOTE の STATUS (状況) 列も、 次のような株の状況が反映されるように更新します。

  1. 値上がり
  2. 今年の新高値
  3. 値下がり
  4. 今年の新安値
  5. 変わらず

これを実現する CREATE TRIGGER ステートメントは、次のようになります。


脚注:

83
コマンド行プロセッサーでこの形式を使用する場合、 ステートメントの終了文字をセミコロンにすることはできません。 代替の終了文字の指定については、コマンド解説書 を参照してください。

84
全選択の前に共通表式を指定できます。


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]