分类: Oracle
2010-09-08 21:41:08
1. 在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
删除数据
truncate table tagentinfo
同步数据
insert into tagentinfo select * from
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
(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
set name =
:NEW.name,
agentskills = :NEW.agentskills,
agentworkgroup = :NEW.agentworkgroup,
vdn =
:NEW.vdn,
workgroupid = :NEW.workgroupid
elsif deleting
then
delete from where agentid = :OLD.agentid;
end
if;
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/