PostgreSQL软删除BEFORE触发器报错问题求助

你遇到的错误是因为BEFORE DELETE触发器中执行的UPDATE操作，和触发它的DELETE命令同时修改了同一行数据，PostgreSQL不允许在同一个命令触发的触发器里对当前行执行写操作（UPDATE/DELETE），否则会触发行修改冲突。

你的当前逻辑是：触发器先UPDATE行设置deleted_at，然后返回OLD，这会让数据库继续执行原本的DELETE操作——等于同一行先被UPDATE，紧接着又要被DELETE，这就触发了报错提示的冲突。

不需要执行UPDATE语句，直接在BEFORE DELETE触发器中修改OLD行的deleted_at字段，然后返回NULL来取消物理删除操作。这样既完成了软删除标记，又避免了行修改冲突。

修改后的触发器函数和触发器定义如下：

CREATE OR REPLACE FUNCTION user_soft_delete() RETURNS trigger AS $$
BEGIN
    -- 直接修改OLD行的deleted_at字段
    OLD.deleted_at = NOW();
    -- 返回NULL取消物理删除操作
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_soft_delete_trigger
BEFORE DELETE ON user_schema.user_account
FOR EACH ROW
EXECUTE PROCEDURE user_soft_delete();

• 返回NULL是BEFORE DELETE触发器的特殊用法：当触发器返回NULL时，PostgreSQL会放弃对当前行执行原本的DELETE操作。
• 这种方式不需要额外的UPDATE语句，直接修改OLD行的字段，数据库会自动将这个修改应用到表中，同时跳过物理删除，完美实现软删除。

内容的提问来源于stack exchange，提问作者Cross