CREATE TRIGGER 文

トリガによりデータベースのイベントに応じて実行されるアクションの集合を、表に定義することができます。ここでデータベースのイベントとは削除、挿入、更新処理のことです。例えば表に削除へのトリガを定義した場合、そのトリガのアクションは表から行が削除されるときに実行されます。

制約と併せて、トリガによる波及的な削除や更新を定義することで、情報の整合性を保つことができます。トリガで実現できる機能はこのほかにも、警告の呈示や、他の表の更新、e-mailの送信やその他の処理など、多岐にわたります。

ひとつの表に定義可能なトリガの数には、それらのトリガが同じイベントへのトリガであるか否かにかかわらず、上限はありません。

スキーマの所有者であるユーザであれば、そのスキーマにトリガを作成することができます。所有していない表にトリガを定義するためには、その表にTRIGGER権限を持っていなければなりません。データベースの所有者であるユーザであれば、どのスキーマのどの表に対してもトリガを作成することができます。

トリガはトリガが定義されている表とは異なるスキーマに定義することも可能です。

限定されたトリガ名のスキーマ名は、SYSで始まってはなりません。

構文

CREATE TRIGGER トリガ名
{ AFTER | NO CASCADE BEFORE } 
{ INSERT | DELETE | UPDATE [ OF 列名 [, 列名]* ] }
ON 表名
[ 参照節 ]
[ FOR EACH { ROW | STATEMENT } ] [ MODE DB2SQL ] 
トリガされるSQL文

トリガの発火が前か後か

トリガは前のトリガあるいは後のトリガのいずれかとして定義できます。

前のトリガは文による変更が適用され制約による検証が行われる前に発火します。前のトリガは、行のトリガあるいは文のトリガのいずれにもなることができます。(詳細は文のトリガと行のトリガを参照してください。)
後のトリガは変更が適用され制約による検証が満たされた後に発火します。 後のトリガは、行のトリガあるいは文のトリガのいずれにもなることができます。(詳細は文のトリガと行のトリガを参照してください。)

挿入、削除、更新のいずれによりトリガは発火されるのか

トリガはどのように定義されたかに従って、次のいずれかのデータベースのイベントにより発火します。(上記の構文を参照してください。

INSERT
UPDATE
DELETE

表のイベントごとに定義できるトリガの数に上限はありません。さらに更新へのトリガであれば、対象となる列を明示することができます。

古い値を参照するのか、新しい値を参照するのか:参照節

発火の原因となったイベントで現在変更された情報をトリガされるSQL文から参照することは良くあることです。また、トリガされるSQL文が参照しなければならないのは新しい値(変更後、後の値、と言うこともできます)である場合があります。

Derbyにて、発火した原因となったイベントにより変更された情報をトリガから参照する方法はひとつだけではありません。変更された情報はトリガされるSQL文から、遷移変数あるいは遷移表で参照することができます。参照節にて遷移変数への相関名や別名を、OLD/NEW AS 相関名という書式で記述できます。

例えば、次のような節をトリガの定義に記述した場合。

REFERENCING OLD AS DELETEDROW

トリガされるSQL文でこの相関名を参照できます。

DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id

遷移変数はOLDかNEWのいずれかに関わらず、単一の行を持つjava.sql.ResultSetにマップされます。

Note: 遷移変数を使えるのは、行のトリガだけです。(文のトリガと行のトリガを参照してください。) また、挿入イベントの行のトリガではOLDの行を参照できません。削除イベントの行のトリガではNEWの行を参照できません。

文のトリガでは、遷移表をトリガされるSQL文やトリガの資格で参照可能です。参照節にて参照表への相関名や別名を、OLD_TABLE/NEW_TABLE AS 相関名という書式で記述できます。

例えば、

REFERENCING OLD_TABLE AS DeletedHotels

と書くことで識別子(DeletedHotels)をトリガされるSQL文で使うことができます。

DELETE FROM HotelAvailability WHERE hotel_id IN
    (SELECT hotel_id FROM DeletedHotels)

遷移表は古いか新しいかに関わらず、トリガが着火した原因のイベントで影響を受けた行の数と同じ基数をもつjava.sql.ResultSetにマップされます。

Note: 文のトリガ(文のトリガと行のトリガを参照してください。)でのみ遷移表は利用可能です。 INSERT文へのトリガでは、OLDの表は参照できません。DELETE文へのトリガでは、NEWの表は参照できません。

参照節では、新しいものと古いものにそれぞれ一つまで、相関名あるいは識別子を定義できます。また、行のトリガには遷移表の識別子を定義できない一方で、文のトリガには遷移変数の相関名を定義できません。

文のトリガと行のトリガ

トリガには、文のトリガと行のトリガがあります。 CREATE TRIGGER文のFOR EACH節にて、どちらのトリガであるか明示されなかった場合の既定は、文のトリガです。

文のトリガ
文のトリガはトリガのイベント毎に一回発火します。トリガの発火に挿入・更新・削除にて何行が変更されたかは関係ありません。
行のトリガ
行のトリガはトリガのイベントが影響する行毎に一回発火します。影響が一行にも及ばなかった場合、トリガは発火しません。

Note: 列の値を元の値と等しい値に変更した場合(例えば、UPDATE T SET C = Cという文を考えてください。)、トリガのイベント前と後で列の値に変化が無くても、行のトリガは発火します。

トリガされるSQL文

トリガで定義されるアクションのことを、トリガされるSQL文と呼びます。(上記、構文の最終行をを参照してください。) トリガされるSQL文には以下の制限があります。

動的な引数(?)があってはなりません。
トリガが定義されている表の作成や、変更、破棄を行ってはなりません。
トリガが定義されている表への索引の作成や破棄を行ってはなりません。
トリガが定義されている表へのトリガの作成や破棄を行ってはなりません。
トランザクションのコミットやロールバック、分離レベルの変更を行ってはなりません。
前のトリガのアクションにINSERT/UPDATE/DELETE文があってはなりません。
前のトリガのアクションで更新を行う手続きを呼び出してはなりません。

トリガされるSQL文ではトリガが定義された表以外のデータベースの情報を参照することができます。それらのデータベースの情報が破棄されていた場合、トリガは無効となります。次回の実行にてトリガの再コンパイルが失敗した場合、呼び出しは例外を投げて、トリガを発火させた文はロールバックされます。

トリガされるSQL文の詳細については、Derby ディベロッパーズガイドを参照してください。

実行の順序

トリガを発火させるデータベースのイベントが起きると、Derbyは処理を次の順で行います。

最初にNo Cascade Beforeなトリガのアクションを実行します。
次に、制約(主キー、一意キー、外部キー)の検証を行います。
続いて、更新、変更、削除などの処理を行います。
最後にAfterなトリガを発火します。

同じ表に対して、同じイベントの、発火する時(トリガが前か後かという意味です。)が同じトリガが複数定義されていた場合、トリガは作成された順に実行されます。

-- 文とトリガ

CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
  FOR EACH ROW MODE DB2SQL
  values app.notifyEmail('Jerry', 'Table x is about to be updated'); 

CREATE TRIGGER FLIGHTSDELETE
  AFTER DELETE ON FLIGHTS
  REFERENCING OLD_TABLE AS DELETEDFLIGHTS
  FOR EACH STATEMENT 
  DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
  (SELECT FLIGHT_ID FROM DELETEDFLIGHTS);

CREATE TRIGGER FLIGHTSDELETE3
  AFTER DELETE ON FLIGHTS
  REFERENCING OLD AS OLD
  FOR EACH ROW 
  DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;

Note: Derby ディベロッパーズガイドにはより多くの例が書かれています。

トリガの再帰

トリガの再帰は最大16までです。