触发器实例(一)

摘要:
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=

随便看看

聚合初始化

从当前C++标准库中提取,提供了两个容器来表示数组的概念:array表示静态数组,vector表示动态数组。这两种类型都可以使用列表初始化进行初始化。std::array={1,2,3,4,5};std::vector<int>={1,2,3,4,5};这实际上是聚合初始化。聚合初始化历史悠久,在C语言中就已经存在。intarray[5]={1,2,3,4,...

selenium自动化之鼠标操作

,selenium为我们提供了一个处理此类事件的类——ActionChains。ActionChains可以模拟鼠标操作,例如单击、双击、右键单击、拖动等。鼠标移动时演示页面的截图:demo1.使用鼠标移动到WriteonOver按钮的顶部。python脚本如下:读取鼠标移动代码,首先定义浏览器驱动程序,最大化窗口,打开测试页面URL,定位到测试按钮顶部,定...

转:(WIN)S04-CH01 PCIE XDMA开发环境搭建以及环路测试

XDMAIP使用部分教程分LINUX篇和WINDOWS篇两个部分。通过实战,面向应用,提供给大家XILINXFPGAPCIE应用解决方案。本教程以MK7160FA作为样机测试。这是一款高性价比的FPGA开发板。而M_AXI_LITE挂载的BRAM是需要进行BAR空间操作,所以地址设置为0x80000000...

flutter vscode+第三方安卓模拟器

1.首先打开夜曲模拟器2.Win+R,选择cmd,在第三方模拟器安装目录的bin目录下输入夜曲模拟器,然后运行命令:nox_Adb.execonnect127.0.0.1:620013。打开项目终端的vscode并建立连接:adbconnect127.00.1:62001(夜神模拟器的默认端口)4。查看连接:adbdevices或不使用第三方模拟器:1.打开...

springboot 学习之路 27(实现ip白名单功能)

listIps.contains){response.getWriter().print;returnfalse;}returntrue;}@OverridepublicvoidpostHandlethrowsException{}@OverridepublicvaidafterCompletionthrowsException}/**获取合格的项目*@re...

uniapp原生插件开发及打包发布

二.调试原生插件:1.使用hx创建uniapp项目2.创建vue页面或者nvue,在js中第一行获取插件对象vartestModule=uni.requireNativePlugin3.然后使用插件对象就可以调用到插件中的方法4.在hx上的菜单上:发行-原生app-本地打包-生成本地app打包资源;5.打包完成会在控制台上输出打包信息,我们关注的是打包路径;...