Oracle触发器：阻止插入指定表同时插入错误表的实现难题

我明白你现在遇到的困境：想通过触发器阻止向Condo_Assign表插入数据，同时把错误信息存入另一张错误表，但用RAISE_APPLICATION_ERROR时，连错误表的插入也被回滚了。这其实是Oracle事务机制导致的，默认情况下触发器和主插入操作在同一个事务里，抛出错误会回滚整个事务。不过用自治事务就能完美解决这个问题，让我一步步给你讲清楚：

第一步：先确保错误表存在（如果还没创建） #

假设你的错误表叫Condo_Assign_Error，可以用下面的语句创建（你可以根据实际需求调整字段）：

CREATE TABLE Condo_Assign_Error (
    error_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    mid INT,
    rid VARCHAR2(3),
    error_message VARCHAR2(200),
    error_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP
);

第二步：带自治事务的触发器实现 #

核心是在触发器里声明PRAGMA AUTONOMOUS_TRANSACTION;，让错误表的插入操作独立于主事务，这样就算主事务被回滚，错误记录也能保留。下面是完整的触发器代码：

CREATE OR REPLACE TRIGGER trg_block_condo_assign
BEFORE INSERT ON Condo_Assign
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION; -- 关键：开启自治事务
    v_error_msg VARCHAR2(200);
BEGIN
    -- 这里替换成你实际的阻止条件（比如检查MID是否存在、RID是否合规等）
    -- 示例条件：如果MID在SkiClub表中不存在，就阻止插入
    IF :NEW.MID NOT IN (SELECT MID FROM SkiClub) THEN
        -- 构造错误信息
        v_error_msg := '插入失败：MID ' || :NEW.MID || ' 在SkiClub表中不存在';
        
        -- 向错误表插入记录
        INSERT INTO Condo_Assign_Error (mid, rid, error_message)
        VALUES (:NEW.MID, :NEW.RID, v_error_msg);
        
        -- 提交自治事务：这一步必须做，否则错误记录不会被保存
        COMMIT;
        
        -- 抛出错误，阻止主表的插入操作
        RAISE_APPLICATION_ERROR(-20001, v_error_msg);
    END IF;
END;
/

为什么之前的方法不行？ #

默认情况下，触发器和触发它的INSERT语句属于同一个事务。当你调用RAISE_APPLICATION_ERROR时，Oracle会回滚整个事务——包括你在触发器里对错误表的插入操作。而自治事务是独立于主事务的小事务，提交后错误记录就会被永久保存，之后再抛出错误只会回滚主事务（也就是Condo_Assign的插入），不会影响已经提交的错误记录。

注意事项 #

• 自定义错误代码要在-20000到-20999之间，这是Oracle预留的用户自定义错误范围
• 自治事务要谨慎使用，它的提交/回滚和主事务完全独立，不要在里面做和主事务强关联的操作
• 一定要在自治事务里执行COMMIT，否则错误表的插入操作会随着主事务回滚消失

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