17,377
社区成员
发帖
与我相关
我的任务
分享
ORCL103 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
create database link ORCL103.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to CCPPH1
using 'orcl103';
--T1表
Create table T1 as select * from T1@orcl103;
alter table T1 add constraint PK_ID primary key (ID);
--T2表
Create table T2 as select * from T2@orcl103;
alter table T2 add constraint PK_ID primary key (ID);
--T1表触发器
create or replace trigger TRI_T1
after insert or update or delete on T1
for each row
begin
if deleting then
dbms_output.put_line('删除');
delete from T1@orcl101 where id=:old.id;
end if;
if inserting then
dbms_output.put_line('插入');
insert into T1@orcl101(id,name)
values(:new.id,:new.name);
end if;
if updating then
dbms_output.put_line('修改');
update T1@orcl101 set id=:new.id,name=:new.name where id=:old.id;
end if;
end TRI_T1;
--T2表触发器
create or replace trigger TRI_T2
after insert or update or delete on T2
for each row
begin
if deleting then
dbms_output.put_line('删除');
delete from T2@orcl101 where id=:old.id;
end if;
if inserting then
dbms_output.put_line('插入');
insert into T2@orcl101(id,name)
values(:new.id,:new.name);
end if;
if updating then
dbms_output.put_line('修改');
update T2@orcl101 set id=:new.id,name=:new.name where id=:old.id;
end if;
end TRI_T2;
--创建物化视图日志
create materialized view log on T1 with rowid;
create materialized view log on T2 with rowid;
create materialized view MV_T
refresh fast on commit
as
select t1.rowid as t1rowid,t2.rowid as t2rowid,t1.id,t1.name,t2.id as t2id,t2.name as t2name from T1 t1,T2 t2 where t1.id = t2.id;
create or replace trigger TRI_T
after insert or update or delete on mv_T
for each row
begin
if deleting then
dbms_output.put_line('删除');
delete from T where ID=:old.ID;
end if;
if inserting then
dbms_output.put_line('插入');
insert into T(id,name)
values(:new.id,:new.name);
end if;
if updating then
dbms_output.put_line('修改');
update T set id=:new.id,name=:new.name where id=:old.id;
end if;
end TRI_T;