oracle数据库触发器监听删除，可是为什么批量删除时，触发器时而起作用时而不起作用呢？

0 0

oracle数据库触发器监听删除，可是为什么批量删除时，触发器时而起作用时而不起作用呢？0

一、涉及三张表（csp_nowinmine、csp_overtimehisalarm 、csp_overtimealarm ）

二、问题：删除监听时而起作用时而不起作用，在PL/SQL中的sql window里测试删除语句是没问题的，但是java代码批量插入数据时则会出现此题

二、代码：

create or replace trigger nowinmine_triggers
after insert or update or delete on csp_nowinmine
for each row
declare
--本地变量定义
orgNametemp            varchar2(50);--存储新的机构名称
oldorgNametemp         varchar2(50);--存储旧的机构名称
coalNametemp           varchar2(50);--存储新的煤矿名称
oldcoalNametemp        varchar2(50);--存储旧的煤矿名称
addresstemp            varchar2(100);--存储新的传感器位置
overtimetemp           number(10,2);--存储新的矿工持续在井时间
oldovertime            number(10,2);--存储旧的矿工持续在井时间
isAlarmtemp            number(2);   --存储之前告警数目
currenttime            varchar2(30);--当前时间
newminercardcode       varchar2(15);--新的矿工卡号
oldminercardcode       varchar2(15);--新的矿工卡号
newsensorcode          varchar2(20);--新的传感器编码
oldsensorcode          varchar2(20);--新的传感器编码
begin
case
    --当触发删除时
    when deleting then
      select o.orgname into oldorgNametemp from csp_org o where o.code = :old.orgcode;
      select c.name into oldcoalNametemp from csp_coalmineinfo c where
        c.id = :old.orgcode || '_' || :old.coalcode;
      currenttime := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
      oldminercardcode := :old.minercardcode;
      oldsensorcode := :old.sensorcode;
      dbms_output.put_line(oldminercardcode);

      delete from csp_overtimealarm ot where ot.orgname = oldorgNametemp and ot.coalname = oldcoalNametemp and
        ot.minercardcode = oldminercardcode;
      update csp_overtimehisalarm oth set oth.alarmflag = 0, oth.endtime = currenttime
          where oth.orgname = oldorgNametemp and oth.coalname = oldcoalNametemp and
            oth.minercardcode = oldminercardcode and oth.alarmflag = 1 and oth.sensorcode = oldsensorcode;

    --当触发插入或者更新时
    when inserting or updating then
      --得到机构名称. 煤矿名称. 传感器位置信息. 矿工持续在井时间
      select o.orgname into orgNametemp from csp_org o where o.code = :new.orgcode;
      select c.name into coalNametemp from csp_coalmineinfo c where c.id = :new.orgcode || '_' || :new.coalcode;
      select l.address into addresstemp from csp_locsensorinfo l
        where l.id = :new.orgcode || '_' || :new.coalcode || '_' || :new.sensorcode;
      newminercardcode := :new.minercardcode;
      newsensorcode := :new.sensorcode;

      overtimetemp := round((To_date(:new.updatetime, 'yyyy-mm-dd hh24:mi:ss') - To_date(:new.inminetime, 'yyyy-mm-dd hh24:mi:ss')) * 24,2);

      --查询之前告警数目
      select count(*) alarmtemp into isAlarmtemp from csp_overtimehisalarm oth
        where oth.orgname = orgNametemp and oth.coalname = coalNametemp and
          oth.minercardcode = newminercardcode and oth.alarmflag = 1 and oth.sensorcode = newsensorcode;
      --如果之前告警数目为0
      if (isAlarmtemp = 0) then
        --将此条数据插入实现告警表
        insert into csp_overtimealarm(orgName,coalName,sensorcode,minercardcode,address,overtime,starttime,alarmflag)
        values(orgNametemp,coalNametemp,newsensorcode,newminercardcode,addresstemp,overtimetemp,:new.updatetime,1);
        --将此条数据插入历史告警表
        insert into csp_overtimehisalarm(orgName,coalName,sensorcode,minercardcode,address,overtime,starttime,alarmflag)
        values(orgNametemp,coalNametemp,newsensorcode,newminercardcode,addresstemp,overtimetemp,:new.updatetime,1);
      --如果之前告警数目不为0
      else
        --将之前的持续在井时间存储到oldovertime中
        select ot.overtime into oldovertime from csp_overtimealarm ot
          where ot.orgname = orgNametemp and ot.coalname = coalNametemp and
            ot.minercardcode = newminercardcode and ot.alarmflag = 1 and ot.sensorcode = newsensorcode;
        --如果持续在井时间改变
        if(overtimetemp != oldovertime) then
          --恢复之前表中该矿工的告警
          update csp_overtimehisalarm oth set oth.alarmflag = 0, oth.endtime = :new.updatetime
            where oth.orgname = orgNametemp and oth.coalname = coalNametemp and
              oth.minercardcode = newminercardcode and oth.alarmflag = 1 and oth.sensorcode = newsensorcode;
          --插入新的告警
          insert into csp_overtimehisalarm(orgName,coalName,sensorcode,minercardcode,address,overtime,starttime,alarmflag)
            values(orgNametemp,coalNametemp,newsensorcode,newminercardcode,addresstemp,overtimetemp,:new.updatetime,1);
          --更新实时告警表中该矿工的告警
          update csp_overtimealarm ot set ot.starttime = :new.updatetime, ot.overtime = overtimetemp
            where ot.orgname = orgNametemp and ot.coalname = coalNametemp and
              ot.minercardcode = newminercardcode and ot.alarmflag = 1 and ot.sensorcode = newsensorcode;
        end if;
      end if;
    end case;
end nowinmine_triggers;