如何创建条件触发器

Question

我有一个带有ID作为自动增量主键和另一个ID的表。

CREATE TABLE tester (
"id" integer PRIMARY KEY AUTOINCREMENT,
"refId" integer DEFAULT 0
);

refid 应该能够为0(默认值(或参考 ID 如果refid> 0(即充当外键(。

。

现在我需要两个约束：

1. 只有在没有其他行的 refid
的情况下，只有在未使用(引用？(的 id 时才能删除一行。
2. 只有在其 refid 为0时才应删除一行。

据我所知，我需要创建一个触发器，该触发器在删除事件发生之前检查这些约束。并取决于 refid 的值中止删除操作或允许它。

但是，我很难理解此的语法以及如何进行有条件的检查。但是到目前为止(考虑到(我所拥有的是1。(：

CREATE TRIGGER no_delete_if_inuse
BEFORE DELETE ON tester
FOR EACH ROW BEGIN
    SELECT RAISE(ABORT, 'cannot delete because of foreign key violation')
    WHERE  (SELECT "refId" FROM tester WHERE "refId" = OLD."id") IS NOT NULL;        
END;

和有关2。(

CREATE TRIGGER no_delete_if_ref
BEFORE DELETE ON tester
FOR EACH ROW BEGIN
    IF OLD."refId" > 0 THEN RAISE(ABORT, "cannot delete tester because it refers to an existing tester");
END;

这有意义并且有效吗？我完全不确定，对我而言，我都是菜鸟。

也是最后一个问题，我可以将其合并到一个触发器中吗？例如，这是一个有效的查询：

CREATE TRIGGER no_delete_if_inuse
    BEFORE DELETE ON tester
    FOR EACH ROW BEGIN
        SELECT RAISE(ABORT, 'cannot delete because of foreign key violation')
        WHERE  (SELECT "refId" FROM tester WHERE ("refId" = OLD."id" OR "refId" > 0) ) IS NOT NULL;        
    END;

Answer 1

您可以定义一个涉及同一表的外键。使用null而不是0进行行，而无需参考：

create table tester(
    id int primary key, 
    refid int references tester,
    check (id <> refid)
);
insert into tester values
(1, null),
(2, null),
(3, 1),
(4, 3);

您需要一个触发器，以确保无法删除另一个引用另一个的行。

create or replace function before_delete_on_tester()
returns trigger language plpgsql as $$
begin
    if old.refid is not null then
        raise exception 
            'Cannot delete: (id)=(%) references (id)=(%)', old.id, old.refid;
    end if;
    return old;
end $$;
create trigger before_delete_on_tester
before delete on tester
for row execute procedure before_delete_on_tester();

测试：

delete from tester where id = 1;
ERROR:  update or delete on table "tester" violates foreign key constraint "tester_refid_fkey" on table "tester"
DETAIL:  Key (id)=(1) is still referenced from table "tester".  
delete from tester where id = 4;
ERROR:  Cannot delete from tester. (id)=(4) references (id)=(3)
CONTEXT:  PL/pgSQL function before_delete_on_tester() line 4 at RAISE   

在Postgres中，您必须定义触发功能。阅读更多：

• 触发行为的概述
• 触发过程
• 创建触发器