将触发器从Oracle 11g迁移到Postgresql 8.4

本文介绍了将触发器从Oracle 11g迁移到Postgresql 8.4的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在Oracle中的触发器看起来像这样……

My trigger in Oracle looks like this…

CREATE OR REPLACE TRIGGER example$example BEFORE UPDATE OR DELETE ON example FOR EACH ROW BEGIN INSERT INTO example$ VALUES ( :old.key, :old.name, :old.describe seq.nextVal ); END;

我以为我可以将其翻译成Postgresql ...

I thought I could simply translate to Postgresql with this…

CREATE OR REPLACE TRIGGER example$example BEFORE UPDATE OR DELETE ON example FOR EACH ROW BEGIN INSERT INTO example$ VALUES ( OLD.key, OLD.name, OLD.describe, NEXTVAL('seq') ); END;

在INSERT语句的末尾出现错误.Postgresql中没有匿名块吗?我必须把它放在一个函数中吗?如果是这样，函数的返回值是多少?空吗?

I'm getting an error at the end of the INSERT statement. Are there no anonymous blocks in Postgresql? Do I have to put this in a function? If so, what is the return value of the function? NULL?

所以我现在正在尝试…

CREATE OR REPLACE FUNCTION example$trigger() RETURNS TRIGGER AS $func$ BEGIN INSERT INTO example$ ( key, name, describe, seq ) VALUES ( OLD.key, OLD.name, OLD.describe, NEXTVAL('seq') ); END $func$ LANGUAGE plpgsql CREATE OR REPLACE TRIGGER example$trigger AFTER UPDATE OR DELETE ON example FOR EACH ROW EXECUTE PROCEDURE example$trigger;

该函数通过触发报告进行编译，没有错误...

The function compiles with no errors by the trigger reports…

ERROR: syntax error at or near "TRIGGER" LINE 1: CREATE OR REPLACE TRIGGER example$trigger ^ ********** Error ********** ERROR: syntax error at or near "TRIGGER" SQL state: 42601 Character: 19

推荐答案

Postgres中的触发器不直接提供触发器代码，而是调用 trigger函数，该函数可以从任意数量的触发器中调用，尽管通常它们是针对一个特定表上的一个特定事件而定制的.

Triggers in Postgres don't provide trigger code directly, but call a trigger function, which can be called from any number of triggers, though often they are customized for one particular event on one particular table.

触发功能:

CREATE OR REPLACE FUNCTION trg_some_tbl_foo() RETURNS trigger AS $func$ BEGIN INSERT INTO some_tbl(key, name, describe) -- or some_other_tbl? VALUES (OLD.key, OLD.name, OLD.describe); RETURN OLD; END $func$ LANGUAGE plpgsql

触发器:

CREATE TRIGGER foo -- not: "CREATE OR REPLACE" ! AFTER UPDATE OR DELETE ON some_tbl FOR EACH ROW EXECUTE PROCEDURE trg_some_tbl_foo()

• 为它添加一个 AFTER 触发器以简化操作. BEFORE 触发器必须进行 RETURN NEW 才能使更新生效，但是 NEW 在 DELETE 触发器中不可见.因此，您需要 IF TG_OP = ... 等.

  • Make it an AFTER trigger to simplify. A BEFORE trigger would have to RETURN NEW to make updates work, but NEW is not visible in a DELETE trigger. So you'd need IF TG_OP = ... etc.

    始终为持久化的 INSERT 语句提供目标列表.在Oracle触发器中也是如此.

    Always provide a target list for persisted INSERT statements. This is just as bad in an Oracle trigger.

    您可能有一个带有 serial 列的表.只是不要在插入内容中提及它，序列中的下一个ID会自动插入.

    You probably have a table with a serial column. Just don't mention it in the insert, the next id from the sequence is inserted automatically.

    SO上有许多代码示例.

    There are numerous code examples here on SO.