用触发器同步2个实例之间的表

最新推荐文章于 2022-12-28 19:06:29 发布

wencsa2004

最新推荐文章于 2022-12-28 19:06:29 发布

阅读量246

点赞数

文章标签： integer insert database delete exception service

. 在2个实例之间同步表的数据

先创建DBLINK...

create database link TEST.US.ORACLE.COM
connect to TEST identified by TEST

using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.36)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)';

测试Db_LINK

select * from tworkgroupnode@TEST

删除数据

truncate table tagentinfo

同步数据
insert into tagentinfo select * from tagentinfo@TEST

2. 建立触发器同步数据

建立dblink

create database link TEST2.US.ORACLE.COM
connect to ICD identified by ICD
using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.36)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)';

创建触发器

create or replace trigger trib_tagentinfo
after insert or update or delete on tagentinfo
for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;
begin
if inserting then
    insert into tagentinfo@TEST2
      (agentid,
       name,
       agentskills,
       agentworkgroup,
       subccno,
       vdn,
       workgroupid)
    values
      (:NEW.agentid,
       :NEW.name,
       :NEW.agentskills,
       :NEW.agentworkgroup,
       :NEW.subccno,
       :NEW.vdn,
       :NEW.workgroupid);
elsif updating then
    update tagentinfo@TEST2
       set name           = :NEW.name,
           agentskills    = :NEW.agentskills,
           agentworkgroup = :NEW.agentworkgroup,
           vdn            = :NEW.vdn,
           workgroupid    = :NEW.workgroupid
elsif deleting then
    delete from tagentinfo@TEST2 where agentid = :OLD.agentid;
end if;
exception
when integrity_error then
    raise_application_error(errno, errmsg);
end;
/