-- hdb_trigger.sql
--
-- HDB v4.50 
--
-- Created:
--         Oct 19, 2000  Stan K.
--         LOCATORS_ITRIG only
-- Modified:
--         Nov 28, 2000  Stan K.
--         Added:
--               LOCATORS_UTRIG
--               LOCATORS_DTRIG      - rewritten
--               NODES_UTRIG
--               NODES_DTRIG         - rewritten
--               TEMPLATE_IDS_DTRIG
--         Jan 24, 2001  Stan K.
--         Added:
--               TEMPL_RECORDS_UTRIG
--         Feb 27, 2001  Stan K.
--               Corrected deletion of TEMPL_FIELDS
--               in TEMPLATE_IDS_DTRIG.
--         Apr 24, 2001  Stan K.
--               Made TEMPL_RECORDS_UTRIG more precise, i.e.
--               added: AND TEMPL_NAME = :OLD.TEMPL_NAME;
--         Mar 26, 2002  Stan K
--         Added:
--               EPICS_DEFINITIONS_IUTRIG
--               MENUS_DTRIG
--               MENUS_UTRIG  
--
PROMPT
PROMPT Creating Triggers on 'LOCATORS'

CREATE OR REPLACE TRIGGER LOCATORS_ITRIG
BEFORE INSERT ON LOCATORS
FOR EACH ROW
WHEN (NEW.ID IS NULL)
DECLARE new_id VARCHAR2(62);
BEGIN
 IF (:NEW.CRATE_NAME IS NOT NULL) THEN
  new_id := LTRIM(RTRIM(:NEW.HOUSE)) || '/' || 
            LTRIM(RTRIM(:NEW.RACK_NAME)) || '/' || 
            LTRIM(RTRIM(:NEW.CRATE_NAME));
 ELSE
  new_id := LTRIM(RTRIM(:NEW.HOUSE)) || '/' || 
            LTRIM(RTRIM(:NEW.RACK_NAME));
 END IF;
 :NEW.ID := new_id;
END;
/
CREATE OR REPLACE TRIGGER LOCATORS_UTRIG
BEFORE UPDATE ON LOCATORS
FOR EACH ROW
--WHEN (NEW.ID = OLD.ID)
DECLARE 
  new_id VARCHAR2(62);
BEGIN
  IF (:NEW.ID = :OLD.ID) THEN
    IF (:NEW.CRATE_NAME IS NOT NULL) THEN
      new_id := LTRIM(RTRIM(:NEW.HOUSE)) || '/' || 
                LTRIM(RTRIM(:NEW.RACK_NAME)) || '/' || 
                LTRIM(RTRIM(:NEW.CRATE_NAME));
    ELSE
      new_id := LTRIM(RTRIM(:NEW.HOUSE)) || '/' || 
                LTRIM(RTRIM(:NEW.RACK_NAME));
    END IF;
    :NEW.ID := new_id;
  END IF;
  IF (:NEW.ID <> :OLD.ID) THEN
    UPDATE DEVICES
    SET LOCATOR_ID = :NEW.ID
    WHERE LOCATOR_ID = :OLD.ID;
  
    UPDATE NODES
    SET LOCATOR_ID = :NEW.ID
    WHERE LOCATOR_ID = :OLD.ID;
  END IF;
END;
/
-- This LOCATORS_DTRIG was well tested and it works.
--
CREATE OR REPLACE TRIGGER LOCATORS_DTRIG
BEFORE DELETE ON LOCATORS
FOR EACH ROW
DECLARE
  dev_id DEVICES.ID%TYPE;
  df_name TEMPL_RECORDS.DFLT_NAME%TYPE;
  CURSOR inst_dev_cur IS
    SELECT ID FROM DEVICES WHERE LOCATOR_ID = :OLD.ID AND
    ID = TEMPL_NAME;
  CURSOR inst_rec_cur (dtmpl CHAR) IS
    SELECT DFLT_NAME FROM TEMPL_RECORDS WHERE TEMPL_NAME = dtmpl;
  CURSOR tmpl_dev_cur IS
    SELECT ID FROM DEVICES WHERE LOCATOR_ID = :OLD.ID AND
    ID <> TEMPL_NAME;
BEGIN
--  DELETE FROM TEMPL_FIELDS WHERE DFLT_NAME IN
--    (SELECT DFLT_NAME FROM TEMPL_RECORDS WHERE TEMPL_NAME IN
--       (SELECT ID FROM DEVICES WHERE LOCATOR_ID = :OLD.ID AND
--        ID = TEMPL_NAME)
--    );
--  DELETE FROM TEMPL_RECORDS WHERE TEMPL_NAME IN
--    (SELECT ID FROM DEVICES WHERE LOCATOR_ID = :OLD.ID AND
--     ID = TEMPL_NAME);
--  DELETE FROM GENERATORS WHERE DEVICE_ID IN
--    (SELECT ID FROM DEVICES WHERE LOCATOR_ID = :OLD.ID AND
--     ID <> TEMPL_NAME);
--  DELETE FROM DEVICES 
--  WHERE LOCATOR_ID = :OLD.ID;
--  DELETE FROM TEMPLATE_IDS WHERE TEMPL_NAME IN
--    (SELECT ID FROM DEVICES WHERE LOCATOR_ID = :OLD.ID AND
--     ID = TEMPL_NAME);
--
-- Loop over instances, i.e. non-templated devices
--
  FOR device_row IN inst_dev_cur LOOP
    dev_id := device_row.ID;
    FOR record_row IN inst_rec_cur(dev_id) LOOP
      df_name := record_row.DFLT_NAME;
      DELETE FROM TEMPL_FIELDS WHERE DFLT_NAME = df_name;
    END LOOP;
    DELETE FROM TEMPL_RECORDS WHERE TEMPL_NAME = dev_id;
    DELETE FROM DEVICES WHERE ID = dev_id;
    DELETE FROM TEMPLATE_IDS WHERE TEMPL_NAME = dev_id;
  END LOOP;
--
-- Loop over truly templated devices
--
  FOR device_row IN tmpl_dev_cur LOOP
    dev_id := device_row.ID;
    DELETE FROM GENERATORS WHERE DEVICE_ID = dev_id;
    DELETE FROM DEVICES WHERE ID = dev_id;
  END LOOP;
  
  DELETE FROM NODES
  WHERE LOCATOR_ID = :OLD.ID;
END;
/
commit
/
PROMPT Creating Triggers on 'NODES'

CREATE OR REPLACE TRIGGER NODES_UTRIG
BEFORE UPDATE OF ID ON NODES
FOR EACH ROW
BEGIN
  UPDATE DEVICES
  SET NODE_ID = :NEW.ID
  WHERE NODE_ID = :OLD.ID;
END;
/
CREATE OR REPLACE TRIGGER NODES_DTRIG
BEFORE DELETE ON NODES
FOR EACH ROW
DECLARE
  dev_id DEVICES.ID%TYPE;
  df_name TEMPL_RECORDS.DFLT_NAME%TYPE;
  CURSOR inst_dev_cur IS
    SELECT ID FROM DEVICES WHERE NODE_ID = :OLD.ID AND
    ID = TEMPL_NAME;
  CURSOR inst_rec_cur (dtmpl CHAR) IS
    SELECT DFLT_NAME FROM TEMPL_RECORDS WHERE TEMPL_NAME = dtmpl;
  CURSOR tmpl_dev_cur IS
    SELECT ID FROM DEVICES WHERE NODE_ID = :OLD.ID AND
    ID <> TEMPL_NAME;
BEGIN
--
-- Loop over instances, i.e. non-templated devices
--
  FOR device_row IN inst_dev_cur LOOP
    dev_id := device_row.ID;
    FOR record_row IN inst_rec_cur(dev_id) LOOP
      df_name := record_row.DFLT_NAME;
      DELETE FROM TEMPL_FIELDS WHERE DFLT_NAME = df_name;
    END LOOP;
    DELETE FROM TEMPL_RECORDS WHERE TEMPL_NAME = dev_id;
    DELETE FROM DEVICES WHERE ID = dev_id;
    DELETE FROM TEMPLATE_IDS WHERE TEMPL_NAME = dev_id;
  END LOOP;
--
-- Loop over truly templated devices
--
  FOR device_row IN tmpl_dev_cur LOOP
    dev_id := device_row.ID;
    DELETE FROM GENERATORS WHERE DEVICE_ID = dev_id;
    DELETE FROM DEVICES WHERE ID = dev_id;
  END LOOP;
END;
/
commit
/
PROMPT Creating Triggers on 'TEMPLATE_IDS'

CREATE OR REPLACE TRIGGER TEMPLATE_IDS_DTRIG
BEFORE DELETE ON TEMPLATE_IDS
FOR EACH ROW
BEGIN
  DELETE FROM PARAMETERS WHERE 
  TEMPL_NAME = :OLD.TEMPL_NAME AND
  DEV_TYPE =   :OLD.DEV_TYPE;
  DELETE FROM TEMPL_FIELDS WHERE 
    TEMPL_NAME = :OLD.TEMPL_NAME AND
    DFLT_NAME IN
    (SELECT DFLT_NAME FROM TEMPL_RECORDS WHERE 
     TEMPL_NAME = :OLD.TEMPL_NAME AND
     DEV_TYPE =   :OLD.DEV_TYPE);
  DELETE FROM TEMPL_RECORDS  WHERE 
  TEMPL_NAME = :OLD.TEMPL_NAME AND
  DEV_TYPE =   :OLD.DEV_TYPE;
  DELETE FROM GENERATORS WHERE DEVICE_ID IN
    (SELECT ID FROM DEVICES WHERE
    TEMPL_NAME = :OLD.TEMPL_NAME AND
    DEV_TYPE_ID = :OLD.DEV_TYPE);
  DELETE FROM DEVICES WHERE
  TEMPL_NAME = :OLD.TEMPL_NAME AND
  DEV_TYPE_ID = :OLD.DEV_TYPE;
END;
/
commit
/
PROMPT Creating Triggers on 'TEMPL_RECORDS'

CREATE OR REPLACE TRIGGER TEMPL_RECORDS_UTRIG
BEFORE UPDATE OF DFLT_NAME ON TEMPL_RECORDS
FOR EACH ROW
BEGIN
  UPDATE TEMPL_FIELDS
  SET DFLT_NAME = :NEW.DFLT_NAME
  WHERE 
  DFLT_NAME = :OLD.DFLT_NAME AND
  TEMPL_NAME = :OLD.TEMPL_NAME;
END;
/
commit
/
PROMPT Creating Triggers on 'EPICS_DEFINITIONS'
 
CREATE OR REPLACE TRIGGER EPICS_DEFINITIONS_IUTRIG
BEFORE INSERT OR UPDATE OF MENU ON EPICS_DEFINITIONS
FOR EACH ROW
WHEN (NEW.MENU IS NOT NULL)
DECLARE cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO cnt FROM MENUS WHERE
  (MENU = :NEW.MENU);
  IF (cnt < 1) THEN
    RAISE_APPLICATION_ERROR (-20500,
    'MENU = ' || :NEW.MENU || ' not found in MENUS table');
  END IF; 
END;
/
commit
/
PROMPT Creating Triggers on 'MENUS'

CREATE OR REPLACE TRIGGER MENUS_DTRIG
BEFORE DELETE ON MENUS
FOR EACH ROW
WHEN (OLD.MENU IS NOT NULL)
DECLARE cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO cnt FROM EPICS_DEFINITIONS WHERE
  (MENU = :OLD.MENU);
  IF (cnt > 0) THEN
    RAISE_APPLICATION_ERROR (-20501,
    'MENU = ' || :OLD.MENU || ' is still referenced in EPICS_DEFINITIONS table');
  END IF;
END;
/
commit
/
CREATE OR REPLACE TRIGGER MENUS_UTRIG
BEFORE UPDATE OF MENU ON MENUS
FOR EACH ROW
BEGIN
  UPDATE EPICS_DEFINITIONS
  SET MENU = :NEW.MENU
  WHERE
  MENU = :OLD.MENU;
END;
/
commit
/
