トリガーを作成したり、トリガーを削除したりするには、セクション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)