This question is a follow up to another SO question.
I have an app with a REST Data Source taking in JSON responses from an API. There are two tables ORDERS and LINES. The ORDERS table contains a column LINES which is a JSON Document type.
I created a trigger on my ORDERS table which runs AFTER INSERT or UPDATE on my ORDERS table, and which maintains the LINES table (Extracts the JSON array from the ORDERS table ORDER_ITEMS column and inserts into ORDER_ITEMS table and each column).
create or replace trigger "TR_MAINTAIN_LINES"
AFTER
insert or update on "ORDERS"
for each row
begin
if inserting or updating then
if updating then
delete LINES
where order_number = :old.order_number;
end if;
insert into LINES ( order_number, product, quantity, price, total_price, item_image)
( select :new.order_number,
j.product,
j.quantity,
j.price,
TO_NUMBER(j.price,'$999.99'),
j.item_image
from json_table(
:new.lines,
'$[*]' columns (
product varchar2(4000) path '$.product',
quantity number path '$.quantity',
price varchar2(4000) path '$.price',
item_image varchar2(4000) path '$.item_image' ) ) j );
end if;
end;
I also have another table CUSTOMERS. This table is to contain customer data such as FIRST_NAME, LAST_NAME, and EMAIL.
I have merged the existing data from the ORDERS table to the CUSTOMERS table with the following:
merge into customers c
using (
select
first_name,
last_name,
email
from orders
) o
on (o.email = c.email)
when not matched then
insert (first_name, last_name, email)
values (o.first_name, o.last_name, o.email);
Now, I want to create a trigger so that the customer data inserts into CUSTOMERS table on a trigger (after insert or update on the ORDERS table). Is this possible within the trigger TR_MAINTAIN_LINES above or would I have to create a separate trigger?
SAMPLE JSON RESPONSE FROM API
[ {
"order_number": "so1223",
"order_date": "2022-07-01",
"first_name": "Carny",
"last_name": "Coulter",
"email": "[email protected]",
"credit_card": "3545556133694494",
"city": "Myhiya",
"state": "CA",
"zip_code": "12345",
"lines": [
{
"product": "Beans - Fava, Canned",
"quantity": 1,
"price": $1.99
},
{
"product": "Edible Flower - Mixed",
"quantity": 1,
"price": $1.50
}
]
},
]
Easiest is to just add it to the same trigger. Since you can't select from the orders table yet, use a select from dual. Like this:
merge into customers c
using (
select
:new.first_name,
:new.last_name,
:new.email
from dual
) o
on (o.email = c.email)
when not matched then
insert (first_name, last_name, email)
values (o.first_name, o.last_name, o.email);
On a side note, there is some redundant code in the trigger:
...
AFTER
--line below ensures the trigger only fires
-- on an insert or update operation
insert or update on "ORDERS"
for each row
begin
--if statement below will always yield true.
-- since trigger has "after insert or update"
-- It can be removed
if inserting or updating then
if updating then
...
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments