hair_trigger というgem があったがエラーが出て機能しなかった。
execute メソッドで直接SQLを実行することになる。
migrate 実行順が影響するので、migration ファイルを別に作成
$ ./script/rails g migration trigger_photos
たとえば以下のようにsqlを流したい場合、
DELIMITER $$ CREATE TRIGGER triger_delete_photo AFTER DELETE ON photos FOR EACH ROW BEGIN IF OLD.is_deleted <= 0 THEN UPDATE `user` SET num_create = num_create - 1 WHERE `user`.id = OLD.user_id; END IF; END$$ CREATE TRIGGER triger_update_photo AFTER UPDATE ON photos FOR EACH ROW BEGIN IF OLD.is_deleted <> NEW.is_deleted THEN IF NEW.is_deleted <= 0 THEN UPDATE `user` SET num_create = num_create + 1 WHERE `user`.id = OLD.user_id; ELSE UPDATE `user` SET num_create = num_create - 1 WHERE `user`.id = OLD.user_id; END IF; END IF; END$$ DELIMITER ;
execute は複数のクエリを実行できない。
DELIMITER の設定を行うこともできない。
def up execute <<-SQL CREATE TRIGGER triger_delete_photo AFTER DELETE ON photos FOR EACH ROW BEGIN IF OLD.is_deleted <= 0 THEN UPDATE `user` SET num_create = num_create - 1 WHERE `user`.id = OLD.user_id; END IF; END SQL execute <<-SQL CREATE TRIGGER triger_update_photo AFTER UPDATE ON photos FOR EACH ROW BEGIN IF OLD.is_deleted <> NEW.is_deleted THEN IF NEW.is_deleted <= 0 THEN UPDATE `user` SET num_create = num_create + 1 WHERE `user`.id = OLD.user_id; ELSE UPDATE `user` SET num_create = num_create - 1 WHERE `user`.id = OLD.user_id; END IF; END IF; END SQL end def down execute 'DROP TRIGGER triger_delete_photo' execute 'DROP TRIGGER triger_update_photo' end
こうなる。