create or replace trigger trg_jx0408
before insert or update or delete on jx0408
for each row
declare
ary_kkzcmx t_varray;--申明数组保存kkzcmx
ary_kcsjmx t_varray;--申明数组保存kcsjmx
t_rxnf varchar2(4);
begin
if inserting then--增加操作
if :new.kkzcmx is not null then--如果kkzcmx不为空
ary_kkzcmx := str_split(:new.kkzcmx,',');--分解kkzcmx并且保存在数组中
end if;
if :new.kcsjmx is not null then--如果kcsjmx不为空
ary_kcsjmx := str_split(:new.kcsjmx,',');--分解kcsjmx并且保存在数组中
select rxnf into t_rxnf from jx0404 where tzdid=:new.tzdid;
end if;
for i in 1 .. ary_kkzcmx.count loop
if ary_kkzcmx(i) is not null then
for j in 1 .. ary_kcsjmx.count loop
if ary_kcsjmx(j) is not null then
insert into jx0408_ct (tzdid,xq,kkzcmx,kcsjmx,rxnf,jsbh,kkdlb,xnxqh,kkd)
values(:new.tzdid,:new.xq,ary_kkzcmx(i),substr(ary_kcsjmx(j),'1','2'),t_rxnf,:new.jsh,:new.kkdlb,:new.xnxqh,:new.kkd);
end if;
end loop;
end if;
end loop;
elsif updating then--更新操作
if :new.kkzcmx is not null then--如果kkzcmx不为空
ary_kkzcmx := str_split(:new.kkzcmx,',');--分解kkzcmx并且保存在数组中
end if;
if :new.kcsjmx is not null then--如果kcsjmx不为空;
ary_kcsjmx := str_split(:new.kcsjmx,',');--分解kcsjmx并且保存在数组中
end if;
select rxnf into t_rxnf from jx0404 where tzdid=:new.tzdid;
delete jx0408_ct where kkd=:old.kkd and tzdid=:old.tzdid;
for i in 1 .. ary_kkzcmx.count loop
if ary_kkzcmx(i) is not null then
for j in 1 .. ary_kcsjmx.count loop
if ary_kcsjmx(j) is not null then
insert into jx0408_ct (tzdid,xq,kkzcmx,kcsjmx,rxnf,jsbh,kkdlb,xnxqh,kkd)
values(:new.tzdid,:new.xq,ary_kkzcmx(i),substr(ary_kcsjmx(j),'1','2'),t_rxnf,:new.jsh,:new.kkdlb,:new.xnxqh,:new.kkd);
end if;
end loop;
end if;
end loop;
elsif deleting then--删除操作
delete jx0408_ct where kkd=:old.kkd and tzdid=:old.tzdid;
end if;
end trg_jx0408;
Oracle 触发器
最新推荐文章于 2022-07-20 20:00:00 发布