表级数据同步：通过trigger实现

用trigger实现表级数据复制的过程，是在源端表上创建trigger，dblink，当源端数据有变化时触发trigger同步到目标端。

1.准备工作：

源、目标端
SQL> create user dbrep identified by dbrep;
SQL> grant connect,resource to dbrep;
SQL> grant create database link to dbrep;

conn dbrep/dbrep
create table replica_test1(
id number(10) primary key,
name varchar2(50),
job varchar2(50))
;
源库：
建立好网络连接，并创建dblink
conn dbrep/dbrep
SQL> create database link target_rep
    connect to dbrep identified by dbrep
    using 'DWH02';

2.创建trigger
create or replace trigger tri_rep_change
after insert or update or delete on replica_test1
for each row
begin
if inserting then
 insert into replica_test1@target_rep
 values(:new.id,:new.name,:new.job);
elsif updating then
 update replica_test1@target_rep
 set id=:new.id,name=:new.name,job=:new.job;
elsif deleting then
 delete from replica_test1@target_rep
 where id=:old.id;
end if;
end tri_rep_change;

3.测试：

--源
SQL> insert into replica_test1 values (1,'tan','dba');

1 row created.

SQL> commit;
--目标：
SQL> select * from replica_test1;

        ID NAME                                               JOB
---------- -------------------------------------------------- --------------------------------------------------
         1 tan                                                dba

测试删除也ok