将触发器从Oracle 11g迁移到Postgresql 8.4 [英] Migrating trigger from Oracle 11g to 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.

    这篇关于将触发器从Oracle 11g迁移到Postgresql 8.4的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！