トリガの使用

このセクションで、MySQL 5.1 の中でトリガを使用する方法並びにこれらの使用に対する幾つかの制限について説明します。トリガに対する制約に関する追加情報を、 項D.1. 「ストアド ルーチンとトリガの規制」 に掲載します。

トリガは名称を持つ、テーブルに付属するデータベース オブジェクトで、テーブルに対して特定イベントが発生すると有効化されます。幾つかは、トリガに対して、テーブルに挿入すべき値を対象にチェックを実施するか、更新に含まれる値に関して、計算を実行するのに使用されます。

INSERT ステートメント、DELETE ステートメントもしくは UPDATE ステートメントがテーブルに対して実行されると、トリガがテーブルに関連付けられ、定義されて有効化されます。トリガはそのステートメントの前か後にセットして有効化する事ができます。例えば、トリガを、各行をテーブルから削除する前または各行を更新した後に有効化させる事ができます。

CREATE TRIGGER ステートメントまたは DROP TRIGGER ステートメントを使ってトリガを生成させるか除去してください。これらのステートメントに対する構文は、項18.1. 「CREATE TRIGGER 構文」 および 項18.2. 「DROP TRIGGER 構文」 で説明されています。

INSERT ステートメントの為のテーブルをトリガに関連付けた例がここにあります。それはそのテーブルのカラムの１つに挿入された値を合計する加算器の役を果たします｡

以下のステートメントによって、テーブルとそれに対するトリガが生成されます:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

CREATE TRIGGER ステートメントは、account と関連している ins_sum なる名称のトリガを生成させます。それには、トリガに対する有効化の時期、トリガ イベント並びにトリガの有効化に要するその他を規定する条項も含まれています。

トリガを利用する為には、 加算器変数をゼロにセットし、INSERTステートメントを実行し、その後変数がどんな値になったかを調べます：

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48               |
+-----------------------+

この場合、INSERT ステートメントが実行された後の @sum の値は 14.98 + 1937.50 - 100 あるいは 1852.48 です。

トリガを破壊する為には、DROP TRIGGER ステートメントを利用してください｡そのトリガが初期設定スキーマに含まれていない場合には、スキーマ名を規定しなければなりません。

mysql> DROP TRIGGER test.ins_sum;

スキーマの名称欄にトリガ名が存在していますが、これは全てのトリガはスキーマの中に固有の名称を持っていなければならない事を意味します。異なるスキーマの中にあるトリガには同じ名称を付ける事ができます。

スキーマ毎にトリガ名を固有な物としなければいけないという要件に加え、生成させる事ができるトリガのタイプに他の制限があります。特に、有効化の時期と有効化イベントが同じ１つのテーブルに対して、2個のトリガを持つ事はできません。例えば、１つのテーブルに対して、２つの BEFORE INSERT トリガもしくは２つの AFTER UPDATE トリガを定義する事はできません｡ (このセクションの後の部分に述べる) BEGIN ... END 合成ステートメントの構築を FOR EACH ROW の後に使う事によって、複数のステートメントを実行するトリガを定義する事ができるので、これを重要な制限とみなすべきではありません。

OLD なるキーワードと NEW なるキーワードを使用すると、トリガによって影響を被る行中のカラムにアクセスする事が可能となります。 (OLD と NEW は大文字でも小文字でも入力する事ができます。)INSERTトリガの場合、NEW.col_name だけが使用可能です。古い行は存在しません。DELETEトリガの場合、OLD.col_name だけが使用可能です。新しい行は存在しません。UPDATE トリガの場合、OLD.col_name を使って更新前の行のカラムを、また NEW.col_name を使用して、アップデート後の行のカラムをそれぞれ参照する事ができます。

OLD の名称を持つカラムは読み取り専用です。(SELECT権限をお持ちである場合)、それを調べる事ができますが、改訂する事はできません。SELECT 権限をお持ちの場合、NEW の名称を持つカラムを調べる事ができます。BEFORE トリガでは、UPDATE 権限をお持ちの場合、SET NEW.col_name = value を使ってその値を変更する事ができます。これは、トリガを使って、新しい行に挿入される、または行を更新する為に利用される値を変更する事ができるという事を意味します。

BEFORE トリガでは、AUTO_INCREMENT カラムに対する NEW 値は、新しい記録が実際に挿入される時に自動的に生成されるシーケンス番号ではなく、0となります。

OLD と NEW はトリガに対する MySQL 拡張子です。

BEGIN ... END 構築を使用する事によって、複数のステートメントを実行するトリガを定義する事ができます。BEGIN ブロックの中で、条件文やループのようなストアド ルーチンの中で許容されているその他の構文を使用する事もできます。しかし、ストアド ルーチンだけに対して、複数のステートメントを実行する1個のトリガを定義する mysql プログラムを使用した場合、 mysql ステートメント デリミタを再定義して、トリガの定義の中で ; ステートメント デリミタを使用可能にする必要があります。 次の例はこれらの点を例示しています。それは、各行を更新するのに使用すべき新しい値をチェックする UPDATE トリガを定義し、その値を0から100までの範囲に収まるように変更します。その値は、行を更新するのに使用する前にチェックする必要があるので、BEFOREトリガでなければなりません。

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

ストアド プロシージャを別々に定義して、次に単純な CALL ステートメントを使ってそれを呼び出して、簡素化する事ができます。これは、幾つかのトリガから同じルーチンを呼び出したい場合にも有益です。

有効化する時トリガが実行するステートメントに記載する事ができる物に対して、幾つかの制限があります。

MySQL はトリガを実行している最中に発生したエラーを以下の通りに処理します：