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;
