ActiveRecord で Trigger を使用する

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

こうなる。