Public.setifservicekeysonupdate (trigger function)

wikibot

CREATE OR REPLACE FUNCTION public.setifservicekeysonupdate()

RETURNS trigger
LANGUAGE plpgsql

AS $function$ BEGIN

 --
 -- (Update with old style foreign key)
 -- This condition keeps ifserviceid inSync with the composite foreign key of nodeid, ipaddr, serviceid
 -- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
 -- one or more of the composite key values, the ifServiceId needs to be updated
 --
 IF (NEW.ifserviceID = OLD.ifServiceId) AND (NEW.nodeId != OLD.nodeId OR NEW.ipAddr != OLD.ipAddr OR NEW.serviceId != OLD.serviceID) 
 THEN
    SELECT ifsvc.id INTO NEW.ifserviceid
      FROM ifservices ifsvc
      WHERE (ifsvc.nodeid = NEW.nodeid AND ifsvc.ipAddr = NEW.ipAddr AND ifsvc.serviceid = NEW.serviceid);
      
    IF NOT FOUND THEN
       RAISE EXCEPTION 'Outages Trigger Exception, Condition 3: No service found for... nodeid: %  ipaddr: %  serviceid: %', NEW.nodeid, NEW.ipAddr, NEW.serviceid;
    END IF;
 --
 -- (Update with new style foreign key)
 -- This condition keeps the composite foreign key of nodeid, ipaddr, serviceid inSync with the ifserviceid
 -- This usually happens with the Hibernate DAOs decide to change the ifserviceid (MonitoredService) represented
 -- by the outage.
 --
 ELSIF NEW.ifServiceId != OLD.ifServiceId
 THEN
    SELECT ifsvc.nodeId, ifsvc.ipAddr, ifsvc.serviceId INTO NEW.nodeId, NEW.ipAddr, NEW.serviceId
      FROM ifservices ifsvc
     WHERE (ifsvc.id = NEW.ifServiceId);
     
     IF NOT FOUND THEN
        RAISE EXCEPTION 'Outages Trigger Exception, Condition 4: No service found for serviceID: %', NEW.ifServiceId;
     END IF;
 END IF;

 RETURN NEW;

END; $function$ |- | Select | Table | public.ifservices