第18章 トリガ

目次

18.1. CREATE TRIGGER 構文
18.2. DROP TRIGGER 構文
18.3. トリガの使用

トリガは名称を持つ、テーブルに付属するデータベース オブジェクトで、テーブルに特定イベントが発生すると有効化されます。例えば、次のステートメントを入力するとテーブル並びに INSERT トリガが生成されます。トリガはテーブル カラムの1つに挿入された値を合計します。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

この章で、トリガを生成させ、除去する構文について説明し、それを使う方法に対して、幾つかの例を紹介します。トリガの使用に対する制限については、項D.1. 「ストアド ルーチンとトリガの規制」 で説明されています。項17.4. 「ストアドルーチンとトリガのバイナリログ」 でトリガに適用されるバイナリ ロギングについて補足説明しています。

MySQL 5.1 中のトリガに関する幾つかの主な質問の答えについては 項A.5. 「MySQL 5.1 FAQ ? Triggers」 をご覧ください。

18.1. CREATE TRIGGER 構文

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

このステートメントによって、新しいトリガが生成されます。トリガは名称を持つ、テーブルに付属するデータベース オブジェクトで、テーブルに対して特定イベントが発生すると有効化されます。現在、CREATE TRIGGER には、そのトリガに添付したテーブルに対する TRIGGER 権限が必要です。(このステートメントは MySQL 5.1.6.より前のバージョンに SUPER 権限を求めるものです。)

トリガは、tbl_name なる名称を持つテーブルと連携するようになり、これによって、トリガはパーマネント テーブルを参照しなければならなくなります。トリガを TEMPORARY テーブルあるいはビューと連携させる事はできません。

トリガが有効化されると、このセクションで後に述べるように DEFINER 条項によって、適用すべき権限が規定されます。

trigger_time はトリガのアクション タイムです。それを有効化するステートメントの前か後にトリガが有効化される事を示す BEFORE または AFTER となる事ができます。

trigger_event はトリガを有効化するステートメントの種類を示します。trigger_event では、以下の中から1つを選ぶ事ができます:

  • INSERT:トリガは、新しい行がテーブルに挿入されると必ず、例えば INSERTLOAD DATA 並びに REPLACE の各ステートメントを通して有効化されます。

  • UPDATE:トリガは、行が修正されると必ず、例えば UPDATE ステートメントを通して有効化されます。

  • DELETE:トリガは、新しい行がテーブルから削除されると必ず、例えば DELETE ステートメント並びに REPLACE ステートメントを通して有効化されます。しかし、テーブル上の DROP TABLE ステートメントおよび TRUNCATE ステートメントは DELETE を使用していないので、このトリガを有効化 しません。パーティションをドロップすると、DELETE トリガも有効化されません。詳しくは 項12.2.9. 「TRUNCATE 構文」 を参照してください。

trigger_event は、それがテーブル 操作のタイプを表している場合には、トリガを有効化する SQL ステートメントのリテラル タイプを表さないと理解する事が重要です。例えば、ステートメントは両方共行をテーブルに挿入するので、INSERTトリガは、INSERT ステートメントのみならず、LOAD DATA ステートメントによっても有効化されます。

この潜在的に紛らわしい例は、INSERT INTO ... ON DUPLICATE KEY UPDATE ... という構文です: BEFORE INSERT トリガは、全ての行に対して有効化され、その後、行に対して重複キーがあるか否かによって、AFTER INSERT トリガもしくは BEFORE UPDATE トリガと AFTER UPDATE トリガの両方が有効化されます。

同じトリガ アクション タイムとトリガ イベントを持つテーブルに対して、2つのトリガは存在する事はできません。例えば、1個のテーブルに対して2つの BEFORE UPDATE トリガを持つ事はできません。しかし、BEFORE UPDATE トリガと BEFORE INSERT トリガもしくは BEFORE UPDATE トリガと AFTER UPDATE トリガを持つ事ができます。

trigger_stmt はトリガを有効化する時に実行させるステートメントです。複数の命令を実行させたい場合には、BEGIN ... END 複合文コンストラクトを使ってください。これは、ストアド ルーチン内で許されているのと同じステートメントも使う事ができるようにします。項17.2.5. 「BEGIN ... END 複合ステートメント構文」 を参照してください。トリガ中に同じステートメントを使用する事は許されません。(項D.1. 「ストアド ルーチンとトリガの規制」 を参照)。

MySQL は sql_mode システム中に、トリガが生成された時に有効であった変数設定を記憶し、現サーバの SQL モードに関係なく、この設定でトリガを実際に実行します。

:トリガは現在、転送された外部キー アクションによって有効化されません。この制限はできるだけ早く撤廃されるでしょう。

MySQL 5.1 で、この例に示す testref という名前のトリガのような、直接の参照を含むトリガを名称別テーブルに書き込む事ができます:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

DELIMITER ;

INSERT INTO test3 (a3) VALUES 
  (NULL), (NULL), (NULL), (NULL), (NULL), 
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES 
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

以下の値をテーブル test1 に、ここに示すように挿入すると仮定します:

mysql> INSERT INTO test1 VALUES 
    -> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

結果として、4つのテーブル中のデータは以下の通りになります:

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

エイリアス OLD とエイリアス NEW を使う事によって、問題のテーブル(トリガに添付されたテーブル)中のカラムを参照する事ができます。OLD.col_name は既存の行のカラムが更新または削除される前にチェックします。NEW.col_name は挿入すべき新しい行あるいは更新された既存の行のカラムを参照します。

DEFINER 条項は、トリガの有効化においてアクセス権をチェックする時に使用すべき MySQL アカウントを指定します。user 値を附与する場合、それを 'user_name'@'host_name' フォーマット(GRANT ステートメントに使用したのと同じフォーマット)の中にある MySQL アカウントとするべきです。user_name の値と host_name の値が両方共必要です。CURRENT_USERCURRENT_USER() として附与する事もできます。DEFINER のデフォルト値は CREATE TRIGGER ステートメントを実行するユーザです。(これは DEFINER = CURRENT_USER と同じです。)

DEFINER 条項を特定する場合、SUPER 権限を保持していない限り、自分の値を除くいかなるアカウントにも値をセットする事はできません。これらの規則は有効な DEFINER ユーザ値を決定します:

  • SUPER 権限を持っていない場合、文字によるか、CURRENT_USER を使って規定されている user 値だけが有効なユーザ アカウントとなります。デファイナを他のアカウントに設定する事はできません。

  • SUPER 権限を持っている場合、構文的に有効なアカウント ネームを規定する事ができます。そのアカウントが実在しない場合、警告が生成されます。

    実在しない DEFINER 値を使ってトリガを生成させる事は可能ですが、デファイナが実在するようになるまで、このようなトリガを有効化しておく事は決して良いアイデアではありません。さもなければ、権限チェックに関する挙動パターンは定義されません。

注意:MySQL 5.1.6以前のバージョンでは、MySQL は CREATE TRIGGER を使用する為に SUPER権限を要求するので、前の規則の2番目の規定だけが適用されます。5.1.6以降のバージョンからは、CREATE TRIGGERTRIGGER 権限を要求し、SUPER 権限は、DEFINERに自身のアカウント値以外の値をセットする事を可能にする為だけに要求されます。

MySQL は、このようなトリガ権限をチェックします:

  • CREATE TRIGGER 時に、ステートメントを発行するユーザは TRIGGER 権限を持つ必要があります。((MySQL 5.1.6.前の SUPER)

  • トリガを有効化する時、権限が DEFINER ユーザの物であるかチェックされます。このユーザは、これらの権限を持っていなければなりません:

    • TRIGGER 権限。((MySQL 5.1.6.前の SUPER)

    • トリガの定義の中にある OLD.col_name あるいは NEW.col_name を経由して照合が行われる場合に得られる問題のテーブルに対する SELECT 権限。

    • テーブル カラムが、SET NEW.col_name = value トリガの定義中の値の割り当てターゲットである場合に得られる問題のテーブルに対する UPDATE 権限。

    • トリガによってステートメントが実行される為に一般に要求されるその他一切の権限。

18.2. DROP TRIGGER 構文

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

このステートメントはトリガをドロップさせます。そのスキーマ(データベース) には、名称を自由につけることができます。スキーマを撤去すると、トリガは初期スキーマから除去されます。MySQL 5.0.2の中に DROP TRIGGER が追加されました。それを使用するには、トリガに関連したテーブルに対して TRIGGER 権限が必要となります。(このステートメントは MySQL 5.1.6.より前のバージョンで SUPER 権限を求めるものです。)

IF EXISTSを使用して、存在していないトリガに対してエラーが発生するのを防止してください。IF EXISTS を使用すると、実在していないトリガに対して NOTE が生成されます。項12.5.4.31. 「SHOW WARNINGS 構文」 を参照してください。IF EXISTS 条項が MySQL 5.1.14の中に追加されました。

:全ての MySQL ? MySQL 5.1 を含め ? 5.0.10以前の古いバージョンの MySQL を、5.0.10以後の新しいバージョンの物に更新する時、更新する前に 一切のトリガをドロップし、その後で再生させなければなりません。これを怠ると、更新後 DROP TRIGGER が作動しなくなります。更新手順に対するヒントについては、項2.11.1. 「MySQL 5.0 から 5.1 へのアップグレード」 をご参照ください。

18.3. トリガの使用

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

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

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

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

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

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

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 なる名称のトリガを生成させます。それには、トリガに対する有効化の時期、トリガ イベント並びにトリガの有効化に要するその他を規定する条項も含まれています。

  • キーワード BEFORE はトリガ アクションの時期を示します。この場合、トリガを各行がテーブルに挿入される前に有効化すべきです。ここで許容されるその他のキーワードは AFTER です。

  • キーワード INSERT はトリガを有効化するイベントを示します。例では、INSERT ステートメントによってトリガの有効化が引き起こされます。DELETE ステートメントおよび UPDATE ステートメントに対してトリガを生成させる事ができます。

  • FOR EACH ROW の後に連なるステートメントは、トリガを有効化するたびに実行すべきステートメントを規定します。これは、トリガに対するステートメントによって影響を被る各行毎に一回発生します。この例では、トリガ ステートメントは、amount 欄に挿入された値を集計する簡単な SET です。そのステートメントは、「新しい行に挿入される amount カラムの値」 を意味する NEW.amount として参照されます。

トリガを利用する為には、 加算器変数をゼロにセットし、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;

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

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

OLD なるキーワードと NEW なるキーワードを使用すると、トリガによって影響を被る行中のカラムにアクセスする事が可能となります。 (OLDNEW は大文字でも小文字でも入力する事ができます。)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となります。

OLDNEW はトリガに対する 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 ステートメントを使ってそれを呼び出して、簡素化する事ができます。これは、幾つかのトリガから同じルーチンを呼び出したい場合にも有益です。

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

  • トリガは、データをクライアントに戻すかダイナミック SQL を使用する、ストアド プロシージャを呼び出す CALL ステートメントを使用する事ができません。(ストアド プロシージャは、OUT または INOUT パラメータを通してトリガにデータを返す事を許します。)

  • トリガは、START TRANSACTIONCOMMITROLLBACK のようなトランザクションを明示的にもしくは暗黙に開始または終了させるステートメントを使用する事ができません。

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

  • BEFOREトリガの機能が停止した場合、対応する行の操作が実施されなくなる。

  • BEFORE トリガが、行を挿入するか改訂する attempt によって、当該試みがその後成功するか否かに関係なく有効化される。

  • AFTER トリガが、BEFORE トリガ(存在している場合) 並びに行操作が両方共うまく実行された場合に限り実行される。

  • BEFORE トリガあるいは AFTER トリガを実行している最中に発生したエラーが、トリガに狂いを引き起こし、これによって、全ステートメントを機能停止にする。

  • トランザクション テーブルに対するステートメントの機能停止は、そのステートメントによって実施された一切の変更のロールバックを引き起こすべきです。トリガの機能停止は、ステートメントに機能停止をもたらし、これによって、トリガの機能停止がロールバックを引き起こす。非トランザクション テーブルに対してこのようなロールバックを行う事ができないので、ステートメントが機能停止しても、エラーが発生した時点より前に実施された一切の変更は有効なまま維持されます。

アダルトレンタルサーバー