CHECK CYCLE BEFORE ADD NEW RECORD

SELECT * FROM BOM;

A B
B C
C D

--CHECK CYCLE BEFORE ADD NEW RECORD ('D','E') => NO CYCLE ERROR
SELECT COUNT(*) FROM
(
SELECT CONNECT_BY_ISCYCLE CYCLE_ERROR,P,C
FROM
(
SELECT P,C
FROM BOM
START WITH P ='A' CONNECT BY NOCYCLE PRIOR C = P
UNION
SELECT 'D','E' FROM DUAL
)FAKE_BOM
START WITH P ='A' CONNECT BY NOCYCLE PRIOR C = P
)
WHERE CYCLE_ERROR = 1;

0

--CHECK CYCLE BEFORE ADD NEW RECORD ('D','B') => CYCLE ERROR
SELECT COUNT(*) FROM
(
SELECT CONNECT_BY_ISCYCLE CYCLE_ERROR,P,C
FROM
(
SELECT P,C
FROM BOM
START WITH P ='A' CONNECT BY NOCYCLE PRIOR C = P
UNION
SELECT 'D','B' FROM DUAL
)FAKE_BOM
START WITH P ='A' CONNECT BY NOCYCLE PRIOR C = P
)
WHERE CYCLE_ERROR = 1;

1