Failing to produce or update a table row using a trigger in SQL

Question

I am trying to create a trigger in Oracle SQL 12c.

The concept is that i have two tables, a storage table (Storage) that holds items and a delivery table (Supplier_Orders) that notes how many items(Units) are being delivered to the storage. The trigger is supposed to detect whether an item that is being delivered to the storage (new_product_id) already exists in the storage(product_id), and if not it automatically adds it and also asks the user to input some mandatory values(barcode and sell_price). If it does exist, it updates the table by adding the items being delivered (Units) to the total of items(remaining_units).

create or replace trigger SL_ORD_ADDER_TR
before insert on Supplier_Orders
for each row
begin
if :new.new_product_id in (select s.product_id from storage s where (:new.new_product_id=s.product_id)) then
update storage
set remaining_units = remaining_units + new.units
where new.new_product_id=product_id;
end if;
if :new.new_product_id not in(select s.product_id from storage s where (:new.new_product_id=s.product_id)) then 
insert into storage(product_id,product_name,remaining_units,barcode,sell_price_per_unit) values(new.new_product_id,new.product_name,new.units,'&barcode','&sell price');
end if;
end;

The errors produced are the following:

Error(5,5): PL/SQL: Statement ignored

Error(5,31): PLS-00405: subquery not allowed in this context

Error(10,5): PL/SQL: Statement ignored

Error(10,34): PLS-00405: subquery not allowed in this context

Answer 1

The Subquery is not allowed at PL/SQL, after IF condition in your code. It's allowed only in SQL.

You can use a variable to check the "product_id" in the "storage" table and determine the IF condition on it.

    CREATE OR REPLACE TRIGGER SL_ORD_ADDER_TR
    BEFORE INSERT on Supplier_Orders
    REFERENCING
    NEW AS new
    OLD AS old
    for each row
    DECLARE
            -- New variable
            v_cnt number;
    begin
        select count(s.product_id) into v_cnt from storage s where s.product_id=:new.new_product_id;
    if v_cnt>0 
        -- It exists, so updating
        then
            update storage
            set remaining_units = remaining_units + :new.units
            where product_id=:new.new_product_id;
    end if;
    if v_cnt=0 
        -- It not exists, so inserting
        then 
        insert into storage(product_id,product_name,remaining_units,barcode,sell_price_per_unit) 
        values(:new.new_product_id,:new.product_name,:new.units,'&barcode','&sell price');
    end if;
    end;