Postgres级联删除with trigger

Question

我的数据库结构如下：

table a (
    id     bigint primary key,
    del    timestamp
);
table b (
    id     bigint primary key,
    a_id   bigint references a(id) on delete cascade,
    del    timestamp
);

当从表a中删除一条记录时，我希望在当前时间设置del标志，并在表b中设置相应的标志(但实际上不是从数据库中删除记录)。

为此，我编写了一个触发器：

create or replace function a_delete_trigger()
    returns trigger as
$$
begin
    update a
    set del = now()
    where id = old.id; -- mark row

    return null; -- return null, so that the record is not removed from database
end;
$$
language plpgsql;
create trigger a_delete
    before delete
    on a
    for each row
execute procedure a_delete_trigger();

和表b的触发器

create or replace function b_delete_trigger()
    returns trigger as
$$
begin
    update b
    set del = now()
    where id = old.id;

    return null;
end;
$$
language plpgsql;
create trigger b_delete
    before delete
    on b
    for each row
execute procedure b_delete_trigger();

当我从表a ( delete from a where id = ? )中删除一条记录时，该记录被标记为deleted (标志del包含时间戳)，但是表b中的相应记录没有被标记(我猜，级联删除不起作用，因为我从触发器a_delete_trigger返回null )

如何使，当我从a表中删除一条记录时，该记录被标记为已删除，并且b中相应的记录也被标记为已删除？

Answer 1

将删除的b添加到a_delete_trigger。

create or replace function a_delete_trigger()
    returns trigger as
$$
begin
    update a
    set del = now()
    where id = old.id; -- mark row

    delete from b where a_id = a.id;

    return null; -- return null, so that the record is not removed from database
end;
$$
language plpgsql;
create trigger a_delete
    before delete
    on a
    for each row
execute procedure a_delete_trigger();