触发器实例(一)

摘要:
createorreplacetriggerTR_ AFBJ_ JL_ BJXXJLafteredeleteorisertONAFBJ_JL_BJXXJLforechrow--更改报警记录表后,修改风险记录表声明--变量声明PRAGMAUTONOUS_TRANSACTION;--打开自主事物n_fxlxdhnumber:=0;n_dwlx编号:=10;数字:=0
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;



免责声明:内容来源于网络,仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇selenium-百度新歌榜python的文件操作1,监控日志联系、读取大文件、修改文件、集合、元组、random模块以及函数初识下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

随便看看

泛微E8升级E9代码修改

E8升级E9需要修改后台二次开发的代码,总工作量相当大。CheckCustomize=function(){varnodeId=$.val();//应用程序节点如果{varpgrcontrol=$.val);varfinishpercent=$.vl();如果{如果{Dialog.alert(“进度控制为yes,需要完成百分比!”);return false...

如何在jenkins上新建一个项目及其简单配置

单击[新建]进入选择页面,您可以在此页面上配置项目(包括拉取源代码、修改连续构建时间以及在打包和部署之前修改配置文件)3。在General中,您可以设置要构建的版本,如下图5所示。在源代码管理模块中,您可以设置源代码地址(我们公司常用的Git)6。如果是自动构建,您可以将自动构建时间(即构建频率)设置为7。以下是构建中的一些设置。您可以使用shell修改源代...

Jdk升级到11引起的问题:程序包javax.xml.bind.annotation不存在

您可以看到ELDict类中有一个引用:importjavax。xml。绑定注释XmlAttribute;虽然未使用,但它会导致mvn编译错误。在在线绑定中搜索“包javax.xml.bind.nannotation不存在”。结果是:包javax。xml。bind Annotation不存在-CSDN论坛2009年12月2日·无法编译使用jaxb的类,因为软件...

开源BI分析工具Metabase配置与完全使用手册

文章目录简介安装初始配置数据分析简单查询创建场景创建集合和仪表盘自定义查询原生查询sql变量动态sql片段管理员操作添加数据库连接oracle成员管理邀请新成员权限配置数据权限文件夹权限邮箱配置定时任务简介Metabase是一个免费的BI分析工具,可以帮助你把数据库中的数据更好的呈现给更多人,通过建立一个”查询“来提炼数据,再以图形化的方式做展示。上手简单,...

electron用默认浏览器打开链接的3种实现方式

在使用Electron开发桌面程序的过程中,我们可能经常需要使Electron程序中包含的链接在单击后直接调用系统的默认浏览器打开。仔细阅读文档后,我们都知道它的核心原理是调用系统的默认浏览器,通过Electron shell模块中的openExternal方法打开链接。然而,它的实现有不同的方法,彻底接管和选择性接管。介绍第3章中的有效方法。以上三种方法都...

EasyPoi导入验证功能

1准备好要导入的Excel,注意Excel的标题要和domain中的@Excel一样1导入验证包支持˂!...