めも

日記帳

mysqlとりがーつかってみる

MySql トリガーメモ

忘れそうだしブログはじめたしめも.
たぶんめっちゃ基本的なことだけど知らなかった。

やりたい事はfooテーブルにupdateが走ったら、barテーブルの更新日を更新するということ。

まずテーブル作成

fooテーブル

CREATE TABLE `foo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `val` char(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
)

barテーブル

CREATE TABLE `bar` (  
  `id` bigint(20) NOT NULL AUTO_INCREMENT,  
  `foo_id` bigint(20) NOT NULL,  
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE  CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_foo_id` (`foo_id`),
  CONSTRAINT `fk_foo_id` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
)

トリガ作るよ

その前に、トリガー対象のテーブルが追加更新削除された時の情報をほかのテーブルにも持ってきて使いたいときは

NEW.カラム名    INSERT及びUPDATEで使用可能  
OLD.カラム名    DELETE及びUPDATEで使用可能  

トリガ作成

mysql> DELIMITER $$
mysql> CREATE TRIGGER update_trigger AFTER UPDATE
    -> ON foo FOR EACH ROW
    -> BEGIN
    -> UPDATE bar SET updated_at = CURRENT_TIMESTAMP() WHERE foo_id = NEW.id;
    -> END;
    -> $$
mysql> DELIMITER ;  

適当な初期データをいれる。

mysql> SELECT *  FROM foo;
+----+------+
| id | val  |
+----+------+
|  1 | text |
+----+------+
1 row in set (0.00 sec)


mysql> SELECT *  FROM bar;
+----+--------+---------------------+
| id | foo_id | updated_at          |
+----+--------+---------------------+
|  1 |      1 | 2017-05-12 11:38:59 |
+----+--------+---------------------+
1 row in set (0.00 sec)

UPDATE文を流してupdated_atの「2017-05-12 11:38:59」が更新されたら成功

mysql> UPDATE foo SET val = 'トリガーテスト';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT *  FROM bar;
+----+--------+---------------------+
| id | foo_id | updated_at          |
+----+--------+---------------------+
|  1 |      1 | 2017-05-12 11:39:44 |
+----+--------+---------------------+
1 row in set (0.00 sec)

できた。

おしまい。

参考
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.19 CREATE TRIGGER 構文
トリガの作成(CREATE TRIGGER文) - トリガの作成 - MySQLの使い方
古い値と新しい値の参照(OLD, NEW) - トリガの作成 - MySQLの使い方