CODEWITHSUNDEEP
plsqloracle-apex
ckp7blessed
ckp7blessed

Reputation: 135

Oracle Apex 22.21 - REST data source - nested JSON array - sync two tables by trigger - PLSQL error question

This question is a follow up to another SO question.

I've actually recreated the tables from the previous question. The updated JSON response can be found at the bottom of this question.

ORDERS_LOCAL table

ORDERS_LOCAL table

ORDERS_LOCAL table data. ORDER_ITEMS column is the JSON array that I need to extract into ORDER_ITEMS_LOCAL table.

ORDERS_LOCAL table data

ORDER_ITEMS_LOCAL table. LINE_ID column should be created automatically. ORDER_ID column is a foreign key to ORDERS_LOCAL table. PRODUCT_ID column is a foreign key to PRODUCTS table. LINE_NUMBER is just the order line number (line 1 = product 1, price, qty | line 2 = product 2, price, qty etc..) I believe it's called a sequence type?

ORDER_ITEMS_LOCAL table

PRODUCTS table

PRODUCTS TABLE

PRODUCTS table data

PRODUCTS Table Data

Per Carsten's answer, I've created a new trigger for the ORDERS table from the Object Browser.

Create Trigger from Object Brower

I've then entered Carsten's PLSQL code from the previous question. He did mention that it was pseudo-code. So I tried to update it..

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 ( line_id, order_id, line_number, product_id, quantity, price) 
        ( select :new.id,
                 seq_lines.nextval,
                 j.line_number,
                 j.product_id,
                 j.quantity,
                 j.price
            from json_table( 
                     :new.order_items,
                     '$[*]' columns (
                         line_number for ordinality,
                         product_id  number path '$.product_id',
                         quantity number        path '$.quantity',
                         price    number        path '$.price' ) ) );
    elsif deleting then
        delete ORDER_ITEMS_LOCAL
        where order_id = :old.id;
    elsif updating then
        delete ORDER_ITEMS_LOCAL
        where order_id = :old.id;
        -- 
        -- handle the update case here.
        -- I would simply delete and re-insert LINES rows.
    end if;
end;

I am receiving the following errors

Compilation failed, line 4 (08:38:57) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00049: bad bind variable 'NEW.ID'Compilation failed, line 19 (08:38:57) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00049: bad bind variable 'OLD.ID'Compilation failed, line 22 (08:38:57) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00049: bad bind variable 'OLD.ID'

I believe this is due to missing columns in the trigger code but I'm not sure.

I am new to PLSQL and parsing the JSON is kind of confusing.. especially below. Please see my comments.

    if inserting then
        insert into ORDER_ITEMS_LOCAL ( line_id, order_id, line_number, product_id, quantity, price) 
        ( select :new.id,                  -- is this new id for `line_id`
                 order_id                  -- how to insert order_id foreign key
                 seq_lines.nextval,        -- not sure what this is for?
                 j.line_number,            -- I changed 'lines' to 'order_items' so should this be seq_order_items.nextval, ?
                 j.product_id,             
                 j.quantity,
                 j.price
            from json_table( 
                     :new.order_items,           -- I changed 'lines' to 'order_items' so I changed this from :new.lines,
                     '$[*]' columns (            -- Would I include 'line_id' and 'order_id' in here as well?
                         line_number for ordinality,
                         product_id  number path '$.product_id',
                         quantity number        path '$.quantity',
                         price    number        path '$.price' ) ) );

Updated JSON response

[
{
    "order_id": "HO9b6-ahMY-B2i9",
    "order_number": 34795,
    "order_date": "2022-11-02",
    "store_id": 2,
    "full_name": "Ronda Perfitt",
    "email": "[email protected]",
    "city": "Fresno",
    "state": "California",
    "zip_code": "93762",
    "credit_card": "5108758574719798",
    "order_items": [
      {
        "line_number": 1,
        "product_id": 2,
        "quantity": 1,
        "price": 3418.85
      },
      {
        "line_number": 2,
        "product_id": 7,
        "quantity": 1,
        "price": 4070.12
      }
    ]
  },
    {
    "order_id": "RFvUC-sN8Y-icJP",
    "order_number": 62835,
    "order_date": "2022-10-09",
    "store_id": 1,
    "full_name": "Wash Rosenfelt",
    "email": "[email protected]",
    "city": "Chicago",
    "state": "Illinois",
    "zip_code": "60646",
    "credit_card": "5048372443777103",
    "order_items": [
      {
        "line_number": 1,
        "product_id": 1,
        "quantity": 1,
        "price": 3349.05
      },
      {
        "line_number": 2,
        "product_id": 3,
        "quantity": 1,
        "price": 4241.29
      },
      {
        "line_number": 3,
        "product_id": 1,
        "quantity": 1,
        "price": 3560.03
      }
    ]
  },
]

I apologize for making this confusing. I really want to learn how to do this right. Your support is much appreciated. Thank you.

Upvotes: 0

Views: 290

Answers (1)

Carsten
Carsten

Reputation: 1114

In the trigger code, the :old and :new prefixes reference the row of your table, before and after the trigger operation. So ...

  • 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.
  • In the INSERTING case, there is no :old.{column-name} (thus that would be NULL); :new.{column-name} references the inserted value.
  • And in the DELETING case, there is no :new.{column-name} value; only :old.{column-name} is available.

You see the compiler error, as my trigger pseudo-code contained :new.id, but your table does not have a column named ID; it's ORDER_ID in your case. So you need to adjust that code accordingly.

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-triggers.html#GUID-E76C8044-6942-4573-B7DB-3502FB96CF6F

Upvotes: 2

Related Questions