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

https://github.com/ajakubo1/opennms · SQL · 62 lines · 55 code · 4 blank · 3 comment · 0 complexity · 7a9e016dd8cd0faa99180534706d1872 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 setIpInterfaceKeysOnUpdateTrigger ON ifServices;
  4. -- Refactoring DB with IpInterface IDs
  5. CREATE OR REPLACE FUNCTION setIpInterfaceKeysOnUpdate() 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. -- (Used with Trigger Update with old style foreign key)
  14. -- This condition keeps ipinterfaceid inSync with the composite foreign key of nodeid, ipaddr, ifindex
  15. -- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
  16. -- one or more of the composite key values, the ipInterfaceId needs to be updated
  17. --
  18. IF (NEW.ipInterfaceId = OLD.ipInterfaceId) AND (NEW.nodeId != OLD.nodeId OR NEW.ipAddr != OLD.ipAddr)
  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 THEN
  24. RAISE EXCEPTION ''IfServices Trigger Exception, Condition 3: No IpInterface found for... nodeid: % ipaddr: % '', NEW.nodeid, NEW.ipAddr USING ERRCODE = ''23NMS'';
  25. END IF;
  26. --
  27. -- (Used with Trigger Update with new style foreign key)
  28. -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the ipinterfaceid
  29. -- This usually happens with the Hibernate DAOs decide to change the ipinterfaceid represented
  30. -- by the ifservices.
  31. --
  32. ELSIF NEW.ipInterfaceId != OLD.ipInterfaceId
  33. THEN
  34. SELECT ipif.nodeId, ipif.ipAddr, ipif.ifIndex INTO NEW.nodeId, NEW.ipAddr, NEW.ifIndex
  35. FROM ipinterface ipif
  36. WHERE (ipif.id = NEW.ipInterfaceId);
  37. IF NOT FOUND THEN
  38. RAISE EXCEPTION ''IfServices Trigger Exception, Condition 4: No IpInterface found for ipInterfaceId: %'', NEW.ipInterfaceId USING ERRCODE = ''23NMS'';
  39. END IF;
  40. IF NEW.ipAddr = ''0.0.0.0''
  41. THEN
  42. RAISE EXCEPTION ''IfServices Trigger Exception, Condition 5: IpInterface found for ipInterfaceId: % has 0.0.0.0 ipAddr'', NEW.ipInterfaceId USING ERRCODE = ''22NMS'';
  43. END IF;
  44. END IF;
  45. RETURN NEW;
  46. END;
  47. ' LANGUAGE 'plpgsql';
  48. CREATE TRIGGER setIpInterfaceKeysOnUpdateTrigger
  49. BEFORE UPDATE
  50. ON ifServices FOR EACH ROW
  51. EXECUTE PROCEDURE setIpInterfaceKeysOnUpdate();