乱射注意！！！MySQL TRIGGER（トリガー）は結構使える！！

使いみちによっては、すごく便利なトリガーについて説明します。使いみちと書いたのは、なんでもかんでもトリガーにもりもりしちゃうと対象テーブルのパフォーマンスに影響出ちゃうよという乱射状態になってしまいますので注意って感じです

トリガーとは

Trigger（トリガー）とは、対象テーブルの変更（INSERT,UPDATE,DELETE）イベントによってあらかじめ設定した処理を自動で実行する機能ですね。ようは、Aテーブルに対して、INSERT / UPDATE / DELETE の引き金（トリガー）を引いたら、Bテーブルの更新件数をカウントアップするとかが良い例ですね。簡単な例を書きたいと思います

f:id:hit10231023:20180309104332j:plain

トリガーの作成例

例えば、table_aにINSERTが入った場合、table_bのカウントが1アップ、table_bにDELETEが入った場合、table_bのカウントが1ダウンなんていう簡単な例から説明していきたいと思います

table_a

CREATE TABLE `table_a` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `msg` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

table_b

CREATE TABLE `table_b` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_name` varchar(200) NOT NULL,
  `cnt` int not null default '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

CREATE TRIGGER構文

CREATE TRIGGERは、引き金になる対象のTABLEに対して記載します。上記の例ですと、table_aがそれにあたります。こんな感じで書いてみました。

DELIMITER $$ 

CREATE TRIGGER table_a_add AFTER INSERT ON table_a
FOR EACH ROW
BEGIN
  INSERT INTO
       table_b (table_name, cnt)
   VALUES
       ('table_a', 1)
   ON DUPLICATE KEY UPDATE
        cnt = cnt + 1;

END $$

DELIMITER ;

簡単に説明すると、table_a にデータがインサートされた後（AFTER INSERT）、トリガーが発動するって意味になります。文中のFOR EACH ROWは、行に影響を与えた場合に限るという意味でいいのかな？ DELIMITER 句は、コマンドの終わりを何で判断するかになります

トリガー実行

では、さっそくtable_aにINSERTをかけてみたいと思います

INSERT INTO table_a
(
  msg
) VALUES (
  'テストです'
) ;

確認

table_bにデータが入っているか確認してみましょう

mysql>
SELECT * FROM table_b ;                           
+----+------------+-----+
| id | table_name | cnt |
+----+------------+-----+
|  1 | table_a    |   1 |
+----+------------+-----+

トリガー再度実行

では、もう一回table_aにINSERTをかけてみたいと思います

INSERT INTO table_a
(
  msg
) VALUES (
  'テスト２です'
) ;

確認

table_bのカウントがアップされているか確認してみましょう

mysql>
SELECT * FROM table_b ;                           
+----+------------+-----+
| id | table_name | cnt |
+----+------------+-----+
|  1 | table_a    |   2 |
+----+------------+-----+

無事カウントアップされているのが確認できたかと思います！

UPDATE TRIGGERも、DELETE TRIGGERも基本要領は同じなので割愛します・・・が！！！TRIGGERの設計的にはこれはNGだと私は思っております。

余談：NGだと思う理由

タイトルにも書いている通り、銃乱射の温床になってしまいます。

管理がしずらい *`見た目が悪い
INSERT/UPDATE/DELETEで似たようなちょっと別のSQLがダラダラと書かれる
TRIGGERの発動先（他テーブル）が複数存在する場合、上記と同じですが、SQLがTRIGGER内にだらだらと書かれる

NGの解決方法

そんなこと言ったって、TRIGGERのSQLは複雑になるんだ！という方は多いと思います。で、あれば、TRIGGER内はもっとシンプルにストアドプロシージャで記載してしまいましょう！という案です。ストアドプロシージャについては、ここで触れました。

hit.hateblo.jp

こんなにスマートになります

ちょっとストアドの例は書きませんが、上記のTRIGGER SQLがこんな形になります、

DELIMITER $$ 

CREATE TRIGGER table_a_add AFTER INSERT ON table_a
FOR EACH ROW
BEGIN

  SET @act='ADD' ;
  SET @table_name='table_a' ;
  CALL sp_table_counter(@act,@table_name) ;

END $$

CREATE TRIGGER table_a_upd AFTER UPDATE ON table_a
FOR EACH ROW
BEGIN

  SET @act='UPD' ;
  SET @table_name='table_a' ;
  CALL sp_table_counter(@act,@table_name) ;

END $$

CREATE TRIGGER table_a_add AFTER DELETE ON table_a
FOR EACH ROW
BEGIN

  SET @act='DEL' ;
  SET @table_name='table_a' ;
  CALL sp_table_counter(@act,@table_name) ;

END $$


DELIMITER ;

どうでしょう？これなら、どんなトリガーでもシンプルで、統一したフォーマットでかけますよね。トリガーを毛嫌いする人も多いですが、使い方によっては、アプリケーションの改修ゼロで、集計テーブルとか作成できたりもしちゃうすぐれものなので、ぜひ活用ください！！

Database JUNKY

MySQL,MariaDBを中心としたブログです