oracle物化视图建立触发器,物化视图上用触发器（用于同步）

作用：由数据源的主表与明细表在目标库进行合并

1，在数据源上建两表，一主，一明细 ,在目标库建引两表的合并表

-- Create table

create table TT_ACC_LEVYCODE主表

(

SESSION_ID NUMBER(10) not null,

LEVYCODE VARCHAR2(11),

KTZC_SESSION_ID VARCHAR2(7)

);

alter table TT_ACC_LEVYCODE

add constraint FKDD primary key (SESSION_ID)

using index

tablespace ZHENGGUAN

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

-- Create table明细表

create table TT_ACC_LEVYDETAILCODE

(

DEATID_ID VARCHAR2(10) not null,

SESSION_ID NUMBER(10),

DETAIL_NAME VARCHAR2(50)

)

tablespace ZHENGGUAN

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

-- Create/Recreate primary, unique and foreign key constraints

alter table TT_ACC_LEVYDETAILCODE

add constraint RFGDFGDF primary key (DEATID_ID)

using index

tablespace ZHENGGUAN

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

alter table TT_ACC_LEVYDETAILCODE

add constraint DDDEEEEE foreign key (SESSION_ID)

references TT_ACC_LEVYCODE (SESSION_ID);

-- Create table

create table TT_ACC_LEVYCODEINFO

(

SESSION_ID NUMBER(10) not null,

LEVYCODE VARCHAR2(11),

KTZC_SESSION_ID VARCHAR2(7),

DEATID_ID VARCHAR2(10) not null,

DETAIL_NAME VARCHAR2(50)

)

tablespace OWB_TG

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 80K

minextents 1

maxextents unlimited

);

-- Grant/Revoke object privileges

grant select, insert, update, delete on TT_ACC_LEVYCODEINFO to STAGING;--注：要把这些权限赋予中转用户

在数据源上建物化视图log

create materialized view log on tt_acc_levycode tablespace zhengguan_sn_log;

create materialized view log on tt_acc_levydetailcode tablespace zhengguan_sn_log;

-------------

2，在中转区建立两源表的物化视图

create materialized view tt_acc_levycode tablespace staging refresh fast as

select t.*

from t;

create materialized view tt_acc_levydetailcode tablespace staging refresh fast as

select t.*

from t;

--LNK27.REGRESS.RDBMS.DEV.US.ORACLE.COM 为中转区到数据源的dblink

3，在中转用户建立触发器

CREATE OR REPLACE TRIGGER WB_tr_acc_levydetailcode

after INSERT OR UPDATE

ON tt_acc_levydetailcode

FOR EACH ROW

DECLARE

v_SESSION_ID NUMBER(10) ;

v_LEVYCODE VARCHAR2(11);

v_KTZC_SESSION_ID VARCHAR2(7);

BEGIN

if INSERTING then

select t.session_id,t.levycode,t.ktzc_session_id

into v_session_id,v_levycode,v_ktzc_session_id

from tt_acc_levycode t

where t.session_id =:new.session_id;

insert into owb_tg.tt_acc_levycodeinfo values(v_SESSION_ID,v_LEVYCODE,v_KTZC_SESSION_ID,:new.DEATID_ID,:new.DETAIL_NAME);

end if;

if UPDATING then

update owb_tg.tt_acc_levycodeinfo t set t.detail_name=:new.detail_name

where t.deatid_id=:new.deatid_id;

end if;

END;

手工刷新视图

execute dbms_mview.refresh('tt_acc_levycode','f');

execute dbms_mview.refresh('tt_acc_levydetailcode','f');

这样执行可保证先后，若全写一起则并发执行

execute dbms_mview.refresh('tt_acc_levycode,tt_acc_levydetailcode','f');