Oracle Apex - REST 数据源 - 嵌套 JSON 数组 - 触发两个表 - 删除函数错误 ORA-04091

这个问题是另一个问题的后续所以问题 https://stackoverflow.com/questions/75219903/oracle-apex-rest-data-source-nested-json-array-trigger-two-tables-update.

我有一个带有 REST 数据源的应用程序，它接收来自 API 的 JSON 响应。有两张桌子ORDERS and ORDER_ITEMS. The ORDERS表包含一列ORDER_ITEMS这是一个JSON文档 type.

我在我的上创建了一个触发器ORDERS运行的表AFTER INSERT, UPDATE or DELETE on my ORDERS表，并维护ORDER_ITEMS表（从ORDERS table ORDER_ITEMS列并插入ORDER_ITEMS表和每列）。

create or replace trigger "TR_MAINTAIN_LINES"
AFTER
insert or update or delete on "ORDERS_LOCAL"
for each row
begin
    if inserting or updating then
        if updating then
          delete ORDER_ITEMS_LOCAL
          where order_id = :old.order_id;
        end if;
        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
        delete ORDER_ITEMS_LOCAL
        where order_id = :old.order_id;
    end if;
end;

触发器适用于AFTER INSERT and UPDATE。然而，当我尝试DELETE一行从ORDERS表，我收到错误。error ORA-04091: table WKSP_DEMO.ORDER_ITEMS_LOCAL is mutating, trigger/function may not see it

但从其中删除一行ORDER_ITEMS表按预期工作。

• ORDERS table 在此输入图像描述 https://i.stack.imgur.com/CsqRW.png

• ORDERS表数据在此输入图像描述 https://i.stack.imgur.com/nuK7M.png

• ORDER_ITEMS table 在此输入图像描述 https://i.stack.imgur.com/10XkG.png

• ORDER_ITEMS表数据在此输入图像描述 https://i.stack.imgur.com/M4TrI.png

• 触发器在此输入图像描述 https://i.stack.imgur.com/12kU8.png

• BI_ORDER_ITEMS_LOCAL trigger

create or replace trigger "BI_ORDER_ITEMS_LOCAL"   
  before insert on "ORDER_ITEMS_LOCAL"               
  for each row  
begin   
  if :NEW."LINE_ID" is null then 
    select "ORDER_ITEMS_LOCAL_SEQ".nextval into :NEW."LINE_ID" from sys.dual; 
  end if; 
end; 

• 错误信息在此输入图像描述 https://i.stack.imgur.com/SoLLC.png

- - 更新 - - -

所以我拿出了触发器的删除部分ORDER and ORDER_LINES表行按预期删除。

create or replace trigger "TR_MAINTAIN_LINES"
AFTER
insert or update on "ORDERS_LOCAL"
for each row
begin
    if inserting or updating then
        if updating then
          delete ORDER_ITEMS_LOCAL
          where order_id = :old.order_id;
        end if;
        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 );

    end if;
end;

ON DELETE CASCADE 子句和触发器逻辑之间存在冲突。在 DELETE 情况下，两者都尝试删除子行。你可以 ...

• 从触发器中删除 DELETE 部分并让 ON DELETE CASCADE 子句完成这项工作
• 从外键中删除 ON DELETE CASCADE 子句。

我想，我会推荐第二种选择，这样所有的逻辑都集中在一个地方，即你的触发器。