CREATE TRIGGER 文

この文は、テーブル内にトリガを作成するために使用します。

構文

CREATE [ OR REPLACE ] TRIGGER trigger-name trigger-type
{ trigger-event-list | UPDATE OF column-list }
[ ORDER integer ] ON table-name
[ REFERENCING [ OLD AS old-name ]
   [ NEW AS new-name ] 
   [ REMOTE AS remote-name ] ]
[ FOR EACH { ROW | STATEMENT } ]
[ WHEN ( search-condition ) ]
trigger-body

column-list :  column-name[, ...]

trigger-type : 
BEFORE 
| AFTER 
| INSTEAD OF 
| RESOLVE

trigger-event-list : trigger-event[, ...  ]

trigger-event : 
DELETE 
| INSERT 
| UPDATE

trigger-body：BEGIN 文。BEGIN 文を参照してください。

パラメータ

OR REPLACE 句 OR REPLACE (CREATE OR REPLACE TRIGGER) を指定すると、新しいトリガが作成されるか、同じ名前の既存のトリガが置き換えられます。
trigger-event トリガは次のイベントによって起動できます。DELETE、INSERT、または UPDATE イベントに複数のトリガを定義するか、UPDATE OF column-list イベントに 1 つのトリガを定義できます。
- DELETE 関連するテーブルのローが削除されると、呼び出されます。
- INSERT このトリガに関連するテーブルに新しいローが挿入されると、呼び出されます。
- UPDATE 関連するテーブルのローが更新されると、呼び出されます。
- UPDATE OF column-list 関連するテーブルのローが更新され、column-list のカラムが修正されると、呼び出されます。このタイプのトリガ・イベントは、trigger-event-list では使用できません。このトリガ用に定義されたトリガ・イベントであることが必要です。この句は、INSTEAD OF トリガでは使用できません。
  処理が必要なイベントごとにトリガを個別に作成できます。または、共有するアクションや、イベントに応じたアクションが複数ある場合は、すべてのイベントに対して 1 つのトリガを作成し、IF 文を使用して実行するアクションを区別できます。トリガ操作の詳細については、トリガ・オペレーション条件を参照してください。
trigger-type ロー・レベルのトリガを定義して、挿入、更新、または削除の前 (BEFORE)、後 (AFTER) または代わり (INSTEAD OF) に実行できます。文レベルのトリガは、文の INSTEAD OF または AFTER の実行を定義できます。
BEFORE UPDATE トリガは、ローを対象に UPDATE が実行されるたび、新しい値が古い値と異なるかどうかに関係なく起動されます。つまり、BEFORE UPDATE トリガに column-list が指定されている場合は、UPDATE 文の SET 句に column-list 内のカラムがあると、トリガが起動します。AFTER UPDATE トリガに column-list が指定されている場合は、column-list 内のカラムの値が UPDATE 文によって変更されると、トリガが起動します。

INSTEAD OF トリガは、通常のビューに定義できるトリガの唯一の形式です。INSTEAD OF トリガは、他の動作を、トリガ動作で置換します。INSTEAD OF トリガが起動すると、トリガ動作はスキップされ、指定された動作が実行されます。INSTEAD OF トリガは、ロー・レベルまたは文レベルで定義できます。文レベルの INSTEAD OF トリガは、ロー・レベルのすべての処理を含め、文全体を置換します。文レベルの INSTEAD OF トリガが起動すると、その文の結果としてロー・レベルのトリガが起動することはありません。ただし、文レベルのトリガの本文が、その他の処理を実行するため、結果として、その他のロー・レベルのトリガが実行されます。

INSTEAD OF トリガを定義する場合は、UPDATE OF column-list 句、ORDER 句、または WHEN 句は使用できません。

INSTEAD OF トリガの機能や制約の詳細については、INSTEAD OF トリガを参照してください。

RESOLVE トリガ型は、SQL Remote とともに使用します。これは、ロー・レベルの UPDATE または UPDATE OF column-list の前だけで起動されます。
FOR EACH 句 トリガをロー・レベルのトリガとして宣言するには、FOR EACH ROW 句を使用します。トリガを文レベルのトリガとして宣言するには、FOR EACH STATEMENT 句を使用するか、または FOR EACH 句を省略します。文レベルのトリガを宣言する場合は、わかりやすくするために、FOR EACH STATEMENT 句を指定することをおすすめします。
ORDER 句 同じタイミング (before、after、resolve) で起動する同じタイプ (insert、update、delete) の追加トリガを定義する場合は、ORDER 句を指定してトリガを起動する順序をデータベース・サーバに指示します。同じタイミングで起動するように設定された同じタイプのトリガの間では、順序番号をユニークにします。ユニークでない順序番号を指定すると、エラーが返されます。順序番号は、連続した順番にする必要はありません (たとえば、1、12、30 と指定できます)。データベース・サーバは、最下位の番号が付いたトリガから順に起動します。
ORDER 句を省略するか、0 を指定すると、データベース・サーバは順序番号 1 を割り当てます。ただし、すでに同じタイプの別のトリガが 1 に設定されている場合は、エラーが返されます。

追加トリガを追加する場合は、トリガの動作が相互に影響するかどうかによって、イベントの同じタイプの既存トリガを修正する必要が生じる可能性があります。相互に影響しない場合、新しいトリガには既存のトリガより大きな ORDER 値が必要です。相互に影響する場合は、他のトリガの動作を検討する必要があり、これらのトリガが起動する順序の変更が必要となる可能性があります。

ORDER 句は、INSTEAD OF トリガではサポートされません。テーブルまたはビューに定義されたタイプ (insert、update、delete) ごとに、1 つの INSTEAD OF トリガだけを使用できます。
REFERENCING 句 REFERENCING OLD 句と REFERENCING NEW 句を使用すると、挿入、削除、または更新されたローを参照できます。この句の性格上、UPDATE は削除とそれに続く挿入として取り扱われます。
INSERT は REFERENCING NEW 句を使います。これは挿入されたローを表します。REFERENCING OLD 句はありません。

DELETE は REFERENCING OLD 句を使います。これは削除されたローを表します。REFERENCING NEW 句はありません。

UPDATE は、REFERENCING OLD 句を使うときは更新前のローを表し、REFERENCING NEW 句を使うときは更新後のローを表します。

REFERENCING OLD と REFERENCING NEW の意味は、トリガがロー・レベルのトリガと文レベルのトリガのどちらかによって異なります。ロー・レベルのトリガの場合、REFERENCING OLD 句を使うと、更新または削除する前のローの値を参照できます。また、REFERENCING NEW 句を使うと、挿入または更新された値を参照できます。OLD ローと NEW ローは、BEFORE と AFTER トリガの中で参照できます。REFERENCING NEW 句を使うと、BEFORE トリガの新しいローを修正してから、挿入または更新操作を行うことができます。

文レベルのトリガの場合、REFERENCING OLD と REFERENCING NEW 句は、ローの古い値と新しい値を保持している宣言されたテンポラリ・テーブルを参照します。これらのテーブルのデフォルト名は deleted と inserted です。

REFERENCING REMOTE 句は SQL Remote で使用します。これを使うと、UPDATE 文の VERIFY 句に設定されている値を参照できます。これは、必ずカラム・リストのトリガ RESOLVE UPDATE または RESOLVE UPDATE OF と一緒に使用してください。
WHEN 句 探索条件が真と評価されたローに対してだけ、トリガが起動されます。WHEN 句は、ロー・レベル・トリガと一緒にだけ使用できます。この句は、INSTEAD OF トリガでは使用できません。
trigger-body トリガの本文には、トリガ・アクションが生じたときに実行されるアクションが含まれます。これは、BEGIN 文で構成されます。BEGIN 文を参照してください。
BEGIN 文にトリガ・オペレーション条件を指定できます。トリガ・オペレーション条件は、トリガを起動したトリガ・イベントに従って動作を行います。たとえば、トリガが更新と削除の両方のために起動するように定義されている場合は、2 つの条件に異なる動作を指定できます。トリガ・オペレーション条件の詳細と例ついては、トリガ・オペレーション条件を参照してください。

備考

CREATE TRIGGER 文は、データベースのテーブルに関連するトリガを作成し、データベースにトリガを格納します。

マテリアライズド・ビューにトリガを定義することはできません。定義すると、SQLE_INVALID_TRIGGER_MATVIEW エラーが返されます。

トリガをロー・レベルのトリガとして宣言すると、各ローを修正する前または後にトリガが実行されます。また、トリガを文レベルのトリガとして宣言すると、トリガ文全体が完了してから、トリガが実行されます。

パーミッション

RESOURCE 権限またはテーブルに対する ALTER パーミッションが必要です。または、テーブルの所有者であるか、DBA 権限が必要です。CREATE TRIGGER はテーブルにテーブル・ロックを設定して、テーブルを排他的に使用します。

参照

標準と互換性

SQL/2003 永続的ストアド・モジュール機能。いくつかの句はベンダ拡張です。

例

この例は、文レベルのトリガを作成します。まず、テーブルを作成する必要があります。

CREATE TABLE t0
( id integer NOT NULL,
 times timestamp NULL DEFAULT current timestamp,
 remarks text NULL,
 PRIMARY KEY ( id )
);

次に、このテーブルについて文レベルのトリガを作成します。

CREATE TRIGGER myTrig AFTER INSERT ORDER 4 ON t0
REFERENCING NEW AS new_name
FOR EACH STATEMENT
BEGIN
  DECLARE @id1 INTEGER;
  DECLARE @times1 TIMESTAMP;
  DECLARE @remarks1 LONG VARCHAR;
  DECLARE @err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
//declare a cursor for table new_name
  DECLARE new1 CURSOR FOR
   SELECT id, times, remarks FROM new_name;
  OPEN new1;
 //Open the cursor, and get the value
  LoopGetRow:
  LOOP
      FETCH NEXT new1 INTO @id1, @times1,@remarks1;
      IF SQLSTATE = @err_notfound THEN
   LEAVE LoopGetRow
      END IF;
      //print the value or for other use
      PRINT (@remarks1);
  END LOOP LoopGetRow;
  CLOSE new1
END;

次の例は、前の例で作成された myTrig トリガを置き換えます。

CREATE OR REPLACE TRIGGER myTrig AFTER INSERT ORDER 4 ON t0
REFERENCING NEW AS new_name
FOR EACH STATEMENT
BEGIN
FOR L1 AS new1 CURSOR FOR
   SELECT id, times, remarks FROM new_name
DO
     //print the value or for other use
     PRINT (@remarks1);
END FOR;
END;

次の例は、BEFORE UPDATE トリガで REFERENCING NEW を使用する方法を示します。次の例では、新規 Employees テーブルの郵便番号が大文字になるようにしています。

CREATE TRIGGER emp_upper_postal_code
BEFORE UPDATE OF PostalCode
ON Employees
REFERENCING NEW AS new_emp
FOR EACH ROW
WHEN ( ISNUMERIC( new_emp.PostalCode ) = 0 )
BEGIN
   -- Ensure postal code is uppercase (employee might be 
   -- in Canada where postal codes contain letters)
   SET new_emp.PostalCode = UPPER(new_emp.PostalCode)
END;

UPDATE Employees SET state='ON', PostalCode='n2x 4y7' WHERE EmployeeID=191;
SELECT PostalCode FROM Employees WHERE EmployeeID = 191;

次の例は、BEFORE DELETE トリガで REFERENCING OLD を使用する方法を示します。この例は、Employees テーブルから、退職していない従業員が削除されないようにします。

CREATE TRIGGER TR_check_delete_employee 
BEFORE DELETE
ON Employees
REFERENCING OLD AS current_employees
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
   IF current_employees.TerminationDate IS NULL THEN
    RAISERROR 30001 'You cannot delete an employee who has not been fired';
    END IF;
END;

次の例は、BEFORE UPDATE トリガで REFERENCING NEW と REFERENCING OLD を使用する方法を示します。この例は、従業員の給料に減給が生じないようにします。

CREATE TRIGGER TR_check_salary_decrease 
    BEFORE UPDATE
      ON Employees
    REFERENCING OLD AS before_update 
    NEW AS after_update 
FOR EACH ROW 
BEGIN
   IF after_update.salary < before_update.salary THEN
    RAISERROR 30002 'You cannot decrease a salary';
    END IF;
END;

次の例は、BEFORE UPDATE トリガで REFERENCING NEW と REFERENCING OLD を使用する方法を示します。この例でも、従業員の給料に減給が生じないようにしますが、このトリガは給料のカラムが更新されたときだけ起動するため、より効率的になっています。

CREATE TRIGGER TR_check_salary_decrease_column 
    BEFORE UPDATE OF Salary
      ON Employees
    REFERENCING OLD AS before_update 
    NEW AS after_update
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
   IF after_update.salary < before_update.salary THEN
    RAISERROR 30002 'You cannot decrease a salary';
End IF;
END;

次の例は、BEFORE INSERT トリガと UPDATE トリガで REFERENCING NEW を使用する方法を示します。この例では、SalesOrderItems テーブルのローで挿入や更新が行われる前に起動する、トリガを作成します。

CREATE TRIGGER TR_update_date 
   BEFORE INSERT, UPDATE
     ON SalesOrderItems
   REFERENCING NEW AS new_row
FOR EACH ROW 
BEGIN
   SET new_row.ShipDate = CURRENT TIMESTAMP;
END;