Difference between revisions of "Public.setifservicekeysonupdate (trigger function)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''triggerfunction | public.setifservicekeysonupdate |- |} <pre> CREATE OR REPLACE FUNCT...)
 
Line 7: Line 7:
 
|-
 
|-
 
|}
 
|}
 +
  
 
<pre>
 
<pre>

Revision as of 14:52, 7 August 2009

wikibot

triggerfunction public.setifservicekeysonupdate


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$