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

From dbscript Online Help
Jump to: navigation, search
Line 1: Line 1:
 
== wikibot ==
 
== wikibot ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|-
 +
| '''Trigger Function
 +
| public.setifservicekeysoninsert
 +
|-
 +
|}
 +
 +
 +
=== Source ===
 +
<pre>
 
CREATE OR REPLACE FUNCTION public.setifservicekeysoninsert()
 
CREATE OR REPLACE FUNCTION public.setifservicekeysoninsert()
 
  RETURNS trigger
 
  RETURNS trigger
Line 44: Line 55:
 
END;
 
END;
 
$function$
 
$function$
 +
</pre>
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 
|-
 
|-
 
| Select
 
| Select
 
| Table
 
| Table
 
| [[public.ifservices_(table)|public.ifservices]]
 
| [[public.ifservices_(table)|public.ifservices]]
 +
 +
|}

Revision as of 08:38, 11 February 2010

wikibot

Trigger Function public.setifservicekeysoninsert


Source

CREATE OR REPLACE FUNCTION public.setifservicekeysoninsert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

  --
  -- (Used with Trigger Insert with old style foreign key)
  -- This condition keeps the ifServiceID inSync with the composite foreign key of nodeid, ipaddr, serviceid
  -- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
  -- code has no knowledge of the new keys
  --
  IF NEW.ifServiceId IS NULL 
  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 1: No service found for... nodeid: %  ipaddr: %  serviceid: %', NEW.nodeid, NEW.ipAddr, NEW.serviceid;
     END IF;
  
  --
  -- (Used with Trigger Insert with new style foreign key)
  -- This condition keeps the composite foreign key of nodeid, ipaddr, serviceid inSync with the ifserviceid
  -- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
  -- the composite key columns
  --
  ELSIF NEW.ifServiceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ipAddr IS NULL OR NEW.serviceId IS NULL)
  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 2: No service found for serviceID: %', NEW.ifServiceId;
      END IF;

  END IF;
  
  RETURN NEW;
END;
$function$

References

Dependency Type Object Type Referenced Object
Select Table public.ifservices