T_TABLE1:
code number time
2015081901,2015081902 34012,34013 2015-08-19 14:21:08
2015082001 35772 2015-08-20 15:34:00T_TABLE2:
id code number time
1 2015081901 34012 2015-08-19 14:21:08
2 2015081902 34013 2015-08-19 14:21:08
3 2015082001 35772 2015-08-20 15:34:00T_TABLE2的id是主键,其它类型都是VARCHAR2(64)。我想要T_TABLE1每次 插入、更新、删除 某条记录的时候,数据自动同步到T_TABLE2。
里面有一点特殊就是T_TABLE1的code字段可能会包含多个编码(逗号割开),在同步到T_TABLE2的时候要分成多条的记录。
code number time
2015081901,2015081902 34012,34013 2015-08-19 14:21:08
2015082001 35772 2015-08-20 15:34:00T_TABLE2:
id code number time
1 2015081901 34012 2015-08-19 14:21:08
2 2015081902 34013 2015-08-19 14:21:08
3 2015082001 35772 2015-08-20 15:34:00T_TABLE2的id是主键,其它类型都是VARCHAR2(64)。我想要T_TABLE1每次 插入、更新、删除 某条记录的时候,数据自动同步到T_TABLE2。
里面有一点特殊就是T_TABLE1的code字段可能会包含多个编码(逗号割开),在同步到T_TABLE2的时候要分成多条的记录。
SQL> create table t1(code varchar2(30),num varchar2(20),time varchar2(20)) ;
Table created
SQL> create table t2(id int ,code varchar2(30),num varchar2(20),time varchar2(20)) ;
Table created
SQL> create sequence seq_t1 ;
Sequence created
SQL> create trigger tri_ins before insert on t1
2 for each row
3 declare
4 m_code varchar2(30) ;
5 m_num varchar(20) ;
6 t_code varchar2(30) ;
7 t_num varchar(20) ;
8 n int ;
9 begin
10 m_code := ',' || :new.code || ',' ;
11 m_num := ',' || :new.num || ',' ;
12 n := regexp_count(m_code,',') ;
13 for x in 1..n-1
14 loop
15 t_code := substr(m_code,instr(m_code,',',x)+1 , instr(m_code,',',1,x+1) - instr(m_code,',',1,x)-1) ;
16 t_num := substr(m_num,instr(m_num,',',x) +1, instr(m_num,',',1,x+1) - instr(m_num,',',1,x)-1) ;
17 insert into t2 values(seq_t1.nextval , t_code,t_num,:new.time) ;
18 end loop ;
19 end ;
20 /
Trigger created
SQL> insert into t1 values('2015081901,2015081902','34012,34013','2015-08-19 14:21:08');
1 row inserted
SQL> insert into t1 values('2015082001','35772','2015-08-20 15:34:00');
1 row inserted
SQL> select * from t1 ;
CODE NUM TIME
------------------------------ -------------------- --------------------
2015081901,2015081902 34012,34013 2015-08-19 14:21:08
2015082001 35772 2015-08-20 15:34:00
SQL> select * from t2 ;
ID CODE NUM TIME
----- ------------------------------ -------------------- --------------------
1 2015081901 34012 2015-08-19 14:21:08
2 2015081902 34013 2015-08-19 14:21:08
3 2015082001 35772 2015-08-20 15:34:00
SQL> drop table t1 purge ;
Table dropped
SQL> drop table t2 purge ;
Table dropped
SQL> drop sequence seq_t1 ;
Sequence droppedSQL>