oracle使用快照和触发器同步数据

假设有数据库db1和db2 , 表db1.t_task_msg, db2.t_task_msg
现在同步db1.t_task_msg数据到db2.t_task_msg

1, 在db2建立到db1的连接source_link

  create database link source_link
　　 connect to db1_user identified by db1_pwd   
　　 using
        '(DESCRIPTION =
           (ADDRESS_LIST =
             (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
           )
          (CONNECT_DATA =
             (SERVICE_NAME = orcl)
          )
       )';

2,在db1上创建要同步的表的快照日志

   create snapshot log on t_task_msg;

3,在db2上创建快照并设置刷新时间

   create snapshot sn_t_task_msg
      refresh fast Start with sysdate next sysdate with primary key
      as select * from t_task_msg@source_link;
  或
  create snapshot sn_t_task_msg as select * from t_task_msg@source_link;
    Alter snapshot sn_t_task_msg refresh fast Start with sysdate next sysdate with primary key;

4,创建一个触发器, 当快照有更新时, 更新db1中的数据

create or replace trigger tr_t_task_msg
  after  insert or update or delete on sn_t_task_msg
  for each row
begin
  if deleting then
      delete from t_task_msg where fid=:old.fid;
  end if;
  if inserting then
      insert into t_task_msg(fid,fserviceid,fcontent,fuserid,fstate,finserttime)
      values(:new.fid,:new.fserviceid,:new.fcontent,:new.fuserid,:new.fstate,:new.finserttime);
  end if;
  if updating then
     update t_task_msg set fserviceid=:new.fserviceid, fcontent=:new.fcontent, fuserid=:new.fuserid, fstate=:new.fstate, finserttime=:new.finserttime where fid=:old.fid;
  end if;
end tr_t_task_msg;

----本文来Net探索者，http://www.codefinds.net 转载请说明出处!