Public.setsnmpinterfacekeysonupdate (trigger function) - Revision history

Bot at 09:20, 27 August 2011

2011-08-27T09:20:32Z

Bot at 22:39, 24 June 2010

2010-06-24T22:39:53Z

Bot at 07:39, 11 February 2010

2010-02-11T07:39:05Z

Bot at 07:32, 11 February 2010

2010-02-11T07:32:57Z

Bot at 13:53, 7 August 2009

2009-08-07T13:53:01Z

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

 CREATE OR REPLACE F...

2009-08-07T13:16:13Z

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

New page

== wikibot ==

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

<pre>
CREATE OR REPLACE FUNCTION public.setsnmpinterfacekeysonupdate()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN

--
-- (Used for Trigger update with old style foreign key)
-- This condition keeps snmpinterfaceid inSync with the composite foreign key of nodeid, ipaddr, ifindex
-- 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 snmpInterfaceId needs to be updated
--
IF ((NEW.snmpInterfaceId = OLD.snmpInterfaceId OR (NEW.snmpInterfaceId IS NULL AND OLD.snmpInterfaceId IS NULL)) AND
(NEW.nodeId != OLD.nodeId OR NEW.ifIndex != OLD.ifIndex OR (NEW.ifIndex IS NULL AND OLD.ifIndex IS NOT NULL) OR (NEW.ifIndex IS NOT NULL AND OLD.ifIndex IS NULL)))
THEN
IF NEW.ifIndex IS NULL AND NEW.snmpInterfaceId IS NOT NULL
THEN
SELECT NULL INTO NEW.snmpInterfaceId;
ELSIF NEW.ifIndex IS NOT NULL
THEN
SELECT snmpif.id INTO NEW.snmpInterfaceId
FROM snmpinterface snmpif
WHERE (snmpif.nodeid = NEW.nodeid AND snmpif.snmpIfIndex = NEW.ifIndex);

IF NOT FOUND THEN
RAISE EXCEPTION 'IpInterface Trigger Notice, Condition 3: No SnmpInterface found for... nodeid: % ifindex: %', NEW.nodeid, NEW.ifIndex;
END IF;
END IF;

--
-- (Used for Trigger update with new style foreign key)
-- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the snmpinterfaceid
-- This usually happens with the Hibernate DAOs decide to change the snmpinterfaceid represented
-- by the ipinterface.
--
-- We dont match on the case where NEW.snmpInterfaceId IS NULL, because we use it in the WHERE clause.
--
ELSIF (NEW.snmpInterfaceId != OLD.snmpInterfaceId OR (NEW.snmpInterfaceId IS NOT NULL AND OLD.snmpInterfaceId IS NULL))
THEN
SELECT snmpif.nodeId, snmpif.snmpIfIndex INTO NEW.nodeId, NEW.ifIndex
FROM snmpinterface snmpif
WHERE (snmpif.id = NEW.snmpInterfaceId);

IF NOT FOUND THEN
RAISE EXCEPTION 'IpInterface Trigger Notice, Condition 4: No SnmpInterface found for snmpInterfaceId: %', NEW.snmpInterfaceId;
END IF;
END IF;

RETURN NEW;
END;
$function$
</pre>

← Older revision		Revision as of 09:20, 27 August 2011
Line 61:		Line 61:
	$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.snmpinterface_(table)\|public.snmpinterface]]~~
−
−	\|}

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

@@ Line 1: / Line 1: @@
 == wikibot ==
 CREATE OR REPLACE FUNCTION public.setsnmpinterfacekeysonupdate()
   RETURNS trigger
@@ Line 51: / Line 62: @@
 END;
 $function$
 |-
 | Select
 | Table
 | [[public.snmpinterface_(table)|public.snmpinterface]]

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