postgresql 级联删除导致无法访问触发器中已删除的外键记录

vbopmzt1 于 2023-10-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(153)

假设存在以下表和触发器：

CREATE TABLE "event"
(
    count           bigserial NOT NULL,
    action          text      NOT NULL,
    child_id        int       NOT NULL,
    persisted_field text      NULL,
    CONSTRAINT "event_pkey" PRIMARY KEY (count)
);

CREATE TABLE "parent_table"
(
    id               int PRIMARY KEY,
    field_to_persist text
);

CREATE TABLE "child_table"
(
    id        int PRIMARY KEY,
    parent_id int,
    CONSTRAINT "child_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "parent_table" ("id") ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION add_child_trigger() RETURNS TRIGGER AS
$$
DECLARE
    v_id        int = coalesce(new.id, old.id);
    v_parent_id int = coalesce(new.parent_id, old.parent_id);
BEGIN
    INSERT INTO "event" ("action", "child_id", "persisted_field")
    VALUES (tg_op, v_id,
            (SELECT "parent_table"."field_to_persist" FROM "parent_table" WHERE "parent_table"."id" = v_parent_id));

    IF (tg_op = 'DELETE') THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "on_change_add_change_event"
    BEFORE INSERT OR UPDATE OR DELETE
    ON "child_table"
    FOR EACH ROW
EXECUTE PROCEDURE "add_child_trigger"();

执行以下语句：

INSERT INTO "parent_table" VALUES (1, 'something');
INSERT INTO "child_table" VALUES (1, 1);
INSERT INTO "child_table" VALUES (2, 1);
DELETE FROM "child_table" WHERE "id" = 2;
DELETE FROM "parent_table" WHERE "id" = 1;

event表现在包含以下记录：
| 计数|行动|子ID|持续字段|
| --|--|--|--|
| 1 |插入| 1 |东西|
| 2 |插入| 2 |东西|
| 3 |删除| 2 |东西|
| 4 |删除| 1 |null|
但是，与通过删除parent_table所指向的记录来级联删除child_table的条目相比，persisted_field在单独删除child_table的条目时为空。为什么persisted_fieldnull与count 4记录在一起，我如何将触发器更改为something？

postgresql

来源：https://stackoverflow.com/questions/77214231/can-not-access-deleted-foreign-key-record-in-trigger-caused-by-cascade-delete

1条答案

按热度按时间

njthzxwz1#

为什么persisted_fieldnull与count 4一起记录
触发器按预期触发before delete on child_table，但在级联场景中，parent_table上的delete完成 * 之后 * 仍然会发生，因此这个子查询什么也没有找到：(demo)

(SELECT "parent_table"."field_to_persist" FROM "parent_table" 
 WHERE "parent_table"."id" = v_parent_id)

当您到达此语句时，您可能希望此语句查找的"parent_table"."id"已经被删除。Chapter 39. Triggers：
在行级BEFORE触发器中执行的SQL命令 * 将 * 看到先前在同一外部命令中处理的行的数据更改的影响。
这涉及到目标表以及任何其他受命令直接或间接影响的对象中的更改（通过级联和触发器，以及生成的列，规则或例程副作用）。
没有办法延迟parent_table行的删除，以便当delete级联到链接的child_table行时，它仍然可以被随后触发的触发器看到。关于CREATE TRIGGER：
由外键强制操作（如ON UPDATE CASCADE或ON DELETE SET NULL）导致的行更新或删除被视为导致它们的SQL命令的一部分（注意此类操作永远不会延迟）。
第二（部分）问题：
如何将触发器更改为something？
创建另一个并将其附加到parent_table。确保child_table上的条目不会重复它的工作，最终从parent_table触发器得到一个event条目，然后从child_table得到另一个条目。
before delete on "parent_table"触发器可以创建一个临时条目：demo

CREATE TABLE IF NOT EXISTS parent_table_deletions
(LIKE "public"."parent_table" INCLUDING ALL);

CREATE OR REPLACE FUNCTION add_parent_trigger() 
RETURNS TRIGGER AS $add_parent_trigger$
BEGIN  INSERT INTO parent_table_deletions SELECT old.*;
       RETURN old;
END $add_parent_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER "temporarily_persist_deleted_parent_table_row"
BEFORE DELETE ON "parent_table" FOR EACH ROW
EXECUTE PROCEDURE "add_parent_trigger"();

然后child_table上的触发器仍然可以看到它，并使用coalesce()及其当前查找的结果。之后，子触发器可以擦除临时条目

CREATE OR REPLACE FUNCTION add_child_trigger() RETURNS TRIGGER AS
$add_child_trigger$
DECLARE v_id        int = coalesce(new.id, old.id);
        v_parent_id int = coalesce(new.parent_id, old.parent_id);
BEGIN
    WITH wipe_recent_deletes AS (
        DELETE FROM "parent_table_deletions" AS o
        WHERE o."id" = v_parent_id
        RETURNING o."field_to_persist")
    INSERT INTO "event" ("action", "child_id", "persisted_field")
    VALUES (tg_op, v_id,coalesce(
                           (SELECT c."field_to_persist"
                            FROM "parent_table" AS c
                            WHERE c."id" = v_parent_id)
                          ,(SELECT "field_to_persist" FROM wipe_recent_deletes) 
                        ) 
            );
    RETURN coalesce(NEW,OLD);
END $add_child_trigger$ LANGUAGE plpgsql;

您可能还需要考虑将ON UPDATE CASCADE添加到child_parent_id_fkey以涵盖这种情况。否则，对parent_table.id的更改会引发错误：demo

ERROR:  update or delete on table "parent_table" violates foreign key constraint "child_parent_id_fkey" on table "child_table"
DETAIL:  Key (id)=(2) is still referenced from table "child_table".

赞(0）回复(0）举报 2023-10-18

我来回答

postgresql 级联删除导致无法访问触发器中已删除的外键记录

1条答案

相关问题

热门标签

最新问答