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 ( column-name )
| UPDATING [ ( column-name-string ) ]

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

OR REPLACE 句 OR REPLACE を指定すると、新しいトリガが作成されるか、同じ名前の既存のトリガが置き換えられます。
trigger-event トリガは次のイベントによって起動できます。DELETE、INSERT、または UPDATE イベントに複数のトリガを定義するか、UPDATE OF column-list イベントに 1 つのトリガを定義できます。
- DELETE 句 関連するテーブルのローが削除されると、呼び出されます。
- INSERT 句 このトリガに関連するテーブルに新しいローが挿入されると、呼び出されます。
- UPDATE 句 関連するテーブルのローが更新されると、呼び出されます。
  UPDATE 句を指定する場合は、REFERENCING 句も指定して構文エラーを回避する必要があります。
- UPDATE OF column-list 句 関連するテーブルのローが更新され、column-list のカラムが修正されると、呼び出されます。このタイプのトリガイベントは、trigger-event-list では使用できません。このトリガ用に定義されたトリガイベントであることが必要です。この句は、INSTEAD OF トリガでは使用できません。
  処理が必要なイベントごとにトリガを個別に作成できます。または、共有するアクションや、イベントに応じたアクションが複数ある場合は、すべてのイベントに対して 1 つのトリガを作成し、IF 文を使用して実行するアクションを区別できます。
- UPDATING 句 UPDATING の引数は、引用符付きの文字列です (たとえば、UPDATING( 'mycolumn' ))。UPDATE の引数は、識別子です (たとえば、UPDATE( mycolumn ))。この 2 つのバージョンは相互運用可能で、他のベンダが提供する DBMS の SQL ダイアレクトとの互換性のために用意されています。
  UPDATING 句を指定する場合は、REFERENCING 句も指定して構文エラーを回避する必要があります。
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 句は使用できません。

RESOLVE トリガ型は、SQL Remote とともに使用します。これは、ローレベルの UPDATE または UPDATE OF column-list の前にのみ起動されます。
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 句は、ローの古い値と新しい値を保持している宣言されたテンポラリテーブルを参照します。

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

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

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

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

CREATE TRIGGER はテーブルにテーブルロックを設定して、テーブルを排他的に使用します。

CREATE ANY TRIGGER または CREATE ANY OBJECT のシステム権限が必要です。さらに、そのトリガが作成されるテーブルの所有者であるか、または次のいずれかの権限を持っていることも必要です。

そのテーブルに対する ALTER 権限
ALTER ANY TABLE システム権限
ALTER ANY OBJECT システム権限

別のユーザが所有するビューのトリガを作成するには、ALTER ANY TRIGGER または ALTER ANY OBJECT システム権限のどちらかを持っていて、ALTER ANY VIEW または ALTER ANY OBJECT システム権限のどちらかを持っている必要があります。

オートコミット。

SQL/2008 CREATE TRIGGER は、SQL/2008 標準のオプションの SQL 言語機能 T211、"Basic trigger capability" の一部です。ROW トリガはオプションの SQL 言語機能 T212 であり、INSTEAD OF トリガはオプションの SQL 言語機能 T213 です。
SQL Anywhere トリガの一部の機能は、ベンダー拡張です。これらのコードを以下に示します。
- オプションの OR REPLACE 構文。既存のトリガを置き換える場合、新しいトリガインスタンスの作成の認証は省略されます。
- ORDER 句。SQL/2008 では、トリガは作成順に起動されます。
- RESOLVE トリガはベンダー拡張です。
Transact-SQL ROW トリガと RESOLVE トリガは、Adaptive Server Enterprise でサポートされていません。Transact-SQL の INSTEAD OF トリガは、Adaptive Server Enterprise ではサポートされていますが、SQL Anywhere の Transact-SQL ダイアレクトではサポートされていません。Transact-SQL トリガは、異なる構文で定義されます。

この例は、文レベルのトリガを作成します。最初に、この CREATE TABLE 文に示されているようにテーブルを作成します (CREATE TABLE システム権限が必要)。

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 テーブルの郵便番号が大文字になるようにしています。この文を実行するには、GROUPO.Employees に対する SELECT、ALTER、UPDATE 権限が必要です。

CREATE TRIGGER emp_upper_postal_code
BEFORE UPDATE OF PostalCode
ON GROUPO.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 GROUPO.Employees SET state='ON', PostalCode='n2x 4y7' WHERE EmployeeID=191;
SELECT PostalCode FROM GROUPO.Employees WHERE EmployeeID = 191;

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

CREATE TRIGGER TR_check_delete_employee 
BEFORE DELETE
ON Employees
REFERENCING OLD AS current_employee
FOR EACH ROW WHEN ( current_employee.Terminate IS NULL )
BEGIN
    RAISERROR 30001 'You cannot delete an employee who has not been fired';
END;

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

CREATE TRIGGER TR_check_salary_decrease 
    BEFORE UPDATE
      ON GROUPO.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 INSERT トリガと UPDATE トリガで REFERENCING NEW を使用する方法を示します。この例では、SalesOrderItems テーブルのローで挿入や更新が行われる前に起動するトリガを作成します。

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

次のトリガでは、トリガが起動されるきっかけとなったアクションを示すメッセージが、Interactive SQL の [結果] ウィンドウ枠の [メッセージ] タブに表示されます。

CREATE TRIGGER tr BEFORE INSERT, UPDATE, DELETE
ON sample_table
REFERENCING OLD AS t1old
FOR EACH ROW
BEGIN
    DECLARE msg varchar(255);
    SET msg = 'This trigger was fired by an ';
    IF INSERTING THEN
        SET msg = msg || 'insert'
    ELSEIF DELETING THEN
        set msg = msg || 'delete'
    ELSEIF UPDATING THEN
        set msg = msg || 'update'
    END IF;
    MESSAGE msg TO CLIENT
END;

CREATE TRIGGER 文

構文

パラメータ

備考

権限

関連する動作

参照

標準と互換性

例