/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

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