The red part is used to handle exceptions where no data is found.
Create or replace trigger TIG_MONITOR_ALARM
After insert on t_monitor_real_minute
For each row
Declare
-- Standard value
Standvalue number;
-- Alarm fact table id
Liveid number;
Begin
--
-- Trigger function: monitors the real-time data table and compares whether the monitoring data exceeds the standard. If the data exceeds the standard, the system records the data in the alarm table that exceeds the standard.
--
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 the data of the input detection item is greater than the standard value, the data is stored in the alarm information table.
If standvalue>-1 then
If: new. M_VALUE> standvalue then
-- Input data into historical alarm data
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 );
-- Exception judgment. If no data is found
Begin
Select r. id into liveid from t_alarm_real r where r. jcdbm =: new. STATION_ID and r. jcxmbm =: new. INFECTANT_ID;
-- No data found
EXCEPTION
WHEN no_data_found THEN
-- If the alarm does not exist, enter the new alarm status.
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;
-- Whether the alarm information of this factor exists in the alarm status
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
-- If the alarm does not exist, enter the new alarm status.
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;