maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_devicealert_keepcount()
Parameters
Name
Type
Mode
Definition
/* To keep the count of viewed alerts based on device */ DECLARE err_msg varchar; err_detail varchar; err_hint varchar; ac_leveltype INTEGER /* this data determines whether we're dealing with a notification, alert, or maintenance alert */; ac_idclient INTEGER; BEGIN SET lock_timeout = '50ms'; /* Get ac.leveltype to know which counter to decrease */ IF (TG_OP = 'UPDATE' AND OLD.enabled = true AND NEW.enabled = false) THEN SELECT leveltype,idclient INTO ac_leveltype,ac_idclient FROM alertconf WHERE idalertconf = NEW.idalertconf; CASE WHEN ac_leveltype = 1 THEN UPDATE mvw_device_alert SET totalnotificaciones = totalnotificaciones - 1 WHERE iddevice = OLD.iddevice AND idclient = ac_idclient; WHEN ac_leveltype = 2 THEN UPDATE mvw_device_alert SET totalalerts = totalalerts - 1 WHERE iddevice = OLD.iddevice AND idclient = ac_idclient; WHEN ac_leveltype = 3 THEN UPDATE mvw_device_alert SET totalmaintenance = totalmaintenance - 1 WHERE iddevice = OLD.iddevice AND idclient = ac_idclient; ELSE -- DO NOTHING END CASE; ELSIF TG_OP = 'INSERT' THEN SELECT leveltype,idclient INTO ac_leveltype,ac_idclient FROM alertconf WHERE idalertconf = NEW.idalertconf; PERFORM 1 FROM mvw_device_alert WHERE iddevice = NEW.iddevice AND idclient = ac_idclient; IF NOT FOUND THEN INSERT INTO mvw_device_alert (iddevice,idclient,totalnotificaciones,totalalerts,totalmaintenance) SELECT NEW.iddevice,ac_idclient,0,0,0; END IF; CASE WHEN ac_leveltype = 1 THEN UPDATE mvw_device_alert SET totalnotificaciones = totalnotificaciones + 1 WHERE iddevice = NEW.iddevice AND idclient = ac_idclient; WHEN ac_leveltype = 2 THEN UPDATE mvw_device_alert SET totalalerts = totalalerts + 1 WHERE iddevice = NEW.iddevice AND idclient = ac_idclient; WHEN ac_leveltype = 3 THEN UPDATE mvw_device_alert SET totalmaintenance = totalmaintenance + 1 WHERE iddevice = NEW.iddevice AND idclient = ac_idclient; ELSE -- DO NOTHING END CASE; END IF; RETURN NEW; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL, err_hint = PG_EXCEPTION_HINT; RAISE WARNING '% % %', err_msg,err_detail,err_hint; RETURN NEW; END;