MySQL でトリガーから動的 SQL を使いたい場合
MySQL5.1 でトリガーをあるテーブルに仕込んで、テーブルに値(ストアドプロシージャ名)を挿入したら、そのストアドプロシージャを実行したい。
イメージとしてはこんな感じ。
DELIMITER \\ DROP TRIGGER IF EXISTS `foo`\\ CREATE TRIGGER `foo` AFTER INSERT ON `tbl_foo` FOR EACH ROW BEGIN Call NEW.s_name(NEW.id, NEW.value_foo, NEW.value_bar); END \\ DELIMITER ;
これをそのままやったら、PROCEDURE new.s_name does not exist と怒られる。
プリペアドステートメントを使って、以下のようにしてみた。
DELIMITER \\ DROP TRIGGER IF EXISTS `foo`\\ CREATE TRIGGER `foo` AFTER INSERT ON `tbl_foo` FOR EACH ROW BEGIN SET @s = CONCAT('Call ', NEW.s_name, '(?, ?, ?); PREPARE stmt from @s; SET @id = NEW.id; SET @foo = NEW.value_foo; SET @bar = NEW.value_bar; EXECUTE stmt USING @id, @foo, @bar; DEALLOCATE PREPARE stmt; END \\ DELIMITER ;
ストアドプロシージャをコンパイル時に、ERROR 1336 (0A000) at line 3: Dynamic SQL is not allowed in stored function or trigger と出た。
結局どうしたかというと、少しめんどくさいけど Gearman MySQL UDF を使用して、非同期に Worker を実行するようにし、Worker がストアドプロシージャ(この場合だと、s_name)を実行する形にしたらうまい事いった。
Gearman の MySQL UDF については id:perezvon さんの以下のエントリに詳しく書かれている。
http://d.hatena.ne.jp/perezvon/20090501/1241156643
http://d.hatena.ne.jp/perezvon/20090502/1241232793
素晴らしい!