Difference between revisions of "Public.setifservicekeysonupdate (trigger function)"
(3 intermediate revisions by the same user not shown) | |||
Line 2: | Line 2: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
− | |- | + | |- valign="top" |
− | | ''' | + | | '''Trigger Function |
| public.setifservicekeysonupdate | | public.setifservicekeysonupdate | ||
− | |||
|} | |} | ||
− | + | === Source === | |
<pre> | <pre> | ||
CREATE OR REPLACE FUNCTION public.setifservicekeysonupdate() | CREATE OR REPLACE FUNCTION public.setifservicekeysonupdate() |
Latest revision as of 10:20, 27 August 2011
wikibot[edit]
Trigger Function | public.setifservicekeysonupdate |
Source[edit]
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$