More than 5 years have passed since last update.

MySQLのテーブルに互いのトリガを更新する場合、エラーが出るのを防ぐ

Last updated at 2016-04-06Posted at 2015-11-02

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)

●やりたいこと１
hogeテーブルのflgを1に更新したらそれに対応する同じidのpiyoテーブルのflgを全部1にする。

●やりたいこと２
piyoテーブルのflgを0に更新したらそれに対応する同じidのhogeテーブルのflgを全部0にする。

トリガ登録

hogeテーブルのトリガとpiyoテーブルのトリガを追加する。
少し長くなるのでsqlスクリプトに書く。

hoge.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.sql

###################################################################################################
# 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)

特にエラーも起きずに、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側で判定して使えるようにしてくれたら楽なのにな、と思った。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up