Public.setifservicekeysoninsert (trigger function) - Revision history

Bot at 09:20, 27 August 2011

2011-08-27T09:20:26Z

Bot at 22:39, 24 June 2010

2010-06-24T22:39:46Z

Bot at 07:38, 11 February 2010

2010-02-11T07:38:57Z

Bot at 07:32, 11 February 2010

2010-02-11T07:32:50Z

Bot at 13:52, 7 August 2009

2009-08-07T13:52:49Z

Bot: New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''triggerfunction | public.setifservicekeysoninsert |- |}

 CREATE OR REPLACE FUNCT...

2009-08-07T13:16:03Z

New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''triggerfunction | public.setifservicekeysoninsert |- |} <pre> CREATE OR REPLACE FUNCT...

New page

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''triggerfunction
| public.setifservicekeysoninsert
|-
|}

<pre>
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$
</pre>

← Older revision		Revision as of 09:20, 27 August 2011
Line 54:		Line 54:
	$function$		$function$
	</pre>		</pre>
−
−	~~=== References ===~~
−	~~{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"~~
−	~~\|- style="background:silver"~~
−	~~\| '''Dependency Type~~
−	~~\| '''Object Type~~
−	~~\| '''Referenced Object~~
−
−	~~\|- valign="top"~~
−	~~\| Select~~
−	~~\| Table~~
−	~~\| [[public.ifservices_(table)\|public.ifservices]]~~
−
−	\|}

@@ Line 2: / Line 2: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
-|-
+|- valign="top"
 | '''Trigger Function
 | public.setifservicekeysoninsert
 |}
 === Source ===
@@ Line 64: / Line 62: @@
 | '''Referenced Object
-|-
+|- valign="top"
 | Select
 | Table

@@ Line 1: / Line 1: @@
 == wikibot ==
 CREATE OR REPLACE FUNCTION public.setifservicekeysoninsert()
   RETURNS trigger
@@ Line 44: / Line 55: @@
 END;
 $function$
 |-
 | Select
 | Table
 | [[public.ifservices_(table)|public.ifservices]]

@@ Line 7: / Line 7: @@
 |-
 |}
 <pre>