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

Answer 2

I think there is a mistake:

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

Instead of :emp. you should use :n.

--- UPDATE Remove brackets in this SQL statement. You don't need them Add variable PRODUCT Change the name of variable TOTAL eg nTotal (it mismatch with column name)

Answer 3

You have several issues, but the most fundamental one is that you should not be doing this at all. Trying to store - and keep in synch - a value that can always be calculated is a fundamental design flaw.

Now, to the code itself. You have

SELECT SUM(VALUE) into product

The target of your INTO must be a declared variable. Looks like you are trying to SELECT .. INTO a column name.

You should name local variables to distinguish between them an column names. Thus, instead of

DECLARE
value number;
amount number;
total number;

You should have

DECLARE
v_value number;
v_amount number;
v_total number;

Conversely, you should think about standard naming conventions for your tables and columns. For columns I use and recommend names in the form of <adjective_noun>, thus ORDER_ID, PRODUCT_NAME, etc. What's this with PRODUCT_ID_PRODUCT, ORDER_ID_ORDER ? Repeating the table name in the column names is usually not beneficial. Though there are times it makes sense because it still follows the adjective_noun format, like the id column of the ORDERS table being named ORDER_ID. Think about table names also, I usually make my table names a plural noun because tables track multiple instances of some entity. If the table name makes sense for a column name (like ORDER_ID) it would be singular, because an individual row tracks a single instance of the entity.

Lastly, it is hard to recommend coding modification without knowing the tables. You've given a vague description of them, but better to lay everything on the table. See minimal-reproducible-example