20.3.1 トリガーの構文と例

トリガーを作成したり、トリガーを削除したりするには、セクション13.1.19「CREATE TRIGGER 構文」およびセクション13.1.30「DROP TRIGGER 構文」で説明しているように、CREATE TRIGGER または DROP TRIGGER ステートメントを使用します。

次に、INSERT 操作に対してアクティブ化するトリガーをテーブルに関連付ける簡単な例を示します。このトリガーは加算器として機能し、テーブルのいずれかのカラムに挿入された値を合計します。

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)

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 つの BEFORE UPDATE トリガーを定義することはできません。これに対処するために、FOR EACH ROW のあとで BEGIN ... END 複合ステートメント構造構文を使用することにより、複数のステートメントを実行するトリガーを定義できます。(例はこのセクションであとから示します。)

トリガー本体内で、OLD および NEW キーワードを使用すると、トリガーの影響を受ける行のカラムにアクセスできます。OLD および NEW はトリガーに対する MySQL の拡張です。これらは大文字と小文字を区別しません。

INSERT トリガー内では、NEW.col_name だけを使用できます。古い行はありません。DELETE トリガーでは、OLD.col_name だけを使用できます。新しい行はありません。UPDATE トリガーでは、OLD.col_name を使用して、更新される前の行のカラムを参照でき、NEW.col_name を使用して、更新されたあとの行のカラムを参照できます。

OLD で指名されたカラムは読み取り専用です。(それに対する SELECT 権限がある場合) 参照はできますが、変更はできません。NEW で指名されたカラムは、それに対する SELECT 権限がある場合に参照できます。BEFORE トリガーでは、それに対する UPDATE 権限がある場合、SET NEW.col_name = value でその値を変更することもできます。これは、トリガーを使用して、新しい行に挿入する値または行の更新に使用される値を変更できることを意味します。(このような SET ステートメントは、行の変更はすでに行われているため、AFTER トリガーでは効果がありません。)

BEFORE トリガーでは、AUTO_INCREMENT カラムの NEW 値は 0 であり、新しい行が実際に挿入されるときに自動的に生成されるシーケンス番号ではありません。

BEGIN ... END 構造構文を使用することにより、複数のステートメントを実行するトリガーを定義できます。BEGIN ブロック内では、条件文やループなど、ストアドルーチン内で許可されたほかの構文を使用することもできます。ただし、ストアドルーチンの場合と同様に、mysql プログラムを使用して、複数のステートメントを実行するトリガーを定義する場合、トリガー定義内で ; ステートメント区切り文字を使用できるように、mysql ステートメント区切り文字を再定義する必要があります。次の例はこれらの要点を示しています。ここでは、各行の更新に使用する新しい値をチェックし、0 から 100 の範囲に収まるように値を変更する UPDATE トリガーを定義しています。行の更新に使用される前に値をチェックする必要があるので、これは 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 ステートメントを使用してトリガーから呼び出したほうが簡単になる場合があります。これは、複数のトリガー内から同じコードを実行する場合にも便利です。

アクティブ化したときにトリガーが実行するステートメントに表示できる対象には制限があります。

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

• トリガーは、START TRANSACTION、COMMIT、ROLLBACK など、トランザクションを明示的または暗黙的に開始したり終了したりするステートメントを使用できません。

セクションD.1「ストアドプログラムの制約」も参照してください。

MySQL は次のようにトリガー実行中にエラーを処理します。

• BEFORE トリガーが失敗した場合、対応する行に対する操作は実行されません。

• BEFORE トリガーは、行を挿入または変更しようとする試行 によってアクティブ化され、その試行がその後成功するかどうかには関係ありません。

• AFTER トリガーは、すべての BEFORE トリガーと行操作の実行が成功した場合にのみ実行されます。

• BEFORE または AFTER トリガーのどちらかの実行中にエラーが発生すると、トリガーの呼び出しを起こしたステートメント全体が失敗します。

• トランザクションテーブルの場合、ステートメントの失敗により、ステートメントが実行したすべての変更がロールバックされます。トリガーの失敗はステートメントの失敗を招くので、トリガーの失敗はロールバックも引き起こします。非トランザクションテーブルの場合、このようなロールバックは行えないので、ステートメントが失敗しても、エラーの時点以前に実行されたすべて変更は有効なままです。

次の例に示す 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)