SYNCHRONIZING IDENTICAL TABLES AT DIFFERENT DATABASES USING TRIGGERS

Steps

Consider SOURCE@PRIMARY as schema containing master table (EMP) and TARGET@DUPLICATE as schema containing duplicate table (EMP).

1. Create EMP table at SOURCE@PRIMARY

CREATE TABLE EMP(NAME VARCHAR2(20),SAPID NUMBER(8) PRIMARY KEY);

2. Create duplicate table other database and table include only primary key constraint

CREATE TABLE EMP(NAME VARCHAR2(20),SAPID NUMBER(8) PRIMARY KEY);

3. Create database link DATALINK at SOURCE@PRIMARY

CREATE DATABASE LINK DATALINK CONNECT TO TARGET IDENTIFIED BY TARGETPASS USING 'DUPLICATE';

4. Check database link is properly set at SOURCE@PRIMARY

INSERT INTO EMP VALUES ('JANE',5);

COMMIT;

INSERT INTO EMP@DATALINK SELECT * FROM EMP;

COMMIT;

SELECT * FROM EMP@DATALINK;

5. Create a trigger on the table EMP at SOURCE@PRIMARY. In the below example before insert/update/delete trigger is created.

CREATE OR REPLACE TRIGGER BEFORE_INSERT_TRG

   BEFORE INSERT OR UPDATE OR DELETE ON EMP

   FOR EACH ROW

 BEGIN

   IF INSERTING THEN

     INSERT INTO EMP@DATALINK (NAME,SAPID) VALUES (:NEW.NAME,:NEW.SAPID);    

   ELSIF UPDATING THEN

         UPDATE EMP@DATALINK SET NAME=:NEW.NAME,SAPID=:NEW.SAPID WHERE SAPID=:OLD.SAPID;   --give primary key column in where condition

   ELSIF DELETING THEN

      DELETE FROM EMP@DATALINK WHERE SAPID=:OLD.SAPID; --give primary key COLUMN IN WHERE CONDITION.

   END IF;

 END;

6. Check the trigger

INSERT INTO EMP VALUES ('ROBERT',3);

UPDATE EMP SET NAME = 'SHAWNS' WHERE NAME='ROBERT'; -- Also check multiple row update

DELETE FROM EMP WHERE SAPID=1;

Note:

Since DDLs can’t be executed through Database links, structural changes on tables need to be done manually for the synch to happen properly.