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

最新推荐文章于 2024-04-24 14:35:12 发布

最新推荐文章于 2024-04-24 14:35:12 发布 · 84 阅读

文章标签：

#数据库 #网络

本文介绍如何使用Oracle数据库中的DBLINK进行表数据同步的方法，并提供通过触发器实现自动同步的示例。

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

先创建DBLINK...

create database link TEST.US.ORACLE.COM
connect toTEST identified byTEST

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

测试Db_LINK

select * fromtworkgroupnode@TEST

删除数据

truncate table tagentinfo

同步数据
insert into tagentinfo select * fromtagentinfo@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 intotagentinfo@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
updatetagentinfo@TEST2
set name = :NEW.name,
agentskills = :NEW.agentskills,
agentworkgroup = :NEW.agentworkgroup,
vdn = :NEW.vdn,
workgroupid = :NEW.workgroupid
elsif deleting then
delete fromtagentinfo@TEST2where agentid = :OLD.agentid;
end if;
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/