PostgreSQL 触发器不工作 - 既不在删除之前也不在删除之后

Question

我刚刚放弃了 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();

我的问题有两个方面:

1. 如果我像上面那样使用 AFTER DELETE，该行将被删除，但更新语句不会更新“工作流”表中的字段。

2. 如果我使用 BEFORE DELETE，进程表将根本不会执行删除并提供一个错误“此行没有唯一标识符”。

谁能给个建议？

Answer 1

问题2:

你的触发函数结束于:

RETURN NULL;

这样您就可以跳过触发事件的执行。 Per documentation on trigger procedures :

Row-level triggers fired BEFORE can 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 the INSERT/UPDATE/DELETE does not occur for this row).

您需要将其替换为:

RETURN OLD;

让系统继续删除行。原因如下:

In the case of a before-trigger on DELETE, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed. Note that NEW is null in DELETE triggers, so returning that is usually not sensible. The usual idiom in DELETE triggers is to return OLD.

大胆强调我的。

问题一

我看不出为什么您的触发器和触发器函数不能像 AFTER DELETE 那样工作。不用说，具有匹配 process_id 的行必须存在于表 workflow 中。