我该如何在Rails迁移中创建触发器？

Question

我正在尝试向我的PostgreSQL数据库（版本9）添加一些触发器，并且希望创建一个Rails迁移（使用版本4.2.7）来实现这个目标。因此，我创建了一个包含以下内容的迁移：

class CreateDeleteAddressTriggers < ActiveRecord::Migration
  def change
    execute <<-SQL
      CREATE OR REPLACE FUNCTION remove_address() RETURNS trigger
        LANGUAGE plpgsql AS
        $$BEGIN
          DELETE FROM public.addresses WHERE id = OLD.address_id;
      END;$$;

      CREATE TRIGGER remove_my_object_address
        AFTER DELETE ON public.my_objects FOR EACH ROW
        EXECUTE TRIGGER remove_address()

      CREATE TRIGGER remove_user_address
        AFTER DELETE ON public.users FOR EACH ROW
        EXECUTE TRIGGER remove_address()
    SQL
  end
end

很不幸，当我运行“rake db:migrate”任务时，我遇到了以下错误。

davea$ rake db:migrate
== 20160928184431 CreateDeleteAddressTriggers: migrating ======================
-- execute("      CREATE OR REPLACE FUNCTION remove_address() RETURNS trigger\n        LANGUAGE plpgsql AS\n        $$BEGIN\n          DELETE FROM public.addresses WHERE id =  OLD.address_id;\n      END;$$;\n\n      CREATE TRIGGER remove_my_object_address\n        AFTER DELETE ON public.my_objects FOR EACH ROW\n        EXECUTE TRIGGER remove_address()\n\n       CREATE TRIGGER remove_user_address\n        AFTER DELETE ON public.users FOR EACH ROW\n        EXECUTE TRIGGER remove_address()\n")
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::SyntaxError: ERROR:  syntax error at or near "TRIGGER"
LINE 9:         EXECUTE TRIGGER remove_address()
                        ^
:       CREATE OR REPLACE FUNCTION remove_address() RETURNS trigger
        LANGUAGE plpgsql AS
        $$BEGIN
          DELETE FROM public.addresses WHERE id = OLD.address_id;
      END;$$;

      CREATE TRIGGER remove_my_object_address
        AFTER DELETE ON public.my_objects FOR EACH ROW
        EXECUTE TRIGGER remove_address()

      CREATE TRIGGER remove_user_address
        AFTER DELETE ON public.users FOR EACH ROW
        EXECUTE TRIGGER remove_address()
/Users/davea/.rvm/gems/ruby-2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'
/Users/davea/.rvm/gems/ruby-2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `block in execute'
/Users/davea/.rvm/gems/ruby-2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:484:in `block in log'
/Users/davea/.rvm/gems/ruby-2.3.0/gems/activesupport-4.2.7.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/Users/davea/.rvm/gems/ruby-2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:478:in `log'
/Users/davea/.rvm/gems/ruby-2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:154:in `execute'
/Users/davea/.rvm/gems/ruby-2.3.0/gems/activerecord-4.2.7.1/lib/active_record/migration.rb:665:in `block in method_missing'
/Users/davea/.rvm/gems/ruby-2.3.0/gems/activerecord-4.2.7.1/lib/active_record/migration.rb:634:in `block in say_with_time'

如何修正我的迁移以使触发器正确运行？ 编辑：针对给出的答案，这是产生的错误...

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "CREATE"
LINE 11:       CREATE TRIGGER remove_user_address

- Dave

Answer 1

你在SQL语句中使用了错误的语法。你需要使用 EXECUTE PROCEDURE，而不是 EXECUTE TRIGGER。

正确的语法应该是：

  CREATE TRIGGER remove_my_object_address
    AFTER DELETE ON public.my_objects FOR EACH ROW
    EXECUTE PROCEDURE remove_address()
            ^^^^^^^^^

并且

 CREATE TRIGGER remove_user_address
    AFTER DELETE ON public.users FOR EACH ROW
    EXECUTE PROCEDURE remove_address()
            ^^^^^^^^^

Source: The PostgreSQL docs.

- user229044

---

我将 "EXECUTE TRIGGER" 替换为 "EXECUTE PROCEDURE"，但在运行迁移时出现了以下错误："PG::SyntaxError: ERROR: syntax error at or near "CREATE", LINE 11: CREATE TRIGGER remove_user_address"。 - Dave

@Dave 在每个语句后面都需要加上分号。 - user229044

@meagar 这种处理函数和触发器迁移的方式不会在测试环境迁移上执行代码。有什么建议吗？ - carbonr