Trigger selecting child records, multiplying their values and updating parent record

Question

I am a PL/SQL newbie and I'm struggling with a trigger.

Description:

I have three objects - PRODUCT, CONTAINS, ORDER. One product can have many CONTAINS and one ORDER can have many CONTAINS (basically it used to be Many-to-many relationship between PRODUCT and ORDER).

Each Product has a column "value", each CONTAINS has a column "amount" and each ORDER has a column "total".

When I add a new PRODUCT to ORDER via creating new CONTAINS, I want to recalculate field "total" on ORDER.

Example: PRODUCT X has "value" of 100. PRODUCT Y has "value" of 200. We have an ORDER O. Now I create CONTAINS between Product X and ORDER O with column "amount" of 5. Now the trigger should multiply 5 * 100 and update the ORDER column "total" to 500. Then I create CONTAINS between PRODUCT Y and ORDER O with column "amount" of 10. Now the trigger should recalculate 5 * 100 + 10 * 200 and update the "total" column on ORDER O to 2500.

My faulty trigger:

    create or replace TRIGGER TRIGGER1 
AFTER DELETE OR INSERT OR UPDATE OF AMOUNT, PRODUCT_ID_PRODUCT, ORDER_ID_ORDER ON CONTAINS 
REFERENCING NEW AS n
FOR EACH ROW
DECLARE
value number;
amount number;
total number;
BEGIN
LOOP
FOR emp IN (SELECT AMOUNT, PRODUCT_ID_PRODUCT, ORDER_ID_ORDER FROM CONTAINS WHERE ORDER_ID_ORDER = :n.ORDER_ID_ORDER) 
LOOP
(SELECT SUM(VALUE) into product FROM PRODUCT WHERE ID_PRODUCT = :emp.PRODUCT_ID_PRODUCT);
amount:= emp.AMOUNT;
total:= total + (product * amount);
UPDATE ORDER SET ORDER.TOTAL = total WHERE ID_ORDER = :n.ORDER_ID_ORDER;
END LOOP;
END LOOP;
END;

EDIT: The error shows on here:

(SELECT SUM(VALUE) into product FROM PRODUCT WHERE ID_PRODUCT = :emp.PRODUCT_ID_PRODUCT)

saying I can't use "emp".

EDIT2: Error message:

10/2 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe <an alternat 10/89 PLS-00103: Encountered the symbol ")" when expecting one of the following: . ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || indicator multiset 15/5 PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: ;

Answer 1

Simplified the trigger by removing loops/cursors that isn't actually required.

create or replace TRIGGER TRIGGER1 
AFTER DELETE OR INSERT OR UPDATE OF AMOUNT, PRODUCT_ID_PRODUCT, ORDER_ID_ORDER 
ON CONTAINS 
REFERENCING NEW AS n
FOR EACH ROW
DECLARE

lv_total number;

BEGIN

SELECT SUM(prdt.VALUE * :n.amount) into lv_total 
FROM PRODUCT prdt where prdt.ID_PRODUCT = :n.PRODUCT_ID_PRODUCT;


UPDATE ORDERs SET TOTAL = lv_total WHERE ID_ORDER = :n.ORDER_ID_ORDER;

END;

Refer DB Fiddle link for solution :https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3be867f6ab2e93978ae45a7d305434a1

PS:Triggers can cause performance bottleneck at time when the DMLs in the triggers are not tuned well enough.Recommendation is to check the explain plan for SELECT,INSERT,UPDATE statements inside a trigger and tune them as desired.If Indexes are not available for CONTAINS.ORDER_ID_ORDER and PRODUCTS.ID_PRODUCT creating one would be beneficial but would recommend consulting with DBA in-charge.

UPDATE : Now since you need to Select from the table on which trigger is fired we have to live with famous Mutating trigger error ORA-04091: table MYTABLE.CONTAINS is mutating, trigger/ and luckily Oracle has an easy solution for it using Compound trigger that was added from Oracle Database 11g Release1 version onwards.

For more details and technical explanation on Compound Trigger you may refer http://stevenfeuersteinonplsql.blogspot.com/2016/12/get-rid-of-mutating-table-trigger.html

Trigger Code goes like this, ta.da.. So we take rows to a pl/sql table for row operation and perform statement operation for each of the rows from the pl/sql table.

CREATE OR REPLACE TRIGGER trigger2    
FOR UPDATE OR INSERT ON contains    
COMPOUND TRIGGER     

   TYPE typ_contains IS TABLE OF contains%rowtype  INDEX BY PLS_INTEGER;    
   tab_contains   typ_contains;    
    
   AFTER EACH ROW IS    
   BEGIN  
      tab_contains (tab_contains.COUNT + 1).amount :=    
           :NEW.amount;    
      tab_contains (tab_contains.COUNT).product_id_product := :NEW.product_id_product;
      tab_contains (tab_contains.COUNT).order_id_order := :NEW.order_id_order;
      
   END AFTER EACH ROW;    
    
   AFTER STATEMENT IS    
   lv_total number;
   
   BEGIN        
       
      FOR indx IN 1 .. tab_contains.COUNT    
      LOOP   
      
       SELECT SUM(prdt.VALUE * tab_contains(indx).amount) into lv_total 
       FROM PRODUCT prdt,contains cnts
       where cnts.order_id_order = tab_contains(indx).order_id_order 
       and prdt.id_product = cnts.product_id_product;

      UPDATE ORDERs SET TOTAL = lv_total 
      WHERE ID_ORDER = tab_contains(indx).ORDER_ID_ORDER;
                                     
      END LOOP;    
   END AFTER STATEMENT;    
END trigger2; 
/

Updated solution can be found in DBfiddle link https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=1fb40eef7cf3a647bc5560ed19490240