LoginSignup
8
3

More than 5 years have passed since last update.

@rita_cano_bika(リタ カノ)

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

Last updated at Posted 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側で判定して使えるようにしてくれたら楽なのにな、と思った。

8
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
3