Oracle触发器更新表遇ORA-04091突变错误，求解决办法

你尝试创建触发器，在CONTRACTOR_LOT表插入或更新记录后，自动更新该记录的LABEL和FRAMEWORK_CONTRACT_NUMBER_LABEL列，原代码如下：

CREATE OR REPLACE TRIGGER "TRIGGER_UPDATE_CONTRACTOR_LOT"
AFTER INSERT OR UPDATE ON "CONTRACTOR_LOT"
FOR EACH ROW
DECLARE
    CONTRACT_LOT_LABEL VARCHAR2(255 BYTE);
    CONTRACTOR_LABEL VARCHAR2(200 BYTE);
BEGIN

       SELECT LABEL INTO CONTRACT_LOT_LABEL FROM LOT_T WHERE ID = :NEW.LOT_ID;
       SELECT CONTRACTOR INTO CONTRACTOR_LABEL FROM CONTRACTOR_T WHERE ID = :NEW.CONTRACTOR_ID;

       UPDATE CONTRACTOR_LOT
        SET LABEL = CONTRACT_LOT_LABEL  || ':' || CONTRACTOR_LABEL,
            FRAMEWORK_CONTRACT_NUMBER_LABEL = :NEW.ORDER || ':' || CONTRACT_LOT_LABEL  || :NEW.FRAMEWORK_CONTRACT_NUMBER
        WHERE ID = :NEW.ID;  
END;

执行后遇到**ORA-04091（表突变）错误，添加PRAGMA AUTONOMOUS_TRANSACTION;并在UPDATE后加COMMIT;又触发ORA-00060（死锁）**错误。

核心问题分析 #

• ORA-04091：AFTER触发器触发时，原表的行锁还未释放，此时再次UPDATE触发表，Oracle会判定表处于"突变"状态，禁止此类操作。
• ORA-00060：自治事务是独立于主事务的单独事务，主事务持有CONTRACTOR_LOT行锁，自治事务又尝试获取同一行锁，导致锁冲突死锁。

正确实现方式 #

改用BEFORE INSERT OR UPDATE触发器，直接修改:NEW伪记录的字段值，无需执行UPDATE语句。BEFORE触发器在数据写入表之前执行，操作的是待插入/更新的行数据，不会触发表突变，也不需要自治事务。

修改后的代码：

CREATE OR REPLACE TRIGGER "TRIGGER_UPDATE_CONTRACTOR_LOT"
BEFORE INSERT OR UPDATE ON "CONTRACTOR_LOT"
FOR EACH ROW
DECLARE
    CONTRACT_LOT_LABEL VARCHAR2(255 BYTE);
    CONTRACTOR_LABEL VARCHAR2(200 BYTE);
BEGIN
    -- 关联查询获取需要的标签值
    SELECT LABEL INTO CONTRACT_LOT_LABEL FROM LOT_T WHERE ID = :NEW.LOT_ID;
    SELECT CONTRACTOR INTO CONTRACTOR_LABEL FROM CONTRACTOR_T WHERE ID = :NEW.CONTRACTOR_ID;

    -- 直接赋值给:NEW伪记录，无需UPDATE表
    :NEW.LABEL := CONTRACT_LOT_LABEL || ':' || CONTRACTOR_LABEL;
    :NEW.FRAMEWORK_CONTRACT_NUMBER_LABEL := :NEW.ORDER || ':' || CONTRACT_LOT_LABEL || :NEW.FRAMEWORK_CONTRACT_NUMBER;
END;

补充说明 #

如果LOT_T或CONTRACTOR_T可能存在无匹配ID的情况，建议添加NO_DATA_FOUND异常处理，避免触发器执行失败导致主事务回滚。示例：

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 根据业务需求处理，比如赋值默认值或抛出明确错误
        :NEW.LABEL := '未知批次:' || :NEW.CONTRACTOR_ID;
        :NEW.FRAMEWORK_CONTRACT_NUMBER_LABEL := '未知订单:' || :NEW.FRAMEWORK_CONTRACT_NUMBER;

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