/opennms-base-assembly/src/main/filtered/etc/setSnmpInterfaceKeysOnInsertTrigger.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 setSnmpInterfaceKeysOnInsertTrigger ON ipInterface;
 5
 6--DB Refactoring Function to introduce SnmpInterface IDs
 7
 8CREATE OR REPLACE FUNCTION setSnmpInterfaceKeysOnInsert() RETURNS trigger AS '
 9BEGIN
10
11  --
12  -- (Used for Trigger insert with old style foreign key)
13  -- This condition keeps the snmpInterfaceId inSync with the composite foreign key of nodeid, ipaddr, ifindex
14  -- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
15  -- code has no knowledge of the new keys
16  --
17  IF NEW.snmpInterfaceId IS NULL 
18  THEN
19     IF NEW.ifIndex IS NOT NULL
20     THEN
21       SELECT snmpif.id INTO NEW.snmpInterfaceId
22         FROM snmpinterface snmpif
23         WHERE (snmpif.nodeid = NEW.nodeid AND snmpif.snmpIfIndex = NEW.ifIndex);
24       
25       IF NOT FOUND 
26       THEN
27         RAISE EXCEPTION ''IpInterface Trigger Notice, Condition 1: No SnmpInterface found for... nodeid: % ifindex: %'', NEW.nodeid, NEW.ifIndex USING ERRCODE = ''23NMS'';
28       END IF;
29     END IF;
30       
31  --
32  -- (Used for Insert with new style foreign key)
33  -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the SnmpInterfaceId
34  -- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
35  -- the composite key columns
36  --
37
38  ELSIF NEW.snmpInterfaceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ifIndex IS NULL)
39  THEN
40     SELECT snmpif.nodeid, snmpif.snmpIfIndex INTO NEW.nodeid, NEW.ifIndex
41       FROM snmpinterface snmpif
42      WHERE (snmpif.id = NEW.snmpInterfaceId);
43      
44      IF NOT FOUND
45      THEN
46         RAISE EXCEPTION ''IpInterface Trigger Notice: No SnmpInterface found for snmpInterfaceId: %'', NEW.snmpInterfaceId USING ERRCODE = ''23NMS'';
47      END IF;
48  END IF;
49  RETURN NEW;
50END;
51' LANGUAGE 'plpgsql';
52
53CREATE TRIGGER setSnmpInterfaceKeysOnInsertTrigger
54   BEFORE INSERT
55   ON ipInterface FOR EACH ROW
56   EXECUTE PROCEDURE setSnmpInterfaceKeysOnInsert();