檢測分佈表中的循環關係

我有以下兩個表，它們將定義從表 A 上的項目到表 A 上的其他項目的分配率（我想我可以說 B 定義了 A 與其自身的多對多關係）。

create table A (
   code char(5) not null primary key,
   desc varchar(50) not null
)

create table B (
   code1 char(5) not null,
   code2 char(5) not null,
   perc numeric(6,2) not null,

   constraint pk_B primary key (code1, code2),
   constraint fk_B_1 foreign key (code1) references A (code) on update cascade on delete cascade,
   constraint fk_B_2 foreign key (code2) references A (code) on update cascade on delete cascade
)

我想要一種簡單的方法來檢查在 B 上插入的新記錄是否會導致創建循環引用。

這個例子說明了這個問題：

insert into B (code1, code2, perc) values ('01.10', '02.10', 100);
insert into B (code1, code2, perc) values ('02.10', '02.11', 50);
insert into B (code1, code2, perc) values ('02.10', '02.12', 50);
insert into B (code1, code2, perc) values ('02.11', '04.10', 50);
insert into B (code1, code2, perc) values ('02.11', '02.12', 50);
insert into B (code1, code2, perc) values ('04.10', '01.10', 50);
insert into B (code1, code2, perc) values ('04.10', '04.11', 50);

這組數據將生成以下循環引用：

我正在使用 Firebird 2.5，但我更喜歡只使用標準 sql，因為我關心的是可移植性。我們可能很快就會改變 DBMS。

Firebird 上的文件雖然有點問題，但我相信 Firebird 中的 CTE 記錄在這裡（搜尋 Common Table Expressions）：https ://www.firebirdsql.org/refdocs/langrefupd21-select.html

在@ypercube對該問題發表評論之後，我想出了這個解決方案：

with recursive A as (
   select code1, code2 from B
   where code1 = new.code2
   union all
   select a2.code1, a2.code2 from B a2
   join A on a2.code1 = A.code2
)

select * from A
where code2 = new.code1

如果返回任何記錄，則 new.code2 引用 new.code1，如果插入此新記錄，則將存在循環引用。

特別是對於 Firebird，這是我使用的：

CREATE EXCEPTION EX1 'circular reference exception';

SET TERM ^ ;

CREATE TRIGGER TRIGGER_B_CIRCULAR FOR B
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS 
DECLARE CODE char(5);
BEGIN 
   FOR with recursive A as (
           SELECT CODE1, CODE2 FROM B
           WHERE CODE1 = new.CODE2
           UNION ALL
           SELECT a2.CODE1, a2.CODE2 FROM B a2
           JOIN A ON a2.CODE1 = A.CODE2
       )

       SELECT CODE2 FROM A WHERE CODE2 = new.CODE1
       INTO :CODE DO
           exception EX1; 
END^

SET TERM ; ^ 

引用自：https://dba.stackexchange.com/questions/106903