/opennms-base-assembly/src/main/filtered/etc/setSnmpInterfaceKeysOnInsertTrigger.sql
https://github.com/ajakubo1/opennms · SQL · 56 lines · 49 code · 4 blank · 3 comment · 0 complexity · 5ff26ebd53d510002eff8594a12b0fdb MD5 · raw file
- --First step to refactoring ipInterface, snmpInterface tables to have actual IDs
- --This trigger puts IpInterface IDs in the IfServices table
- DROP TRIGGER setSnmpInterfaceKeysOnInsertTrigger ON ipInterface;
- --DB Refactoring Function to introduce SnmpInterface IDs
- CREATE OR REPLACE FUNCTION setSnmpInterfaceKeysOnInsert() RETURNS trigger AS '
- BEGIN
- --
- -- (Used for Trigger insert with old style foreign key)
- -- This condition keeps the snmpInterfaceId inSync with the composite foreign key of nodeid, ipaddr, ifindex
- -- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
- -- code has no knowledge of the new keys
- --
- IF NEW.snmpInterfaceId IS NULL
- THEN
- IF 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 1: No SnmpInterface found for... nodeid: % ifindex: %'', NEW.nodeid, NEW.ifIndex USING ERRCODE = ''23NMS'';
- END IF;
- END IF;
-
- --
- -- (Used for Insert with new style foreign key)
- -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the SnmpInterfaceId
- -- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
- -- the composite key columns
- --
- ELSIF NEW.snmpInterfaceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ifIndex 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: No SnmpInterface found for snmpInterfaceId: %'', NEW.snmpInterfaceId USING ERRCODE = ''23NMS'';
- END IF;
- END IF;
- RETURN NEW;
- END;
- ' LANGUAGE 'plpgsql';
- CREATE TRIGGER setSnmpInterfaceKeysOnInsertTrigger
- BEFORE INSERT
- ON ipInterface FOR EACH ROW
- EXECUTE PROCEDURE setSnmpInterfaceKeysOnInsert();