将触发器从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. ABEFORE
trigger would have toRETURN NEW
to make updates work, butNEW
is not visible in aDELETE
trigger. So you'd needIF 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屋!