MySQL 5.7でやっと(?)1テーブル複数トリガーが仕掛けられるようになった

As of MySQL 5.7.2, it is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a trigger_order clause that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.

MySQL :: MySQL 5.7 Reference Manual :: 13.1.16 CREATE TRIGGER Syntax


試してみよう。

5.6のフツーの動作は

mysql56> CREATE TRIGGER before_insert_1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SET num = NEW.num, val = NEW.val;
Query OK, 0 rows affected (0.02 sec)

mysql56> INSERT INTO t1 VALUES (1, 'one');
Query OK, 1 row affected (0.03 sec)

mysql56> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql56> SELECT * FROM t2;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql56> CREATE TRIGGER before_insert_2 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

もちろんこう。


5.7.6だと

mysql57> CREATE TRIGGER before_insert_1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SET num = NEW.num, val = NEW.val;
Query OK, 0 rows affected (0.01 sec)

mysql57> INSERT INTO t1 VALUES (1, 'one');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t2;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql57> CREATE TRIGGER before_insert_2 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
Query OK, 0 rows affected (0.01 sec)

mysql57> INSERT INTO t1 VALUES (2, 'two');
Query OK, 1 row affected (0.04 sec)

mysql57> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)

mysql57> SELECT * FROM t2;
+-----+--------------------+
| num | val                |
+-----+--------------------+
|   1 | one                |
|   2 | 残念だったな       |
+-----+--------------------+
2 rows in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: before_insert_1
               Event: INSERT
               Table: t1
           Statement: INSERT INTO t2 SET num = NEW.num, val = NEW.val
              Timing: BEFORE
             Created: 2015-03-27 02:53:23.66
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: before_insert_2
               Event: INSERT
               Table: t1
           Statement: UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num
              Timing: BEFORE
             Created: 2015-03-27 02:53:47.85
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

当たり前だけど張れた。順番の制御は、FOR EACH ROWのあとにPRECEDESまたはFOLLOWSで既存のトリガーを指定する(省略時は最後のトリガーの更に次に追加されるのかな)

mysql57> DROP TRIGGER before_insert_2;
Query OK, 0 rows affected (0.01 sec)

mysql57> CREATE TRIGGER before_insert_3 BEFORE INSERT ON t1 FOR EACH ROW PRECEDES before_insert_1 UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t1 VALUES (3, 'three');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> SELECT * FROM t2;
+-----+--------------------+
| num | val                |
+-----+--------------------+
|   1 | one                |
|   2 | 残念だったな       |
|   3 | three              |
+-----+--------------------+
3 rows in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: before_insert_3
               Event: INSERT
               Table: t1
           Statement: UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num
              Timing: BEFORE
             Created: 2015-03-27 02:56:45.90
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: before_insert_1
               Event: INSERT
               Table: t1
           Statement: INSERT INTO t2 SET num = NEW.num, val = NEW.val
              Timing: BEFORE
             Created: 2015-03-27 02:53:23.66
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

UPDATE t2が先に実行されるのでt2にレコードがなくて空ぶって、そのあとINSERT INTO t2でコピーされる。
ちょっと便利になるかも知れない。