Trigger for update or insert row/values of other table if new vlaues in parent table

Question

I have two tables Test1 and Test 2.First table gets all the data from another job and second table is having unique data on base of ID column. I need to write a trigger where if any new row insert in the table test1 then need to check newly inserted row’s values of columns Price1 and Price2 for id if its new values then update in test2’s columns Price1 and Price2 for respective ID. IF that id not there in Test2 then simply insert there.

Table  : Test1
-----------------------------
|  ID  | Price1 |  Price2   |
-----------------------------
|   1  |  11    |  9        |
|   2  |  21    |  1        | 
|   2  |  33    |  2        |
-----------------------------



Table  : Test2
---------------------------------
|  ID(PK)  | Price1  |  Price2  |
---------------------------------
|   1      |  11     |    9     |
|   2      |  33     |    2     | 
---------------------------------

I tried below trigger but its not working

CREATE OR REPLACE TRIGGER  Test_Ord_Update   
AFTER  
insert or update on test1  
for each row  

DECLARE
   v_id1 NUMBER (5);

begin

 SELECT ID INTO v_id1
   FROM test1;
   
   update test2 set id = v_id1,  Price1 = new.Price1, Price2 = new.Price2 where id = v_id1;
  


end;

Answer 1

It is merge you need; at least, it does upsert which is what you want.

Sample tables:

SQL> desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 PRICE1                                             NUMBER
 PRICE2                                             NUMBER

SQL> desc test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 PRICE1                                             NUMBER
 PRICE2                                             NUMBER

Trigger:

SQL> create or replace trigger trg_aiu_test1
  2    after insert or update on test1
  3    for each row
  4  begin
  5    merge into test2 b
  6      using dual a
  7      on (:new.id = b.id)
  8      when matched then update set
  9        b.price1 = :new.price1,
 10        b.price2 = :new.price2
 11      when not matched then insert (id, price1, price2)
 12        values (:new.id, :new.price1, :new.price2);
 13  end;
 14  /

Trigger created.

Testing:

SQL> insert into test1 (id, price1, price2) values (1, 11, 9);

1 row created.

SQL> select * from test1;

        ID     PRICE1     PRICE2
---------- ---------- ----------
         1         11          9

SQL> select * from test2;

        ID     PRICE1     PRICE2
---------- ---------- ----------
         1         11          9

SQL> update test1 set price1 = 100 where id = 1;

1 row updated.

SQL> select * from test1;

        ID     PRICE1     PRICE2
---------- ---------- ----------
         1        100          9

SQL> select * from test2;

        ID     PRICE1     PRICE2
---------- ---------- ----------
         1        100          9

SQL>