How to change old data of table with before insert trigger in oracle?

Question

if new row is inserted into table, if duplicate rows already present i want to merge the two rows into one

table TEST ( ID column defined as primary key)

ID     ITEM     QUANTITY
--     ----     --------
1       KA1        5
2       KA2        2

if new row is inserted to table test with values (KA1,6), because item KA1 already present new row should be inserted with total quantity 11 and old row should be deleted .

Result set should be as the following :

ID     ITEM     QUANTITY
--     ----     --------
2       KA2        2
3       KA1       11

where used trigger and inline procedure are :

CREATE OR REPLACE TRIGGER  MERG_DUP
BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
  FOR VAL IN(SELECT ID,ITEM,QUANTITY, FROM TEST)
  LOOP
  IF VAL.ITEM=:NEW.ITEM THEN
            :NEW.QUANTITY:=:NEW.QUANTITY+VAL.QUANTITY;
           XXI_MULTI_PR_REMOVE(VAL.ID);
        EXIT;
  END IF;
  END LOOP;
end; 
/


CREATE  OR REPLACE PROCEDURE XXI_MULTI_PR_REMOVE(ID number)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
L_TID NUMBER;
BEGIN
 L_TID:=ID;
  DELETE FROM  TEST WHERE ID=L_TID;
commit;
END;
/

Answer 1

You do not need to use PRAGMA AUTONOMOUS_TRANSACTION, and do not use COMMIT inline inside your procedures.

For your case it's nice to CREATE TABLE test with ID column defined as number generated always as identity primary key.

Therefore, the statements in the following order may be used :

 SQL> CREATE TABLE test(
                  id       number generated always as identity primary key,
                  item     varchar2(100),
                  quantity int
 );
 /
 SQL> INSERT INTO test(item,quantity) VALUES ('KA1',5);
 SQL> INSERT INTO test(item,quantity) VALUES ('KA2',2);

 SQL> CREATE OR REPLACE PROCEDURE XXI_MULTI_PR_REMOVE( I_ITEM varchar2 ) IS
 BEGIN
  DELETE TEST WHERE ITEM = I_ITEM;
 END;
 /
 SQL> CREATE OR REPLACE TRIGGER MERG_DUP
 BEFORE INSERT ON TEST
 FOR EACH ROW
 DECLARE  
      v_qty NUMBER;
 BEGIN
    BEGIN
      SELECT SUM(NVL(QUANTITY,0)) INTO v_qty FROM TEST WHERE ITEM = :NEW.ITEM;
     EXCEPTION WHEN OTHERS THEN v_qty := NULL; 
    END;  
    IF ( v_qty IS NOT NULL ) THEN
        XXI_MULTI_PR_REMOVE(:NEW.ITEM);
       :NEW.QUANTITY:=:NEW.QUANTITY+v_qty;    
    END IF;
 END MERG_DUP;
 /
 SQL> INSERT INTO test(item,quantity) VALUES ('KA3',6);
 SQL> COMMIT;
 SQL> SELECT * FROM test;