/opennms-base-assembly/src/main/filtered/etc/setIpInterfaceKeysOnUpdateTrigger.sql

 1--First step to refactoring ipInterface, snmpInterface tables to have actual IDs
 2--This trigger puts IpInterface IDs in the IfServices table
 3
 4DROP TRIGGER setIpInterfaceKeysOnUpdateTrigger ON ifServices;
 5
 6-- Refactoring DB with IpInterface IDs
 7
 8CREATE OR REPLACE FUNCTION setIpInterfaceKeysOnUpdate() RETURNS trigger AS '
 9BEGIN
10
11  -- ifServices must have an IP address that is *not* 0.0.0.0
12  IF NEW.ipAddr IS NOT NULL AND NEW.ipAddr = ''0.0.0.0''
13  THEN
14    RAISE EXCEPTION ''IfServices Trigger Exception, Condition 0: ipAddr of 0.0.0.0 is not allowed in ifServices table'' USING ERRCODE = ''22NMS'';
15  END IF;
16
17  --
18  -- (Used with Trigger Update with old style foreign key)
19  -- This condition keeps ipinterfaceid inSync with the composite foreign key of nodeid, ipaddr, ifindex
20  -- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
21  -- one or more of the composite key values, the ipInterfaceId needs to be updated
22  --
23  IF (NEW.ipInterfaceId = OLD.ipInterfaceId) AND (NEW.nodeId != OLD.nodeId OR NEW.ipAddr != OLD.ipAddr) 
24  THEN
25     SELECT ipif.id INTO NEW.ipInterfaceId
26       FROM ipinterface ipif
27       WHERE (ipif.nodeid = NEW.nodeid AND ipif.ipAddr = NEW.ipAddr AND ipif.ipAddr != ''0.0.0.0'');
28       
29     IF NOT FOUND THEN
30        RAISE EXCEPTION ''IfServices Trigger Exception, Condition 3: No IpInterface found for... nodeid: %  ipaddr: % '', NEW.nodeid, NEW.ipAddr USING ERRCODE = ''23NMS'';
31     END IF;
32     
33  --
34  -- (Used with Trigger Update with new style foreign key)
35  -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the ipinterfaceid
36  -- This usually happens with the Hibernate DAOs decide to change the ipinterfaceid represented
37  -- by the ifservices.
38  --
39  ELSIF NEW.ipInterfaceId != OLD.ipInterfaceId
40  THEN
41     SELECT ipif.nodeId, ipif.ipAddr, ipif.ifIndex INTO NEW.nodeId, NEW.ipAddr, NEW.ifIndex
42       FROM ipinterface ipif
43      WHERE (ipif.id = NEW.ipInterfaceId);
44      
45      IF NOT FOUND THEN
46         RAISE EXCEPTION ''IfServices Trigger Exception, Condition 4: No IpInterface found for ipInterfaceId: %'', NEW.ipInterfaceId USING ERRCODE = ''23NMS'';
47      END IF;
48      
49      IF NEW.ipAddr = ''0.0.0.0''
50      THEN
51         RAISE EXCEPTION ''IfServices Trigger Exception, Condition 5: IpInterface found for ipInterfaceId: % has 0.0.0.0 ipAddr'', NEW.ipInterfaceId USING ERRCODE = ''22NMS'';
52      END IF;
53  END IF;
54
55  RETURN NEW;
56END;
57' LANGUAGE 'plpgsql';
58
59CREATE TRIGGER setIpInterfaceKeysOnUpdateTrigger
60   BEFORE UPDATE
61   ON ifServices FOR EACH ROW
62   EXECUTE PROCEDURE setIpInterfaceKeysOnUpdate();