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

https://github.com/ajakubo1/opennms · SQL · 64 lines · 57 code · 4 blank · 3 comment · 0 complexity · 6e079e7062de3d5bff04b18b47fc43ce 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 setIpInterfaceKeysOnInsertTrigger ON ifServices;
  4. -- Refactoring DB with IpInterface IDs
  5. CREATE OR REPLACE FUNCTION setIpInterfaceKeysOnInsert() RETURNS trigger AS '
  6. BEGIN
  7. -- ifServices must have an IP address that is *not* 0.0.0.0
  8. IF NEW.ipAddr IS NOT NULL AND NEW.ipAddr = ''0.0.0.0''
  9. THEN
  10. RAISE EXCEPTION ''IfServices Trigger Exception, Condition 0: ipAddr of 0.0.0.0 is not allowed in ifServices table'' USING ERRCODE = ''22NMS'';
  11. END IF;
  12. --
  13. -- (Insert with old style foreign key)
  14. -- This condition keeps the ipInterfaceId inSync with the composite foreign key of nodeid, ipaddr, ifindex
  15. -- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
  16. -- code has no knowledge of the new keys
  17. --
  18. IF NEW.ipInterfaceId IS NULL
  19. THEN
  20. SELECT ipif.id INTO NEW.ipInterfaceId
  21. FROM ipinterface ipif
  22. WHERE (ipif.nodeid = NEW.nodeid AND ipif.ipAddr = NEW.ipAddr AND ipif.ipAddr != ''0.0.0.0'');
  23. IF NOT FOUND
  24. THEN
  25. RAISE EXCEPTION ''IfServices Trigger Exception, Condition 1: No IpInterface found for... nodeid: % ipaddr: %'', NEW.nodeid, NEW.ipAddr USING ERRCODE = ''23NMS'';
  26. END IF;
  27. --
  28. -- (Insert with new style foreign key)
  29. -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the ipInterfaceId
  30. -- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
  31. -- the composite key columns
  32. --
  33. ELSIF NEW.ipInterfaceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ipAddr IS NULL)
  34. THEN
  35. SELECT ipif.nodeid, ipif.ipAddr, ipif.ifIndex INTO NEW.nodeid, NEW.ipAddr, NEW.ifIndex
  36. FROM ipinterface ipif
  37. WHERE (ipif.id = NEW.ipInterfaceId);
  38. IF NOT FOUND
  39. THEN
  40. RAISE EXCEPTION ''IfServices Trigger Exception: No ipinterface found for ipInterfaceId: %'', NEW.ipInterfaceId USING ERRCODE = ''23NMS'';
  41. END IF;
  42. IF NEW.ipAddr = ''0.0.0.0''
  43. THEN
  44. RAISE EXCEPTION ''IfServices Trigger Exception, Condition 5: IpInterface found for ipInterfaceId: % has 0.0.0.0 ipAddr'', NEW.ipInterfaceId USING ERRCODE = ''22NMS'';
  45. END IF;
  46. END IF;
  47. RETURN NEW;
  48. END;
  49. ' LANGUAGE 'plpgsql';
  50. CREATE TRIGGER setIpInterfaceKeysOnInsertTrigger
  51. BEFORE INSERT
  52. ON ifServices FOR EACH ROW
  53. EXECUTE PROCEDURE setIpInterfaceKeysOnInsert();