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;
/

Deleting the existing row and incrementing the primary key is not a wise option, rather you could simply update quantity with a merge into. — Kaushik Nayak
im not incrementing the primary key , new row is inserted with that key , im deleting the old row , old row quantity should be added to new row if both item are same — Guru
So, that means there is a sequence/ trigger for generating primary keys am I right? or identity column? — Kaushik Nayak
Have you tried anything yet? Edit the question and add part of the code that you have tried so far.. Also remember that to change display of sample data set /code, you need not put html tags. Simply use the {} symbol on the toolbar to change the display format. — Kaushik Nayak

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2018-04-26T10:47:58

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;

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

1 Answers