FrontPage


Firebird SQLリファレンス:CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER



CREATE TRIGGER

 トリガを作成します。同時に、それが起動されるタイミングや動作を定義します。 DSQL,isqlで使用できます。  この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの 各リリースノートにおける追加内容に関して反映・統合されています。

構文

Firebird 1.5以降:(マルチアクショントリガ機能追加)

CREATE TRIGGER name FOR table
   [ACTIVE | INACTIVE]
   {BEFORE | AFTER} <multiple_action>
   [POSITION number]
   AS <trigger_body> terminator
<multiple_action> ::=
   <single_action> [OR <single_action> [OR <single_action>]]
<single_action> ::= {INSERT | UPDATE | DELETE}

IB6/Firebird 1.0:

CREATE TRIGGER name FOR table [ACTIVE | INACTIVE]
   {BEFORE | AFTER} {DELETE | INSERT | UPDATE}
   [POSITION number]
   AS <trigger_body> terminator
<trigger_body> = [<variable_declaration_list>] <block>

Firebird 1.5以降:(初期化できるようになった)

<variable_declaration_list> =
   DECLARE VARIABLE var <datatype> [{'=' | DEFAULT} value];
   [DECLARE VARIABLE var <datatype> [{'=' | DEFAULT} value]; …]

IB6/Firebird 1.0:

<variable_declaration_list> =
   DECLARE VARIABLE var <datatype>;
   [DECLARE VARIABLE var <datatype>; …]
<block> =
   BEGIN
       <compound_statement>
       [<compound_statement> …]
   END
<compound_statement> = {<block> | statement;}
<datatype> = SMALLINT | INTEGER | BIGINT | FLOAT | DOUBLE PRECISION
   | {DECIMAL | NUMERIC} [(precision [, scale])]
   | {DATE | TIME | TIMESTAMP}
   | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}
       [(int)] [CHARACTER SET charname]
   | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]



引数説明
nameトリガの名前です。データベース内において一意でなければなりません。
tableテーブルまたビューの名前です。指定したテーブル・ビューで指定した操作が行われたときに、トリガが起動されます。
ACTIVE|INACTIVE省略可能です。トランザクション終了でのトリガ動作を指定します。
・ACTIVE : (省略時デフォルト) トリガの操作は有効となります。
・INACTIVE : トリガの操作は有効となりません。
BEFORE|AFTER必須指定です。トリガの起動タイミングを指定します。
・BEFORE : 関連操作前を指定します。
・AFTER : 関連操作後を指定します。
※関連操作は、DELETE, INSERT, UPDATEのいずれかです。
DELETE|INSERT|UPDATEテーブルに対して指定した操作が行われるときにトリガを起動します。
POSITION number同アクションに対して複数のトリガがあるときに、トリガが実行される順序を指定するための数値です。0〜32767の間である必要があります。
・数値が少ないトリガが先に実行されます。
・指定を省略した場合、0指定を行ったことと等価となり、通常は最初に実行されます。
・この値は複数のトリガ間で連続している必要はありません。同じ数値のトリガが複数ある場合、その実行順序はランダムとなり、保証されません。
DECLARE VARIABLE var datatypeトリガ内で使用するローカル変数を宣言します。ローカル変数は、使用に先立ち DECLARE VARIABLEとその終了のセミコロンで宣言される必要があります。
・var : 変数名で、トリガ内でローカルであり、かつ一意でなければなりません。
・datatype: ローカル変数のデータ型名です。
・Firebird1.5以降では、値を初期化出来るようになりました。
statement処理内容を定義する、プロシージャやトリガを記述する1つの文です。BIGINとENDの間にいくつか記述しますが、BEGIN,END以外の文は必ずセミコロン(;)で終わっている必要があります。
terminatorSET TERMで変更した終端文字です。通常の文で使用されるセミコロンはトリガの記述で使用するため、isqlでは変更が必要となります。
・トリガ本体の終了を表します。
・isqlでのみ使用します。


詳細の説明

 CREATE TRIGGER により、データベースに新しいトリガを定義します。 トリガとは、テーブルやビューにおいて行の追加・削除、変更が行われたときに 自動的に起動される、自己完結した1つのプログラムです。 (訳注:この機能により、テーブルやビューに対して加工を行ったり、ログの自動取得 などの様々な機能を付加することが出来ます。)


 トリガは、アプリケーションなどから直接呼び出されることはありません。 アプリケーションやユーザにより、INSERT,UPDATE,DELETEなどによって行の操作が 行われたとき、関連付けられたトリガが自動的に呼び出されて実行されます。  ただし、リードオンリーのビューに対して UPDATE に対するトリガを定義した 場合、更新による実行を試みようとしてもにそれが実行されることは決してありません。


 トリガの定義はヘッダと本体の2つで構成されいます。

  • ヘッダの定義内容には、以下のようなものがあります。
    • トリガの名前。データベース内において、他のオブジェクトも含めて一意な名前 である必要があります。
    • トリガが関連付けられるテーブルの名前。
    • トリガが実行されるタイミングの指定。
  • 本体の定義には、以下のものが含まれます。
    • 必要ならば、ローカル変数と型定義のリスト。Firebird1.5では、変数の宣言時に 値を初期化出来るようになりました。
    • BEGIN で始まり END で括られる、プロシージャ・トリガ記述言語により記述された いくつかの文で構成されるブロック。ブロック内には、別のブロックを含むことが 出来ます。つまり、ブロックの多重ネストが可能となっています。

重要 トリガの本体部分の記述では、終端文字としてセミコロンを使用します。 このため、isqlによりストアドプロシージャの定義を行う場合は、SET TERM により 終端文字をセミコロン以外に変更する必要があります。また、このような場合は、 CREATE TRIGGER 文を終了させた後に、終端文字を元のセミコロンに戻しましょう。

 トリガは、テーブルに関連付けます。テーブルに対するすべての特権をもつ所有者 はもとより、所有者によりテーブルに対してアクセスできる特権を与えられたすべて のユーザーが、トリガを実行することが出来ます。 (訳注:EXECUTE特権はなくても実行できるということ。)

 ユーザーやプロシージャに対して特権を与えるのと同様に、トリガに対しても GRANT文により特権を与えることが出来ます。この場合、"TO username"の代わりに "TO TRIGGER trigger_name"を使用して下さい。特権を削除する場合は、同様にして REVOKE文を使用してください。

 ユーザーがトリガが起動される要因となる操作を行うときに、以下の条件のいずれか に当てはまる場合は、起動されたトリガは起動要因となった操作に対する特権を所有 します。

  • トリガ自体が、すでに特権を所有している場合。
  • 起動要因を発生させたユーザーが、その要因に対する特権を所有している場合。

 処理内容を記述するプロシージャ・トリガ記述言語に関しては、 CREATE PROCEDUREのプロシージャ・トリガ記述言語を参照して下さい。

 Firebird 1.5 以降では、マルチアクショントリガの定義が可能となりました。
 これは複数の行操作(INSET/UPDATE/DELETE)に対して、同じトリガを関連付けることが出来る機能です。具体的には、関連付ける操作を OR で結びます。
 また、この機能追加に伴い、起動要因となった操作を識別するためのコンテキスト変数が追加されました。追加された変数は、INSERTING、UPDATING、DELETINGです。 この機能の追加により、似た処理が多い別々の行操作用トリガ定義を1つにまとめる 事が出来るため、可読性と保守性の向上が期待できます。

※isqlの場合、SET TRIGGERSによりデータベースに定義されたトリガのリストが 参照できます。また、SET TRIGGER trigger_name により、指定したトリガの現在の メタデータを参照できます。

用例

すべて、isqlでの例です。

  • トリガSAVE_SALARY_CHANGEは、テーブルEMPLOYEEが更新された時に列SALARYの値が 変更されていた場合、SALARY_HISTORYにログを取得します。
    SET TERM !! ;
    
    CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
    AFTER UPDATE AS
    BEGIN
     IF (OLD.SALARY <> NEW.SALARY) THEN
       INSERT INTO SALARY_HISTORY
         (EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE)
         VALUES (OLD.EMP_NO, 'now', USER, OLD.SALARY,
         (NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY);
    END !!
    
    SET TERM ; !!
  • トリガSET_CUST_NOは、テーブルCUSTOMERに新行を追加するときに、列CUST_NOに ジェネレータで生成されたユニークなカスタマ番号を付与します。BEFOREが指定 されているため、このトリガは挿入前に実行されます。
    SET TERM !! ;
    
    CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
    BEFORE INSERT AS
    BEGIN
      NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
    END !!
    
    SET TERM ; !!
  • トリガPOST_NEW_ORDERは、テーブルSALESに行が挿入されると、"new_order"という 名前のイベントをポストします。
    SET TERM !! ;
    
    CREATE TRIGGER POST_NEW_ORDER FOR SALES
    AFTER INSERT AS
    BEGIN
      POST_EVENT 'new_order';
    END !!
    
    SET TERM ; !!
  • BFFORE,AFTRE,POSITION指定による実行順序制御の例です。本体部分は省略されていますので 注意して下さい。
    • 定義ヘッダです。
      CREATE TRIGGER A FOR accounts
      BEFORE UPDATE
      POSITION 5 … /*Trigger body follows*/
      
      CREATE TRIGGER B FOR accounts
      BEFORE UPDATE
      POSITION 0 … /*Trigger body follows*/
      
      CREATE TRIGGER C FOR accounts
      AFTER UPDATE
      POSITION 5 … /*Trigger body follows*/
      
      CREATE TRIGGER D FOR accounts
      AFTER UPDATE
      POSITION 3 … /*Trigger body follows*/
    • 次のようにしてテーブルacountが更新(UODATE)されると、以下の順序で処理が 行われます。
      UPDATE accounts SET account_status = 'on_hold'
      WHERE account_balance <0;
  1. トリガ B 実行
  2. トリガ A 実行
  3. 行更新の実行
  4. トリガ D 実行
  5. トリガ C 実行

参照

ALTER EXCEPTION , ALTER TRIGGER , CREATE EXCEPTION , CREATE PROCEDURE , DROP EXCEPTION , DROP TRIGGER , EXECUTE PROCEDURE

 これ以上の情報が欲しい場合は、Data Definition Guideの creating and using triggers を参照して下さい。


ALTER TRIGGER

 既存のトリガを変更します。 DSQL,isqlで使用できます
 この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの 各リリースノートにおける追加内容に関して反映・統合されています。

構文

Firebird1.5以降:(マルチアクショントリガ機能追加)

ALTER TRIGGER name
    [ACTIVE | INACTIVE]
    [{BEFORE | AFTER} <multiple_action>]
    [POSITION number]
    [AS <trigger_body> terminator]
<multiple_action> ::=
    <single_action> [OR <single_action> [OR <single_action>]]
<single_action> ::= {INSERT | UPDATE | DELETE}

IB6/Firebird 1.0:

ALTER TRIGGER name
    [ACTIVE | INACTIVE]
    [{BEFORE | AFTER} {DELETE | INSERT | UPDATE}]
    [POSITION number]
    [AS <trigger_body>] [terminator]
引数説明
name変更を行いたい、既存のトリガ名です。
ACTIVE|INACTIVE省略可能です。トランザクション終了でのトリガ動作を指定します。
・ACTIVE : (省略時デフォルト) トリガの操作は有効となります。
・INACTIVE : トリガの操作は有効となりません。
BEFORE|AFTER必須指定です。トリガの起動タイミングを指定します。
・BEFORE : 関連操作前を指定します。
・AFTER : 関連操作後を指定します。
※関連操作は、DELETE, INSERT, UPDATEのいずれかです。
DELETE|INSERT|UPDATEテーブルに対して指定した操作が行われるときにトリガを起動します。
POSITION number同アクションに対して複数のトリガがあるときに、トリガが実行される順序を指定するための数値です。0〜32767の間である必要があります。
・数値が少ないトリガが先に実行されます。
・指定を省略した場合、0指定を行ったことと等価となり、通常は最初に実行されます。
・この値は複数のトリガ間で連続している必要はありません。同じ数値のトリガが複数ある場合、その実行順序はランダムとなり、保証されません。
trigger_bodyトリガ定義の本体です。プロシージャ・トリガ記述言語で記述されたブロックです。
※詳細に関しては、CREATE TRIGGERと同様ですので参照して下さい。
terminatorSET TERMで変更した終端文字です。通常の文で使用されるセミコロンはトリガの記述で使用するため、isqlでは変更が必要となります。
・トリガ本体の終了を表します。
・isqlでのみ使用します。

詳細の説明

 ALTER TRIGGER により、既存のトリガの定義を変更できます。ALTER TRIGGERは、 CREATE TRIGGER と似ていますがほとんどの引数を省略できます。その場合は、 デフォルト値もしくはCREATE TRIGGERまたは最後の ALTER TRIGGER で設定された 現在値が設定されます。

 ALTER TRIGGER では次のような変更が可能です。

  • ヘッダ情報のみの変更。トリガの活性化設定、アクションとの関連付け、 実行タイミング、実行順序の制御値などです。
  • 本体部分のみの変更。ASに続くトリガ処理内容を記述した部分の変更です。
  • ヘッダと本体両方の変更。この場合は、トリガの再定義と同じようなことに なります。

 Firebird 1.5 以降では、マルチアクショントリガの定義が可能となりました。
 これは複数の行操作(INSET/UPDATE/DELETE)に対して、同じトリガを関連付けること が出来る機能です。具体的には、関連付ける操作を OR で結びます。
 マルチアクション定義の関連付けのみの変更も可能です。

 トリガの変更が出来るのは、作成者、SYSDBAユーザーおよびOSの管理者権限 を持ったユーザーのみです。

※テーブルのCHECK制約によって自動的に定義されたトリガを変更するためには、 ALTER TABLE を使用して制約の変更を行う必要があります。

※isqlの場合、SET TRIGGERSによりデータベースに定義されたトリガのリストが 参照できます。また、SET TRIGGER trigger_name により、指定したトリガの現在の メタデータを参照できます。

用例

すべて、isqlでの例です。

  • トリガSET_CUST_NOを非活性化します。
    ALTER TRIGGER SET_CUST_NO INACTIVE;
  • トリガSET_CUST_NOを変更します。行が追加されたとき、同時に NEW_CUSTNO に 行を追加します。
    SET TERM !! ;
    
    ALTER TRIGGER SET_CUST_NO FOR CUSTOMER
    BEFORE INSERT AS
    BEGIN
      NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
      INSERT INTO NEW_CUSTOMERS(NEW.CUST_NO, TODAY)
    END !!
    
    SET TERM ; !!

参照

 CREATE TRIGGER , DROP TRIGGER

 これ以上の情報が欲しい場合は、Data Definition Guideの triggers を参照して 下さい。


DROP TRIGGER

データベースより、既存であるユーザー定義のトリガを削除します。 DSQL,isqlで使用できます。

構文

DROP TRIGGER name
引数説明
name削除を行いたい、既存のトリガの名前です。

詳細の説明

 DROP TRIGGER により、ユーザーが定義したトリガをデータベースから削除します。
 システム定義のトリガやCHECK制約で作成されたトリガは削除できません。 そのようなトリガ定義を削除する場合は、ALTER TABLE を使用します。

 トリガが有効なトランザクションで使用されている場合、そのトランザクションが 終了するまで削除は行われません。

 トリガの削除が出来るのは、作成者、SYSDBAユーザーまたは OS の管理者権限を 持ったユーザーのみです。

※ 一時的にトリガを使用しない場合には、ALTER TRIGGER に INACTIVE を指定して 非活性化することにより実現しましょう。

用例

 isqlで、トリガの削除を行う例です。

DROP TRIGGER POST_NEW_ORDER;

参照

 ALTER TRIGGER , CREATE TRIGGER