-- 取序列号
- begin
- IF :new.other_area_id is null or :new.other_area_id=0 THEN
- select sequence_other_area.nextval INTO :new.other_area_id from dual;
- END IF;
- exception
- when integrity_error then raise_application_error(errno, errmsg);
- end;
- if :new.other_area_site_code is not null then
- insert into tab_quote_other_list
- (guid,other_area_id ,use_site_code ,fee_type_code ,other_site_code ,modify_date)
- select sys_guid(),:new.other_area_id,:new.use_site_code,:new.fee_type_code,
- substr(other_area_site,
- instr(other_area_site, ';', 1, rownum) + 1,
- instr(other_area_site, ';', 1, rownum + 1) -
- instr(other_area_site, ';', 1, rownum) - 1),sysdate
- from (select :new.other_area_site_code as other_area_site from dual)
-- 正则表达式查找 ";" 的个数
connect by rownum <length(regexp_replace(other_area_site, '[^;]',''));
-- 函数替换 (数据量小)translate(string,from_str,to_str), 同时比较 string 和 from_str
-- 字符串中的每个字符, 存在不存在 to_str 字符, 即从 string 中剔除掉
- --connect by rownum < length(translate(other_area_site,';' || other_area_site,';'));
- end if;
- elsif deleting then
- if :old.other_area_site_code is not null then
- delete from tab_quote_other_list where other_area_id = :old.other_area_id;
- end if;
- else
- if nvl(:old.modify_date,to_date('1899-1-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) <>
- nvl(:new.modify_date,to_date('1899-1-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) then
- if :old.other_area_site_code is not null then
- delete from tab_quote_other_list where other_area_id = :old.other_area_id;
- end if;
- if :new.other_area_site_code is not null then
- insert into tab_quote_other_list
- (guid,other_area_id ,use_site_code ,fee_type_code ,other_site_code ,modify_date)
- select sys_guid(),:new.other_area_id,:new.use_site_code,:new.fee_type_code,
- substr(other_area_site,
- instr(other_area_site, ';', 1, rownum) + 1,
- instr(other_area_site, ';', 1, rownum + 1) -
- instr(other_area_site, ';', 1, rownum) - 1),
- sysdate
- from (select :new.other_area_site_code as other_area_site from dual)
- connect by rownum < length(translate(other_area_site,';' || other_area_site,';'));
- end if;
- end if;
- end if;
- end trg_quote_otherArea_to_list;
触发器
来源: http://www.bubuko.com/infodetail-2614869.html