oracle 中的触发器
create or replace trigger "ALARMTRIGGER"before insert on event2005
for each row
declare
status varchar2(5);
resourceid varchar2(60);
alerttime date;
recenttime date;
alertmessage varchar2(60);
alertlevel varchar2(5);
alertname varchar2(60);
devicetype varchar2(60);
alerttype varchar2(60);
clearmessage varchar2(60);
suid varchar2(60);
nlevel varchar2(60);
isexistcount int;
alarmcount int;
begin
status := :new.nstatus; --新的状态值
alertname := :new.ssource || '告警'; --动力环境里的资源名称
alertmessage := :new.SDESCRIPTION; --告警信息
nlevel := :new.nlevel; --告警级别
devicetype := 'resourceType136373ea8c1a48dfaac53607a06e9510'; --设备类型
alerttype := '5'; --告警类型
clearmessage := '告警解除！'; --告警清除信息
suid := :new.suid;
alertlevel:='1';--暂时采用重要告警
if nlevel >= 6 then
dbms_output.put_line('状态'||nlevel);
if nlevel = 6 then
alertlevel:='3';
end if;
select to_date(:new.stime, 'yyyy-MM-dd HH24:mi:ss')
into alerttime
from dual; --告警发生时间
select to_date(:new.stime, 'yyyy-MM-dd HH24:mi:ss')
into recenttime
from dual; --告警最新发生时间
SELECT ISRD.RESOURCEID
INTO resourceid
FROM RESOURCE_DATA ISRD
WHERE ISRD.assetno = substr(suid, 0, 3); --资源ID赋值
if status = 0 or status = 1 then
dbms_output.put_line(alertname || '异常解除');
dbms_output.put_line(status);
dbms_output.put_line(resourceid);
dbms_output.put_line(resourceid);
INSERT INTO alarm_history
(INFO_ID,
ALARMINFOID,
RESOURCE_ID,
ALERT_TIME,
ALERT_MESSAGE,
ALERT_LEVEL,
ALERT_NAME,
ALERT_TYPE,
DEVICE_TYPE,
OCCUR_TIMES,
RECENT_TIME,
CLEAR_RECORD,
CLEAR_OPERATOR,
CLEAR_TIME)
SELECT INFO_ID,
INFO_ID,
RESOURCE_ID,
ALERT_TIME,
ALERT_MESSAGE,
ALERT_LEVEL,
ALERT_NAME,
ALERT_TYPE,
DEVICE_TYPE,
OCCUR_TIMES,
RECENT_TIME,
clearmessage,
'SYSTEM',
sysdate
FROM alert_info t
where t.resource_id = resourceid
and t.alert_name = alertname
and t.alert_level = alertlevel;
delete from alert_info t
where t.resource_id = resourceid
and t.alert_name = alertname
and t.alert_level = alertlevel;
end if;
if status = 2 then
select count(*)
into isexistcount
from event2005 e2
where e2.suid = :new.suid
and e2.ssource = :new.ssource
and e2.nstatus = :new.nstatus
and e2.nlevel = :new.nlevel;
if isexistcount > 0 then
--dbms_output.put_line(:new.ssource || '累加异常次数');
select count(*)
into alarmcount
from alert_info t
where t.resource_id = resourceid
and t.alert_message = alertmessage
and t.alert_name = alertname
and t.alert_level = alertlevel;
if alarmcount > 0 then
update alert_info t
set t.occur_times = t.occur_times + 1,
t.recent_time = recenttime
where t.resource_id = resourceid
and t.alert_message = alertmessage
and t.alert_name = alertname
and t.alert_level = alertlevel;
end if;
if alarmcount = 0 then
INSERT INTO alert_info
(INFO_ID,
RESOURCE_ID,
ALERT_TIME,
ALERT_MESSAGE,
ALERT_LEVEL,
ALERT_NAME,
ALERT_TYPE,
STATE,
DEVICE_TYPE,
OCCUR_TIMES,
RECENT_TIME)
values
('JFHJ#FK#'||seq_trigger_alerm.nextval,
resourceid,
alerttime,
alertmessage,
alertlevel,
alertname,
alerttype,
'0', --0为未确认
devicetype,
'1', --告警发生次数 第一次
recenttime);
end if;
end if;
if isexistcount = 0 then
-- dbms_output.put_line(:new.ssource || '产生异常了');
INSERT INTO alert_info
(INFO_ID,
RESOURCE_ID,
ALERT_TIME,
ALERT_MESSAGE,
ALERT_LEVEL,
ALERT_NAME,
ALERT_TYPE,
STATE,
DEVICE_TYPE,
OCCUR_TIMES,
RECENT_TIME)
values
('JFHJ#FK#'||seq_trigger_alerm.nextval,
resourceid,
alerttime,
alertmessage,
alertlevel,
alertname,
alerttype,
'0', --0为未确认
devicetype,
'1', --告警发生次数 第一次
recenttime);
end if;
end if;
end if;
end;
页:
[1]