PostgreSQL 触发器不起作用 - 无论是 BEFORE 还是 AFTER DELETE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10687582/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL trigger not working - neither BEFORE nor AFTER DELETE
提问by kinderplatonic
I have just left MySQL behind in favor of PostgreSQL, and I have a question regarding triggers. This trigger is designed to update a field in the 'workflow' table if a row is deleted in the 'processes' table.
我刚刚放弃 MySQL 而支持 PostgreSQL，我有一个关于触发器的问题。如果在“流程”表中删除了一行，则此触发器旨在更新“工作流”表中的字段。
CREATE OR REPLACE FUNCTION fn_process_delete() RETURNS TRIGGER AS $$
BEGIN
UPDATE workflow SET deleted_process_name = OLD.process_name
WHERE process_id = OLD.process_id;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS process_delete ON processes;
CREATE TRIGGER process_delete
AFTER DELETE ON processes
FOR EACH ROW
EXECUTE PROCEDURE fn_process_delete();
My question is two-fold:
我的问题有两个：
If I use AFTER DELETE as above, the row will delete, but the update statement does not update the field in the 'workflow' table.
If I use BEFORE DELETE, the processes table will not perform the delete at all and delivers an error saying "No unique identifier for this row".
如果我像上面一样使用 AFTER DELETE，该行将被删除，但更新语句不会更新“工作流”表中的字段。
如果我使用 BEFORE DELETE，则流程表将根本不会执行删除操作，并会显示“该行没有唯一标识符”的错误消息。
Can anyone advise?
任何人都可以建议吗？
回答by Erwin Brandstetter
Question 2:
问题2：
Your trigger function ends with:
您的触发器功能以：
RETURN NULL;
With that you skip the execution of the triggering event. Per documentation on trigger procedures:
这样您就可以跳过触发事件的执行。根据触发程序的文档：
Row-level triggers fired
BEFOREcan return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and theINSERT/UPDATE/DELETEdoes not occur for this row).
行级触发器发射
BEFORE可以返回null信号触发经理跳过操作的此行的剩余部分（即，随后的触发器不会被触发，以及INSERT/UPDATE/DELETE没有此行发生）。
You need to replace that with:
您需要将其替换为：
RETURN OLD;
for the system to proceed with the deletion of the row. Here is why:
以便系统继续删除该行。原因如下：
In the case of a before-triggeron
DELETE, the returned value has no direct effect, but it has to be nonnullto allow the trigger action to proceed. Note thatNEWis null inDELETEtriggers, so returning that is usually not sensible. The usual idiom inDELETEtriggers is to returnOLD.
在触发前on的情况下
DELETE，返回值没有直接影响，但它必须为非空以允许触发操作继续进行。请注意，触发器中的NEW值为 nullDELETE，因此返回通常是不明智的。触发器中的惯用语DELETE是 returnOLD。
Bold emphasis mine.
大胆强调我的。
Question 1
问题 1
I see no reason why your trigger and trigger function should not work as AFTER DELETE. It goes without saying that a row with a matching process_idhas to exist in table workflow.
我看不出为什么您的触发器和触发器功能不能作为AFTER DELETE. 不用说process_idtable 中必须存在匹配的行workflow。
