Oracle如何创建触发器实现同表反向ID插入且避免无限循环？

问题分析 #

你当前的触发器会引发无限循环的核心原因是：所有对m_to_n表的INSERT操作（包括触发器自身执行的INSERT）都会触发该AFTER INSERT触发器。比如插入(1,3)后，触发器插入(3,1)，这一插入操作又会触发触发器尝试插入(1,3)，最终因主键重复报错才终止循环。

解决方案 #

通过在触发器中添加两个关键判断，即可避免循环并实现需求：

• 排除ID_1和ID_2相等的自引用场景（避免插入重复主键的行）
• 仅当反转后的行不存在时，才执行插入操作

修改后的触发器代码 #

CREATE OR REPLACE TRIGGER m_to_n_reverse_insert
AFTER INSERT
ON m_to_n 
FOR EACH ROW
BEGIN
    -- 仅当不是自引用，且反转行不存在时执行插入
    IF :new.ID_1 != :new.ID_2 THEN
        INSERT INTO m_to_n (ID_1, ID_2)
        SELECT :new.ID_2, :new.ID_1
        FROM DUAL
        WHERE NOT EXISTS (
            SELECT 1 
            FROM m_to_n 
            WHERE ID_1 = :new.ID_2 AND ID_2 = :new.ID_1
        );
    END IF;
END;
/

逻辑说明 #

• 当你手动插入(1,3)时，触发器检查到(3,1)不存在，执行插入操作
• 触发器插入(3,1)时，会再次触发触发器，但此时检查到(1,3)已经存在，不会执行插入操作，循环直接终止
• 若插入的是自引用行（如(2,2)），因ID_1等于ID_2，触发器会直接跳过插入逻辑，避免主键冲突

内容的提问来源于stack exchange，提问作者Athshe