【MySQL】トリガーの基本操作
MySQLのトリガーの基本操作を解説します。
トリガー
トリガーは対象テーブルを操作した時に連動して起動する処理です。
MySQLはデータの追加（INSERT）、更新（UPDATE）、削除（DELETE）の操作を行った時のトリガーを設定できます。
作成（CREATE TRIGGER）
トリガーの作成はCREATE TRIGGERを使います。
基本構文
CREATE TRIGGER トリガー名 [ AFTER | BEFORE ] [ INSERT | UPDATE | DELETE ]
ON テーブル名 FOR EACH ROW
クエリ[ AFTER | BEFORE ]はトリガーの起動タイミングです。
操作処理の前はBEFORE、後はAFTERを記述します。
[ INSERT | UPDATE | DELETE ]は操作の種類です。
データ操作の種類と同様に追加はINSERT、更新はUPDATE、削除はDELETEを記述します。
また、FOR EACH ROWはトリガーの起動単位です。
FOR EACH ROWはレコード単位でトリガーを起動します。
サンプル
初めにトリガーを作成します。
mysql> DESC items;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> DESC items_log;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| content | varchar(100) | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> CREATE TRIGGER item_insert_before_log BEFORE INSERT
-> ON items FOR EACH ROW
-> INSERT INTO items_log ( content, dt )
-> VALUE ( 'INSERT-BEFORE' , NOW() );
Query OK, 0 rows affected (0.01 sec)item_insert_before_logトリガーはitemsテーブルのデータ追加時にitems_logテーブルにデータを追加します。
mysql> INSERT INTO items ( name ) VALUE ( 'Apple' );
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM items;
+----+-------+
| id | name |
+----+-------+
| 1 | Apple |
+----+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM items_log;
+----+---------------+---------------------+
| id | content | dt |
+----+---------------+---------------------+
| 1 | INSERT-BEFORE | 2024-02-13 20:12:29 |
+----+---------------+---------------------+
1 row in set (0.00 sec)カラムデータの参照（NEW/OLD）
トリガーの処理でカラムのデータを参照するにはNEWまたはOLDを使います。
基本構文
NEW.カラム名OLD.カラム名INSERTではNEWで値を参照します。
UPDATEでは更新前のデータはOLD、更新後のデータをNEWはです。
DELETEではOLDで値を参照できます。
サンプル
mysql> DESC items;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> DESC items_log;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| content | varchar(100) | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> CREATE TRIGGER item_insert_before_log BEFORE INSERT
-> ON items FOR EACH ROW
-> INSERT INTO items_log ( content, dt )
-> VALUE ( CONCAT('INSERT-BEFORE: ', NEW.name) , NOW() );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO items ( name ) VALUE ( 'Apple' );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM items;
+----+-------+
| id | name |
+----+-------+
| 1 | Apple |
+----+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM items_log;
+----+----------------------+---------------------+
| id | content | dt |
+----+----------------------+---------------------+
| 1 | INSERT-BEFORE: Apple | 2024-02-13 20:19:07 |
+----+----------------------+---------------------+
1 row in set (0.00 sec)複数クエリ（BEGIN END）
トリガーの処理（クエリ）を複数設定する場合はBEGIN ENDとdelimiterを設定します。
基本構文
delimiter //
CREATE TRIGGER トリガー名 [ AFTER | BEFORE ] [ INSERT | UPDATE | DELETE ]
ON テーブル名 FOR EACH ROW
BEGIN
クエリ1;
クエリ2;
...
END;
//
delimiter ;複数のクエリをBEGINとENDで括ります。
また、複数クエリのため、途中でセミコロン（;）を使用することが想定されます。
デフォルトではセミコロン（;）は終端文字として扱われるため、その時点でクエリを読み込んでエラーとなってしまいます。
そのため、delimiter //で一時的に終端文字を//に変更し、トリガーを設定しています。
最後にdelimiter ;を記述することで終端文字をセミコロンに戻しています。
サンプル
mysql> DESC items;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> DESC items_log;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| content | varchar(100) | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> delimiter //
mysql> CREATE TRIGGER item_insert_after_log AFTER INSERT
-> ON items FOR EACH ROW
-> BEGIN
-> INSERT INTO items_log ( content, dt )
-> VALUE ( CONCAT('INSERT-AFTER [id] ', NEW.id) , NOW() );
-> INSERT INTO items_log ( content, dt )
-> VALUE ( CONCAT('INSERT-AFTER [name] ', NEW.name) , NOW() );
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> INSERT INTO items ( name ) VALUE ( 'Apple' );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM items;
+----+-------+
| id | name |
+----+-------+
| 1 | Apple |
+----+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM items_log;
+----+---------------------------+---------------------+
| id | content | dt |
+----+---------------------------+---------------------+
| 1 | INSERT-AFTER [id] 1 | 2024-02-13 20:29:26 |
| 2 | INSERT-AFTER [name] Apple | 2024-02-13 20:29:26 |
+----+---------------------------+---------------------+
2 rows in set (0.00 sec)一覧表示（SHOW TRIGGERS）
トリガーの一覧表示はSHOW TRIGGERSを使います。
基本構文
SHOW TRIGGERSサンプル
mysql> SHOW TRIGGERS;
+-----------------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-----------------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| item_insert_after_log | INSERT | items | BEGIN
INSERT INTO items_log ( content, dt )
VALUE ( CONCAT('INSERT-AFTER [id] ', NEW.id) , NOW() );
INSERT INTO items_log ( content, dt )
VALUE ( CONCAT('INSERT-AFTER [name] ', NEW.name) , NOW() );
END | AFTER | 2024-02-13 20:29:15.84 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+-----------------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)情報表示（SHOW CREATE TRIGGER）
トリガーの情報表示はSHOW CREATE TRIGGERを使います。
基本構文
SHOW CREATE TRIGGER トリガー名サンプル
mysql> SHOW CREATE TRIGGER item_insert_after_log;
+-----------------------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
| Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation | Created |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
| item_insert_after_log | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` TRIGGER `item_insert_after_log` AFTER INSERT ON `items` FOR EACH ROW BEGIN
INSERT INTO items_log ( content, dt )
VALUE ( CONCAT('INSERT-AFTER [id] ', NEW.id) , NOW() );
INSERT INTO items_log ( content, dt )
VALUE ( CONCAT('INSERT-AFTER [name] ', NEW.name) , NOW() );
END | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | 2024-02-13 20:29:15.84 |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
1 row in set (0.01 sec)削除（DROP TRIGGER）
トリガーの削除はDROP TRIGGERを使います。
基本構文
DROP TRIGGER トリガー名サンプル
mysql> SHOW TRIGGERS;
+-----------------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-----------------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| item_insert_after_log | INSERT | items | BEGIN
INSERT INTO items_log ( content, dt )
VALUE ( CONCAT('INSERT-AFTER [id] ', NEW.id) , NOW() );
INSERT INTO items_log ( content, dt )
VALUE ( CONCAT('INSERT-AFTER [name] ', NEW.name) , NOW() );
END | AFTER | 2024-02-13 20:29:15.84 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+-----------------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
mysql> DROP TRIGGER item_insert_after_log;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TRIGGERS;
Empty set (0.01 sec)
