触发器实例(一)

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

随便看看

Notification(Notification的通知栏常驻、Notification的各种样式、Notification点击无效)

所以Notification的使用,也在开发当中,使用的越来越频繁。今天我就来跟大家分享一下Notification的常用事项。最新的Notification的用法,是推荐使用V4包下的NotificationCompat.Builder,利用它,进行各种设置,具体的用法先别着急,我们慢慢道来。//Notification.DEFAULT_SOUND:系统默...

oracle触发器调试

如果触发器执行成功,不会出现第4个图,不成功,会出现数据调试信息,具体报错位置会定位到。F7单步执行4.出错时,会出现调试数据,双击调试数据,可以复制出来...

backgroundsize

当背景大小值为和时,可以设置两个值,也可以设置一个值。当只取一个值时,第二个值相当于auto,但此处的auto不会将背景图像的高度保持在其原始高度,而是与第一个值相同。此外,如果只取一个值,宽度和高度将相同,这相当于背景大小:80%自动。...

CUPS

杯子:一个。工具1.hal设备管理器2.系统配置打印机3.Web管理器/etc/cups/ccups。conf/etc/cups/printer conf II。打印机本地安装和客户端安装1.在本地安装Linux打印机时,应选择postscript和pcl打印机。如果没有,则应将打印机设置为原始打印模式/etc/cups/printers。有限公司...

使用Docker构建redis集群

将六个独立的Redis节点关联到主机上的Redis集群中。Redis部落。rb是Redis官方提供的一个ruby脚本,用于构建Redis集群并修改Redis conf将其移动到上部路径/usr/docker_root/redis_Cluster/。受保护模式norequipassa1s2W3l4%Greunbind无法连接到凹坑以构建Redis基本映像。9....

rm 命令(删除文件和目录)

Rm是常用命令。其功能是删除目录中的一个或多个文件或目录。它还可以删除目录及其下的所有文件和子目录。对于链接文件,只删除链接,原始文件保持不变。如果使用rm删除文件,仍然可以将文件恢复到原始状态。yroot@localhosttest1]#Ll总计0[root@localhosttest1]#注意:输入rmlog.log命令后,系统将询问是否删除。输入y后,...