PL/SQL: Using MERGE to Synchronize Data from a Table Trigger

The following script demonstrates how the MERGE statement can be utilized in a table
trigger to enable the synchronization of data between two similar tables.

CREATE TABLE test_trigger1(id number (12),
                           col1  varchar2(50),
                           col2  varchar2(50));

CREATE TABLE test_trigger2(id number (12),
                           col1  varchar2(50),
                           col2  varchar2(50));

CREATE OR REPLACE TRIGGER l_update_test
 BEFORE UPDATE OR INSERT
 ON test_trigger1
 FOR EACH ROW
BEGIN

   MERGE INTO test_trigger2 a
   USING(SELECT :new.id id,:new.col1 col1,:new.col2 col2 from dual) b
   ON (a.id = b.id)
   WHEN MATCHED THEN
     UPDATE SET
     a.col1 = b.col1,
     a.col2 = b.col2
   WHEN NOT MATCHED THEN
     INSERT(id,col1,col2) VALUES (b.id,b.col1,b.col2);
     

END;
/

INSERT INTO test_trigger1 VALUES (1,'Test1','Test2');
INSERT INTO test_trigger1 VALUES (2,'Test3','Test4');

COMMIT;

SELECT * FROM test_trigger2;

UPDATE test_trigger1 SET col1 = 'This worked' WHERE id=2;
COMMIT;

SELECT * FROM test_trigger2;

9:21:07 AM