Oracle Apex删除ORDERS记录触发ORA-04091表变异错误

错误原因 #

ORA-04091错误的根源是：原触发器采用行级AFTER DELETE逻辑，在删除ORDERS_LOCAL行的同时直接操作ORDER_ITEMS_LOCAL表。此时触发表ORDERS_LOCAL处于变异状态（事务尚未完成，表数据处于不稳定状态），Oracle会阻止这种可能引发数据不一致的跨表操作。

解决方案：改用复合触发器 #

复合触发器可以将行级数据捕获与语句级批量操作分离，避免表变异问题。具体逻辑是：

• 行级阶段：捕获需要删除的order_id，暂存到集合中，不直接操作ORDER_ITEMS_LOCAL
• 语句级阶段：在整个删除语句执行完成后，批量删除ORDER_ITEMS_LOCAL中对应的订单项

完整触发器代码 #

create or replace trigger "TR_MAINTAIN_LINES"
FOR INSERT OR UPDATE OR DELETE ON "ORDERS_LOCAL"
COMPOUND TRIGGER
    -- 定义集合存储待删除的order_id
    TYPE t_order_ids IS TABLE OF NUMBER;
    v_order_ids t_order_ids := t_order_ids();

    -- 行级逻辑：处理插入/更新，捕获删除的order_id
    AFTER EACH ROW IS
    BEGIN
        IF INSERTING OR UPDATING THEN
            -- 更新时先清理旧order_id对应的订单项
            IF UPDATING THEN
                DELETE ORDER_ITEMS_LOCAL WHERE order_id = :old.order_id;
            END IF;
            -- 解析JSON插入新订单项
            INSERT INTO ORDER_ITEMS_LOCAL (order_id, line_id, line_number, product_id, quantity, price)
            SELECT :new.order_id,
                   seq_line_id.nextval,
                   j.line_number,
                   j.product_id,
                   j.quantity,
                   j.price
            FROM json_table(
                     :new.order_items,
                     '$[*]' columns (
                         line_id FOR ORDINALITY,
                         line_number NUMBER PATH '$.line_number',
                         product_id NUMBER PATH '$.product_id',
                         quantity NUMBER PATH '$.quantity',
                         price NUMBER PATH '$.price' ) ) j;
        ELSIF DELETING THEN
            -- 将待删除的order_id加入集合
            v_order_ids.extend;
            v_order_ids(v_order_ids.last) := :old.order_id;
        END IF;
    END AFTER EACH ROW;

    -- 语句级逻辑：批量删除订单项
    AFTER STATEMENT IS
    BEGIN
        IF v_order_ids.count > 0 THEN
            DELETE ORDER_ITEMS_LOCAL
            WHERE order_id MEMBER OF v_order_ids;
        END IF;
    END AFTER STATEMENT;
END;

方案说明 #

1. 行级阶段仅完成数据捕获和插入/更新逻辑，删除操作延迟到语句级执行
2. 语句级阶段在整个删除事务完成后执行，此时触发表ORDERS_LOCAL已脱离变异状态，Oracle允许对ORDER_ITEMS_LOCAL进行修改
3. 批量删除操作比逐行删除更高效，同时避免了行级操作带来的锁冲突问题

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