create or replace trigger TR_AFBJ_JL_BJXXJL after delete or insert ON AFBJ_JL_BJXXJL for each row --报警记录表改变后，修改风险记录表 declare --变量声明 PRAGMA AUTONOMOUS_TRANSACTION;--开启自治事物 n_fxlxdh number:=0 ; n_dwlx number:=10 ; nums number:=0 ; BEGIN if inserting then -- a、判断是不是市直属网点 ，即n_dwlx=9时为市直属网点 select t2.n_dwlx into n_dwlx from t_dwxx t left outer join t_dwxx t2 on t.n_sjdw = t2.n_dwdh where t.n_dwdh = :new.n_dwdh; -- b、获取当前插入报警记录表中的记录详情 if (:new.N_QFBZ=0) then --网点，查询当前插入网点记录信息 select t.n_fxlxdh into n_fxlxdh from afbj_fxbjdy_wd t where t.n_bjlxdh = :new.n_bjlxdh; else --业务库，查询当前插入业务库记录信息 select t.n_fxlxdh into n_fxlxdh from afbj_fxbjdy_ywk t where t.n_bjlxdh = :new.n_bjlxdh; end if; ------------------------------------------------------------------------------------------------------------------------------------- -- 将可以转换成风险的报警记录插入风险记录表中 -- 除了 违规操作、设备异常 其它8类报警在插入报警记录表时同时插入风险记录表中 -- 1、紧急情况报警、情况异常、案件预警全部可见 if (:new.n_xxxzdh=1 or :new.n_xxxzdh=2 or :new.n_xxxzdh=3) then INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,0,0,0,0 ); end if; ------------------------------------------------------------------------------------------------------------------------------------- -- 2、设备破坏报警、设备断线断线报警、安全检查检测报警、ATM异常报警、系统检测报警 if (:new.n_xxxzdh=4 or :new.n_xxxzdh=5 or :new.n_xxxzdh=6 or :new.n_xxxzdh=9 or :new.n_xxxzdh=10) then if (n_dwlx=9) then --市直属网点、业务库，市级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属网点、业务库，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0); end if; end if; ------------------------------------------------------------------------------------------------------------------------------------- -- 3、违规操作报警 if (:new.n_xxxzdh=7) then -- 3.1、网点 if (:new.N_QFBZ=0) then if (n_fxlxdh>=132 and n_fxlxdh < 185) then -- 1、一个月中发生n次的记录 -- 获取当月发生的总次数 nums select count(*) into nums from AFBJ_JL_BJXXJL t where (t.d_bjsj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_bjlxdh = :new.n_bjlxdh and t.n_dwdh = :new.n_dwdh and t.n_qfbz = 0; if (nums=1) then --第一次发生，则插入风险记录表中 if (n_dwlx= 9) then --市直属网点，市级可见，县级不可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属网点，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; elsif (nums>=5 and n_dwlx!=9) then --市级可见，非市直属网点时执行 update AFBJ_JL_FXXXJL t set t.d_fxcssj_shi = sysdate, t.n_shibz=0,t.n_shengbz=1,t.n_jtbz=1 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=7) then --省级可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_sheng = sysdate, t.n_shengbz=0,t.n_jtbz=1 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=10) then --集团可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_jt = sysdate, t.n_jtbz=0 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; end if; elsif (n_fxlxdh=185 or n_fxlxdh= 188) then -- 2、网点 月检 if (n_dwlx=9) then --市直属网点，市级可见 INSERT INTO AFBJ_JL_FXXXJL(N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属网点，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; elsif (n_fxlxdh= 186 or n_fxlxdh= 187) then -- 3、网点，连续2次提示 -- 获取没有按时检查设备、未按时维护设备的总次数 nums d_jcxmwcsj(检查项目完成时间),d_gzxfdxsj(故障修复短信时间) select count(*) into nums from AFBJ_JL_BJXXJL t where t.d_jcxmwcsj is null and t.d_gzxfdxsj is null and t.n_bjlxdh = :new.n_bjlxdh and t.n_dwdh = :new.n_dwdh and t.n_qfbz = 0; if (nums=1) then --第一次发生，则插入风险记录表中 if (n_dwlx=9) then --市直属网点，市级可见，县级不可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属网点，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; elsif (nums>=2 and n_dwlx!=9) then --市级可见，非市直属网点时执行 update AFBJ_JL_FXXXJL t set t.d_fxcssj_shi = sysdate, t.n_shibz=0,t.n_shengbz=1,t.n_jtbz=1 where t.d_fxxxczsj is null and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=3) then --省级可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_sheng = sysdate, t.n_shengbz=0,t.n_jtbz=1 where t.d_fxxxczsj is null and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=4) then --集团可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_jt = sysdate, t.n_jtbz=0 where t.d_fxxxczsj is null and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; end if; end if; -- 3.2、业务库 else if (n_fxlxdh>=132 and n_fxlxdh < 137) then --3.2.1、一个月中发生n次的记录 -- 获取当月发生的总次数 nums select count(*) into nums from AFBJ_JL_BJXXJL t where (t.d_bjsj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_bjlxdh = :new.n_bjlxdh and t.n_dwdh = :new.n_dwdh and t.n_qfbz = 1; if (nums=1) then --第一次发生，则插入风险记录表中 if (n_dwlx=9) then --市直属业务库，市级可见，县级不可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属业务库，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; elsif (nums>=5 and n_dwlx!=9) then --市级可见，非市直属业务库时执行 update AFBJ_JL_FXXXJL t set t.d_fxcssj_shi = sysdate, t.n_shibz=0,t.n_shengbz=1,t.n_jtbz=1 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=7) then --省级可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_sheng = sysdate, t.n_shengbz=0,t.n_jtbz=1 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=10) then --集团可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_jt = sysdate, t.n_jtbz=0 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; end if; elsif (n_fxlxdh = 137) then -- 3.2.2、业务库 月检 if (n_dwlx=9) then --市直属业务库，市级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属业务库，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; elsif (n_fxlxdh= 138 or n_fxlxdh= 139) then --3.2.3、业务库，连续2次提示 -- 获取没有按时检查、维护设备的总次数 nums select count(*) into nums from AFBJ_JL_BJXXJL t where t.d_jcxmwcsj is null and t.d_gzxfdxsj is null and t.n_bjlxdh = :new.n_bjlxdh and t.n_dwdh = :new.n_dwdh and t.n_qfbz = 1; if (nums=1) then --第一次发生，则插入风险记录表中 if (n_dwlx=9) then --市直属业务库，市级可见，县级不可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属业务库，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; elsif (nums>=2 and n_dwlx!=9) then --市级可见，非市直属业务库时执行 update AFBJ_JL_FXXXJL t set t.d_fxcssj_shi = sysdate, t.n_shibz=0,t.n_shengbz=1,t.n_jtbz=1 where t.d_fxxxczsj is null and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=3) then --省级可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_sheng = sysdate, t.n_shengbz=0,t.n_jtbz=1 where t.d_fxxxczsj is null and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=4) then --集团可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_jt = sysdate, t.n_jtbz=0 where t.d_fxxxczsj is null and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; end if; end if; end if; end if; ----------------------------------------------------------------------------------------------------------------------------------------- -- 4、设备异常，工作提醒排除(即 n_xxfldh==13 时) if (:new.n_xxxzdh=8 and :new.n_xxfldh!=13) then -- 4.1、网点 if (:new.N_QFBZ=0) then -- 4.1.1、一个月中发生n次的记录 if (n_fxlxdh>=146 and n_fxlxdh < 152) then -- 获取当月发生的总次数 nums select count(*) into nums from AFBJ_JL_BJXXJL t where (t.d_bjsj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_bjlxdh = :new.n_bjlxdh and t.n_dwdh = :new.n_dwdh and t.n_qfbz = 0; if (nums=1) then --第一次发生，则插入风险记录表中 if (n_dwlx=9) then --市直属网点，市级可见，县级不可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属网点，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; elsif (nums>=5 and n_dwlx!=9) then --市级可见，非市直属网点时执行 update AFBJ_JL_FXXXJL t set t.d_fxcssj_shi = sysdate, t.n_shibz=0,t.n_shengbz=1,t.n_jtbz=1 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=7) then --省级可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_sheng = sysdate, t.n_shengbz=0,t.n_jtbz=1 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; elsif (nums>=10) then --集团可见 update AFBJ_JL_FXXXJL t set t.d_fxcssj_jt = sysdate, t.n_jtbz=0 where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :new.n_dwdh and t.n_fxlxdh = n_fxlxdh; end if; -- 4.1.2、其他网点情况 else if(n_dwlx=9) then --市直属网点，市级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属网点，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; end if; -- 4.2、业务库 else if (n_dwlx=9) then --市直属业务库，市级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,0,1 ); else --非市直属业务库，县级可见 INSERT INTO AFBJ_JL_FXXXJL (N_FXXXJLDH,N_XXXZDH,N_XXFLDH,N_FXLXDH,N_DWDH, N_SBZJBH,D_FXCSSJ,N_QFBZ,N_BJXXJLDH,N_JTBZ,N_SHENGBZ,N_SHIBZ,N_XIANBZ) values (Q_FXXXJLDH.NEXTVAL,:new.n_xxxzdh,:new.n_xxfldh,n_fxlxdh,:new.n_dwdh, :new.n_sbzjbh,:new.d_bjsj,:new.n_qfbz,:new.n_bjxxjldh,1,1,1,0 ); end if; end if; end if; ------------------------------------------------------------------------------------------------------------------------------------------- -- 5、删除操作 elsif deleting then -- 5.1、除了违规操作、设备异常 其它8类风险可以在报警记录删除的同时，在风险记录表中也删除 if(:new.n_xxxzdh!=7 and :new.n_xxxzdh!=8) then delete from AFBJ_JL_FXXXJL t where t.n_bjxxjldh = :old.n_bjxxjldh; -- 5.2、违规操作、设备异常 else if(:new.N_QFBZ=0) then -- 5.2.1、网点 select count(*) into nums from AFBJ_JL_BJXXJL t where (t.d_bjsj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_bjlxdh = :old.n_bjlxdh and t.n_dwdh = :old.n_dwdh and t.n_qfbz = 0; else -- 5.2.2、业务库 select count(*) into nums from AFBJ_JL_BJXXJL t where (t.d_bjsj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_bjlxdh = :old.n_bjlxdh and t.n_dwdh = :old.n_dwdh and t.n_qfbz = 1; end if; -- 5.2.3、当在报警记录表中记录为0时，在风险记录表中也删除 if(nums=0) then delete from AFBJ_JL_FXXXJL t where (t.d_fxcssj between (select to_date(to_char(sysdate, 'yyyy-mm') || '-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual) and sysdate) and t.n_dwdh = :old.n_dwdh and t.n_fxlxdh = n_fxlxdh; end if; end if; end if; COMMIT; END;