第124回　trigger

第121回ではevent_schedulerを使って定期実行する動作を確認しました。event_scheduler以外にも、テーブルで特定のイベントが発生したときに動作するtriggerというものがあります。特にMySQL 5.7で、それぞれのテーブルに対して複数のtriggerを設定できるようになり、より便利な使い方が検討できるようになりました。

今回はtriggerでデータが更新されるタイミングで動作することを確認していきます。なお、検証環境はCentOS 7で、MySQLのバージョンは8.0.20を利用しています。

triggerの作成

triggerを作成するにはCREATE TRIGGER構文を利用します。CREATE TRIGGER構文はCREATE TRIGGER <trigger名> <trigger_time> <trigger_event> ON <table名> FOR EACH ROW <triggerの中身>といった形式で記述します。

trigger_timeにはBEFOREまたはAFTERを記述し、triggerを実施するタイミングはデータの更新の前か後かを決定します。

trigger_eventはINSERT/UPDATE/DELETEのいずれかを記述し、triggerが実施される行の操作のタイプを記入します。

trigger_bodyには実際にtriggerが動作するときにどのように動作させるかを記述します。

trigger_bodyの中ではNEW.<カラム名>とOLD.<カラム名>を利用することで、更新または削除前後のデータを利用することができます。

実際に作成して動作を確認していきましょう。num(int)のようなt1テーブルに対して、INSERT後にt2にnumに入れた値の2倍を挿入するtriggerを作成してみます。

mysql> create table t1(num int);
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2(num int);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TRIGGER trg_t1_insert AFTER INSERT ON t1
    -> FOR EACH ROW INSERT INTO t2 SET num = NEW.num * 2;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t2;
+------+
| num  |
+------+
|    2 |
|    4 |
|    6 |
+------+
3 rows in set (0.00 sec)

実際にt2.numにt1.numの2倍の値が挿入されました。なお、作成したtiggerは、SHOW TRIGGERS構文またはINFORMATION_SCHEMAのTRIGGERSテーブルで確認できます。また、SHOW CREATE TRIGGER <trigger_name>で、triggerの定義を確認することができます。

triggerによるレプリケーションの動作

triggerを利用する際のレプリケーションの動作を確認してみましょう。

triggerを作成することで、レプリケーション先にもtriggerは作成されます。レプリケーション先でtriggerが動作するかどうかは、レプリケーションをROWベース、STATEMENTベースどちらでレプリケーションを実施しているかによります。

ROWベースのレプリケーションを実施している場合、レプリケーション先ではtriggerは実施されません。これはROWベースのレプリケーションの場合はtriggerによる更新もバイナリログにはきだされるため、2重で更新されるのを防ぐためです。

STATEMENTベースのレプリケーションでは、レプリケーション先でtriggerによる更新が実施されます。

より詳細な動作については、公式ドキュメントの17.5.1.35 Replication and Triggersにあるので、レプリケーション環境で利用する場合は一度読んでおくことをおすすめします。

triggerを利用するときの注意点

triggerはアプリケーションコードを記入しなくとも、関連するデータを更新することができますが、注意点もあります。

まず、triggerを利用するということは、1つのクエリの実行によりほかの1つ以上のクエリが実行されることです。これは単純にクエリの実行数が増えるため、QPSが跳ね上がる可能性があります。また、関連するデータを操作することによる、意図しない長時間のロック待ちやデッドロックが発生するかもしれません。

また、triggerの利用により、エラーが起きた場合にもとのクエリも失敗してしまうことがあります。これは、triggerを利用している認識がない場合にエラーの理由に気づきにくい恐れがあるかもしれません。さらに、triggerが張られていることを知らないで利用すると、意図しない更新が入ったと勘違いしてしまうかもしれません。

他にも、外部キーによるアクションに対してはtriggerは動作しない点も、考慮に入れておく必要があります。その他の注意点は、公式ドキュメント24.8 Restrictions on Stored ProgramsのRestrictions for Triggersをご確認ください。

まとめ

今回は簡単ではありますが、triggerについて紹介しました。triggerはデータの更新を駆動として動作するため、利用できそうなことがたくさんありそうですが、知らないと起こりうる問題もあるので十分に注意が必要です。

筆者としては、事前にサマリするとパフォーマンスが圧倒的によくなるときや、既存のアプリケーションをどうしても改修が難しい場合など、なにか大きなメリットがある場合や“⁠苦肉の策⁠”として利用することにおすすめします。