CODEWITHSUNDEEP
plsqloracle-apex
ckp7blessed
ckp7blessed

Reputation: 135

Oracle Apex - REST data source - nested JSON array - trigger two tables - update function

This question is a follow up to another SO question.

I've followed Carsten's instructions on the previous question. The trigger works as expected. I now just need to figure out how to handle the updating function of the trigger.

create or replace trigger "TR_MAINTAIN_LINES"
AFTER
insert or update or delete on "ORDERS_LOCAL"
for each row
begin
    if inserting then
        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;
    elsif updating then
        delete ORDER_ITEMS_LOCAL
        where order_id = :old.order_id;
        -- 
        -- handle the update case here.
        -- I would simply delete and re-insert ORDER_ITEMS rows.
    end if;
end;

Carsten did mention in his answer to a previous question:

"In the UPDATING case, :old.{column-name} references the value of a table column column before the update, :new.{column-name} references the value after the update."

    elsif updating then
        delete ORDER_ITEMS_LOCAL
        where order_id = :old.order_id;
        -- 
        -- handle the update case here.
        -- I would simply delete and re-insert ORDER_ITEMS rows.

    elsif updating then
        delete ORDER_ITEMS_LOCAL
        where order_id = :old.order_id;
        insert into ORDER_ITEMS_LOCAL ( order_id, line_id, line_number, product_id, quantity, price) 
        ( select :new.order_id,
                 seq_line_id.nextval,
                 :new.line_number,
                 :new.product_id,
                 :new.quantity,
                 :new.price )

Upvotes: 0

Views: 91

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18665

I'm assuming that the update could potentially mean a new json value in ORDERS_LOCAL.ORDER_ITEMS. In that case the json needs to be parsed just like for the insert. Combine both inserting and deleting in a single if statement to avoid duplicate code.

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;

Upvotes: 1

Related Questions