/opennms-base-assembly/src/main/filtered/etc/setIpInterfaceKeysOnInsertTrigger.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 setIpInterfaceKeysOnInsertTrigger ON ifServices;
 5
 6-- Refactoring DB with IpInterface IDs
 7
 8CREATE OR REPLACE FUNCTION setIpInterfaceKeysOnInsert() 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  -- (Insert with old style foreign key)
19  -- This condition keeps the ipInterfaceId inSync with the composite foreign key of nodeid, ipaddr, ifindex
20  -- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
21  -- code has no knowledge of the new keys
22  --
23  IF NEW.ipInterfaceId IS NULL 
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 
30       THEN
31          RAISE EXCEPTION ''IfServices Trigger Exception, Condition 1: No IpInterface found for... nodeid: %  ipaddr: %'', NEW.nodeid, NEW.ipAddr USING ERRCODE = ''23NMS'';
32       END IF;
33       
34  --
35  -- (Insert with new style foreign key)
36  -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the ipInterfaceId
37  -- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
38  -- the composite key columns
39  --
40
41  ELSIF NEW.ipInterfaceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ipAddr IS NULL)
42  THEN
43     SELECT ipif.nodeid, ipif.ipAddr, ipif.ifIndex INTO NEW.nodeid, NEW.ipAddr, NEW.ifIndex
44       FROM ipinterface ipif
45      WHERE (ipif.id = NEW.ipInterfaceId);
46      
47      IF NOT FOUND
48      THEN
49         RAISE EXCEPTION ''IfServices Trigger Exception: No ipinterface found for ipInterfaceId: %'', NEW.ipInterfaceId USING ERRCODE = ''23NMS'';
50      END IF;
51      
52      IF NEW.ipAddr = ''0.0.0.0''
53      THEN
54         RAISE EXCEPTION ''IfServices Trigger Exception, Condition 5: IpInterface found for ipInterfaceId: % has 0.0.0.0 ipAddr'', NEW.ipInterfaceId USING ERRCODE = ''22NMS'';
55      END IF;
56  END IF;
57  RETURN NEW;
58END;
59' LANGUAGE 'plpgsql';
60
61CREATE TRIGGER setIpInterfaceKeysOnInsertTrigger
62   BEFORE INSERT
63   ON ifServices FOR EACH ROW
64   EXECUTE PROCEDURE setIpInterfaceKeysOnInsert();