Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE	11.2.0.2.0	Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


create table contains(amount number,product_id_product varchar2(10),
order_id_order varchar2(10));
create table product(id_product varchar2(10),value number);
create table orders(id_order varchar2(10),total number);
/*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;
*/
ORA-01742: comment not terminated properly

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; 
/
select * from user_errors;
insert into product values(1,100);
1 rows affected

insert into product values(2,200);
1 rows affected

insert into orders values('O',2);
1 rows affected

insert into orders values('X',5);
1 rows affected

insert into contains values(100,1,'O');
1 rows affected

insert into contains values(200,2,'O');
1 rows affected

select * from orders;


ID_ORDER
TOTAL


O
60000

X
5


insert into contains values(200,2,'O');
1 rows affected

select * from orders;


ID_ORDER
TOTAL


O
100000

X
5


/*ORDER O has 2 CONTAINS records. 
Each CONTAINS record has different PRODUCT record. If I make a change on 
one CONTAINS record, it calculates only itself. So instead of 
let's say Amount = 1 times Product value = 100 and other CONTAINS record 
has Amount 10 times Product value = 5000 which amkes together 50 100, 
it calculates only 1 times 100
as if the other product of the ORDER does not exist*/
ORA-01742: comment not terminated properly