gger OGG_trg.trg_tTB01
before insert or update on OGG_trg.tTB01
for each row
declare
v_temp varchar2(100);
begin
if(:new.TPROVINCE is not null)
then
v_temp:=:new.TPROVINCE;
:new.TCITY:=substr(v_temp,instr(v_temp,'_')+1);
:new.TPROVINCE:=substr(v_temp,0,instr(v_temp,'_')-1);
end if;
end;
/
3 业务测试
启动源端和目标端的进程后在源端插入测试数据
insert into OGG_OWNER.STB01(
SID ,
SNAME ,
SGENDER ,
SBRIDATE ,
ADETAIL )
select 1,'sywu','1',sysdate,'云南省_临沧' from dual
union all
select 2,'xiaohong','2',sysdate,'云南省_昆明' from dual
/
commit;
检验数据
SYS@sydb>column TPROVINCE format a30
SYS@sydb>select * from OGG_OWNER.STB01;
SID SNAME S SBRIDATE ADETAIL
---------- --------------- - ------------------ ----------------------------------------------------------------------
1 sywu 1 08-SEP-15 云南省_临沧
2 xiaohong 2 08-SEP-15 云南省_昆明
Elapsed: 00:00:00.00
SYS@sydb>select * from OGG_trg.ttb01;
TID TNAME T TBRIDATE TPROVINCE TCITY TRANSFERDATE
---------- --------------- - ------------------ ------------------------------ ---------- ------------------
1 sywu 1 08-SEP-15 云南省_临沧 08-SEP-15
Elapsed: 00:00:00.01
4 GoldenGate支持触发器
通过以上测试查询发现，源端的数据可以交换到目标端了，但是目标端并没有实现将地址字段数据分拆，为什么？
SYS@sydb>column OWNER format a10
SYS@sydb>column TRIGGER_NAME format a20
SYS@sydb>column TRIGGER_TYPE format a20
SYS@sydb>column TRIGGERING_EVENT format a20
SYS@sydb>column TABLE_NAME format a20
SYS@sydb>select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_NAME,STATUS from dba_triggers where trigger_name='TRG_TTB01';
OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_NAME STATUS
---------- -------------------- -------------------- -------------------- -------------------- --------
OGG_TRG TRG_TTB01 BEFORE EACH ROW INSERT OR UPDATE TTB01 ENABLED
Elapsed: 00:00:00.09
但查询触发器是可用的哦，于是在官方文档上发现这样一段描述:官方文档DBOPTIONS参数；
SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS Valid for nonintegrated Replicat for Oracle. Controls whether or not triggers are fired during the Replicat session. Provides an alternative to manually disabling triggers. (Integrated Replicat does not require disabling of triggers on the target system.)
SUPPRESSTRIGGERS is the default and prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. SUPPRESSTRIGGERS is valid for Oracle 11.2.0.2 and later 11gR2 versions. SUPPRESSTRIGGERS is not valid for 11gR1.
To allow a specific trigger to fire, you can use the following SQLEXEC statement in the Replicat parameter file, where trigger_owner is the owner of the trigger and trigger_name is the name of the trigger.
SQLEXEC 'dbms_ddl.set_trigger_firing_property(trigger_owner "trigger_name", FALSE);'
你应该注意到了fire是默认值，so 要启用触发器只要设置NOSUPPRESSTRIGGERS即可；
GGSCI (sywu) 16> edit param RSYDB001
REPLICAT rsydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg_trg,password AADAAAAAAAAAAAHABDQFVJMADCAFECACYEPIQEJCFGDGMDHBRJXCUBOBQJEGLBPEBDMCOAACDILGAJKA &
aes128,ENCRYPTKEY securekey1
DISCARDFILE /u01/app/product/ogg_trg/discrd/reptr.desc,append,meg