红色部分为对查询不到数据异常的处理
create or replace trigger TIG_MONITOR_ALARM
after insert on t_monitor_real_minute
for each row
declare
-- 标准值
standvalue number;
--报警实况表id
liveid number;
begin
--
--触发器功能：监测实况数据表，对比监测数据是否超标，超标数据则记录入超标报警表中
--
standvalue:=-1;
liveid:=-1;
select nvl(t.bzz,-1) into standvalue from t_monitor_factor t where t.jcdbm=:new.STATION_ID and t.jcxmbm=:new.INFECTANT_ID;
--如果录入检测项目数据大于标准值，则入库报警信息表中
if standvalue>-1 then
if :new.M_VALUE>standvalue then
--将数据录入报警历史数据中
insert into t_alarm_history(id,jcdbm,jcxmbm,mvalue,mtime)
values(SEQ_ALARM_HISTORY.NEXTVAL,:new.STATION_ID,:new.INFECTANT_ID,:new.M_VALUE,:new.M_TIME);
--异常判断，如果查询不到数据
begin
select r.id into liveid from t_alarm_real r where r.jcdbm=:new.STATION_ID and r.jcxmbm=:new.INFECTANT_ID;
--查询不到数据
EXCEPTION
WHEN no_data_found THEN
--不存在则录入新的报警实况
insert into t_alarm_history(id,jcdbm,jcxmbm,mvalue,mtime)
values(SEQ_ALARM_REAL.NEXTVAL,:new.STATION_ID,:new.INFECTANT_ID,:new.M_VALUE,:new.M_TIME);
end;
--报警实况中是否已存在该监测点的该因子报警信息
if liveid>-1 then
update t_alarm_real r1 set r1.mvalue=:new.M_VALUE,r1.mtime=:new.M_TIME,r1.status=0 where r1.id=liveid;
else
--不存在则录入新的报警实况
insert into t_alarm_history(id,jcdbm,jcxmbm,mvalue,mtime)
values(SEQ_ALARM_REAL.NEXTVAL,:new.STATION_ID,:new.INFECTANT_ID,:new.M_VALUE,:new.M_TIME);
end if;
end if;
end if;
EXCEPTION
WHEN no_data_found THEN
null;
end TIG_MONITOR_ALARM;