MySQLのテーブルに互いのトリガを更新する場合、エラーが出るのを防ぐ
MySQLでお互いのテーブルを更新するようなトリガを組んでいる場合、片方のテーブルを更新しようとすると以下のエラーが出る。
ERROR 1442 (HY000): Can't update table 'hoge' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
詳細→http://okwave.jp/qa/q4020691.html
カスケードのこととか→http://thinkit.co.jp/cert/article/0703/13/7/2.htm
なぜエラーになるのか
例えばAテーブルとBテーブルがあるとする。
それぞれトリガを設定していて、Aテーブルが更新されたとき、AテーブルのトリガによりBテーブルも更新する。
Bテーブルが更新されたとき、BテーブルのトリガによりAテーブルを更新する。
Aテーブル更新→AのトリガによりBテーブルを更新→Bテーブルの更新が入ったのでBのトリガ実行→BのトリガによりAテーブルの更新を行う→以下ループ
といった無限ループを防ぐために、MySQLでは入口でこのようなことにならないように弾いている。
これを防ぐためには一時的に更新側テーブルのトリガを無効にしなくてはならない。
準備したテーブル
mysql> create table hoge (id varchar(5),flg int)
Query OK, 0 rows affected (0.23 sec)
mysql> create table piyo (id varchar(5),name varchar(20),flg int)
Query OK, 0 rows affected (0.19 sec)
mysql> INSERT INTO hoge (id,flg) values('AAAAA',0);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO hoge (id,flg) values('BBBBB',0);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO piyo (id,name,flg) values('AAAAA','foo',0);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO piyo (id,name,flg) values('AAAAA','bar',0);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO piyo (id,name,flg) values('BBBBB','baz',0);
Query OK, 1 row affected (0.06 sec)
mysql> select * from hoge;
+-------+------+
| id | flg |
+-------+------+
| AAAAA | 0 |
| BBBBB | 0 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from piyo;
+-------+------+------+
| id | name | flg |
+-------+------+------+
| AAAAA | foo | 0 |
| AAAAA | bar | 0 |
| BBBBB | baz | 0 |
+-------+------+------+
3 rows in set (0.00 sec)
mysql> create table hoge (id varchar(5),flg int)
Query OK, 0 rows affected (0.23 sec)
mysql> create table piyo (id varchar(5),name varchar(20),flg int)
Query OK, 0 rows affected (0.19 sec)
mysql> INSERT INTO hoge (id,flg) values('AAAAA',0);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO hoge (id,flg) values('BBBBB',0);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO piyo (id,name,flg) values('AAAAA','foo',0);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO piyo (id,name,flg) values('AAAAA','bar',0);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO piyo (id,name,flg) values('BBBBB','baz',0);
Query OK, 1 row affected (0.06 sec)
mysql> select * from hoge;
+-------+------+
| id | flg |
+-------+------+
| AAAAA | 0 |
| BBBBB | 0 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from piyo;
+-------+------+------+
| id | name | flg |
+-------+------+------+
| AAAAA | foo | 0 |
| AAAAA | bar | 0 |
| BBBBB | baz | 0 |
+-------+------+------+
3 rows in set (0.00 sec)
●やりたいこと１
hogeテーブルのflgを1に更新したらそれに対応する同じidのpiyoテーブルのflgを全部1にする。
●やりたいこと２
piyoテーブルのflgを0に更新したらそれに対応する同じidのhogeテーブルのflgを全部0にする。
トリガ登録
hogeテーブルのトリガとpiyoテーブルのトリガを追加する。
少し長くなるのでsqlスクリプトに書く。
###################################################################################################
# hogeトリガ(hoge_t)
#
# [説明]
# hogeテーブルを更新したとき、piyoテーブルのフラグも更新する。
#
# [更新するレコード]
# /piyoテーブル/
# ・フラグ(flg)：hogeテーブルのフラグ(flg)が1に更新された時、
# hogeテーブルのidと同じフラグ(flg)を全て1に更新する。
#
# [テーブル名]
# hoge ：hogeテーブル
# piyo ：piyoテーブル
#
###################################################################################################
\! echo "■hogeトリガ(hoge_t)"
USE db_test; # データベース切り替え
SELECT DATABASE() AS 現在接続中のデータベース;
\! echo 既に同じトリガがあった場合は削除
DROP TRIGGER IF EXISTS hoge_t; # トリガが既にあった場合は一度削除
\! echo トリガ作成処理
DELIMITER $$ # 終端文字「;」を「$$」へ変更
CREATE TRIGGER hoge_t AFTER UPDATE # タイミング：該当テーブルのレコードが更新された後
ON hoge FOR EACH ROW
thisTrigger: BEGIN # 実行SQL文
# トリガ有効フラグセッション変数をチェック
IF (@hoge_t_run = FALSE)
THEN
LEAVE thisTrigger; # フラグが0の時はトリガを無効にする
END IF;
SET @piyo_t_run = FALSE; # カスケードエラー回避、一時的にpiyoトリガの無効
# トリガ処理
UPDATE piyo SET flg = 1
WHERE id = new.id AND
old.flg != 1 AND
new.flg = 1;
SET @piyo_t_run = TRUE; # カスケードエラー回避、piyoトリガの有効
END;
$$
DELIMITER ; # 終端文字を「;」に戻す
\! echo トリガを有効にする
SET @hoge_t_run = TRUE; # トリガ有効フラグセッション変数を有効
SELECT @hoge_t_run AS 'トリガ確認：@hoge_t_run';
解説：「@hoge_t_run」、「@piyo_t_run」変数にトリガを無効にするか有効にするかを管理する。この値にFALSEをセットすることで無効にすることができる。
そして一番のミソはトリガ処理を行う前に「piyo_t」のトリガを無効にしてUPDATE処理を行い、もう一度有効にすることでエラーを回避している。
piyoテーブルのトリガ「piyo_t」も同じように作成。
###################################################################################################
# piyoトリガ(piyo_t)
#
# [説明]
# piyoテーブルを更新したとき、hogeテーブルのフラグも更新する。
#
# [更新するレコード]
# /hogeテーブル/
# ・フラグ(flg)：piyoテーブルのフラグ(flg)が0に更新された時、
# hogeテーブルのidと同じフラグ(flg)を更新する。piyoテーブルのidと同じ他のフラグ(flg)は変えない。
#
# [テーブル名]
# piyo ：piyoテーブル
# hoge ：hogeテーブル
#
###################################################################################################
\! echo "■piyoトリガ(piyo_t)"
USE db_test; # データベース切り替え
SELECT DATABASE() AS 現在接続中のデータベース;
\! echo 既に同じトリガがあった場合は削除
DROP TRIGGER IF EXISTS piyo_t; # トリガが既にあった場合は一度削除
\! echo トリガ作成処理
DELIMITER $$ # 終端文字「;」を「$$」へ変更
CREATE TRIGGER piyo_t AFTER UPDATE # タイミング：該当テーブルのレコードが更新された後
ON piyo FOR EACH ROW
thisTrigger: BEGIN # 実行SQL文
# トリガ有効フラグセッション変数をチェック
IF (@piyo_t_run = FALSE)
THEN
LEAVE thisTrigger; # フラグが0の時はトリガを無効にする
END IF;
SET @hoge_t_run = FALSE; # カスケードエラー回避、一時的にhogeトリガの無効
# トリガ処理
UPDATE hoge SET flg = 0
WHERE id = new.id AND
old.flg != 0 AND
new.flg = 0;
SET @hoge_t_run = TRUE; # カスケードエラー回避、hogeトリガの有効
END;
$$
DELIMITER ; # 終端文字を「;」に戻す
\! echo トリガを有効にする
SET @piyo_t_run = TRUE; # トリガ有効フラグセッション変数を有効
SELECT @piyo_t_run AS 'トリガ確認：@piyo_t_run';
テスト
さっそく作成したSQLスクリプトを実行してトリガを登録してみる。
mysql> SHOW TRIGGERS; # トリガー確認
Empty set (0.01 sec)
mysql> source hoge.sql
■hogeトリガ(hoge_t)
～長いので省略
mysql> source piyo.sql
■piyoトリガ(piyo_t)
～長いので省略
これで準備は整ったので実際に値を更新してみる。
※★は更新された場所。
結果
mysql> select * from hoge;
+-------+------+
| id | flg |
+-------+------+
| AAAAA | 0 |
| BBBBB | 0 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from piyo;
+-------+------+------+
| id | name | flg |
+-------+------+------+
| AAAAA | foo | 0 |
| AAAAA | bar | 0 |
| BBBBB | baz | 0 |
+-------+------+------+
3 rows in set (0.00 sec)
# hogeのidがAAAAAのflgを1に更新する
mysql> update hoge set flg = 1 where id = 'AAAAA';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from hoge;
+-------+------+
| id | flg |
+-------+------+
| AAAAA | 1 |★
| BBBBB | 0 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from piyo;
+-------+------+------+
| id | name | flg |
+-------+------+------+
| AAAAA | foo | 1 |★
| AAAAA | bar | 1 |★
| BBBBB | baz | 0 |
+-------+------+------+
3 rows in set (0.00 sec)
mysql> select * from hoge;
+-------+------+
| id | flg |
+-------+------+
| AAAAA | 0 |
| BBBBB | 0 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from piyo;
+-------+------+------+
| id | name | flg |
+-------+------+------+
| AAAAA | foo | 0 |
| AAAAA | bar | 0 |
| BBBBB | baz | 0 |
+-------+------+------+
3 rows in set (0.00 sec)
# hogeのidがAAAAAのflgを1に更新する
mysql> update hoge set flg = 1 where id = 'AAAAA';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from hoge;
+-------+------+
| id | flg |
+-------+------+
| AAAAA | 1 |★
| BBBBB | 0 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from piyo;
+-------+------+------+
| id | name | flg |
+-------+------+------+
| AAAAA | foo | 1 |★
| AAAAA | bar | 1 |★
| BBBBB | baz | 0 |
+-------+------+------+
3 rows in set (0.00 sec)
特にエラーも起きずに、piyo側にもflgが1に設定されている。
piyo側の更新もやってみる。
piyoテーブルのidが'AAAAA'でnameが'bar'を「0」に更新する。
mysql> update piyo set flg = 0 where id = 'AAAAA' AND name = 'bar';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from hoge;
+-------+------+
| id | flg |
+-------+------+
| AAAAA | 0 |★
| BBBBB | 0 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from piyo;
+-------+------+------+
| id | name | flg |
+-------+------+------+
| AAAAA | foo | 1 |
| AAAAA | bar | 0 |★
| BBBBB | baz | 0 |
+-------+------+------+
3 rows in set (0.00 sec)
できた。これで互いの更新ができる。
barのflgが「0」になったことでトリガが走り、hogeの'AAAAA'のflgが「0」になっている。
副産物的にできた@変数を使って、トリガの有効無効を好きな時に変えられる。
SET @hoge_t_run = FALSE：hoge_tトリガーを無効にする。
SET @piyo_t_run = FALSE：piyo_tトリガーを無効にする。
完全にトリガーを消したい場合
DROP TRIGGER hoge_t;
DROP TRIGGER piyo_t;
特に無限ループでもないトリガはMySQL側で判定して使えるようにしてくれたら楽なのにな、と思った。
Author And Source
この問題について(MySQLのテーブルに互いのトリガを更新する場合、エラーが出るのを防ぐ), 我々は、より多くの情報をここで見つけました https://qiita.com/rita_cano_bika/items/ea3ddd2dc80f65a58e26著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .