createTrig.sql

CREATE TRIGGER PHYSICIAN_ID_UPDATE
BEFORE UPDATE OF PHYS_ID ON PHYSICIAN
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN(LEFT(N.PHYS_ID,1) != 1 AND LEFT(N.PHYS_ID,1) != 2 AND LEFT(N.PHYS_ID,1) != 3 )
    SET N.PHYS_ID = O.PHYS_ID;

CREATE TRIGGER DEPARTMENT_HEAD_UPDATE
BEFORE UPDATE OF DEP_HEAD_ID ON FN45421.DEPARTMENT
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN(LEFT(N.DEP_HEAD_ID,1) != 1 AND LEFT(N.DEP_HEAD_ID,1) != 2 AND LEFT(N.DEP_HEAD_ID,1) != 3 )
    SET N.DEP_HEAD_ID = O.DEP_HEAD_ID;

CREATE TRIGGER PATIENT_ID_UPDATE
BEFORE UPDATE OF PAT_ID ON PATIENT
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN((LEFT(N.PAT_ID,1) < 'A' OR LEFT(N.PAT_ID,1) > 'Z') OR
     (LENGTH(RTRIM(TRANSLATE(RIGHT(N.PAT_ID,7), '*', '0123456789'))) != 0))
    SET N.PAT_ID = O.PAT_ID;

CREATE TRIGGER RECEIPTIONIST_UPDATE
BEFORE UPDATE OF REC_ID ON FN45421.RECEIPTIONIST
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN(LEFT(N.REC_ID,1) != 7)
    SET N.REC_ID = O.REC_ID;

CREATE TRIGGER DRIVER_UPDATE
BEFORE UPDATE OF DRIVER_ID ON FN45421.DRIVER
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN(LEFT(N.DRIVER_ID,1) != 8)
    SET N.DRIVER_ID = O.DRIVER_ID;

CREATE TRIGGER NURSE_UPDATE
BEFORE UPDATE OF NURSE_ID ON FN45421.NURSE
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN(LEFT(N.NURSE_ID,1) != 4 AND LEFT(N.NURSE_ID,1) != 5 AND LEFT(N.NURSE_ID,1) != 6 )
    SET N.NURSE_ID = O.NURSE_ID;

CREATE TRIGGER NURSE_INSERT
AFTER INSERT ON FN45421.NURSE
REFERENCING NEW AS N
FOR EACH ROW
WHEN(LEFT(N.NURSE_ID,1) != 4 AND LEFT(N.NURSE_ID,1) != 5 AND LEFT(N.NURSE_ID,1) != 6 )
    DELETE FROM NURSE
    WHERE NURSE_ID = N.NURSE_ID;

CREATE TRIGGER PHYSICIAN_INSERT
AFTER INSERT ON FN45421.PHYSICIAN
REFERENCING NEW AS N
FOR EACH ROW
WHEN(LEFT(N.PHYS_ID,1) != 1 AND LEFT(N.PHYS_ID,1) != 2 AND LEFT(N.PHYS_ID,1) != 3 )
    DELETE FROM PHYSICIAN
    WHERE PHYS_ID = N.PHYS_ID;

CREATE TRIGGER DEPARTMENT_HEAD_INSERT
AFTER INSERT ON FN45421.DEPARTMENT
REFERENCING NEW AS N
FOR EACH ROW
WHEN(LEFT(N.DEP_HEAD_ID,1) != 1 AND LEFT(N.DEP_HEAD_ID,1) != 2 AND LEFT(N.DEP_HEAD_ID,1) != 3 )
    DELETE FROM DEPARTMENT
    WHERE DEP_HEAD_ID = N.DEP_HEAD_ID;

CREATE TRIGGER RECEIPTIONIST_INSERT
AFTER INSERT ON FN45421.RECEIPTIONIST
REFERENCING NEW AS N
FOR EACH ROW
WHEN(LEFT(N.REC_ID,1) != 7)
    DELETE FROM RECEIPTIONIST
    WHERE REC_ID = N.REC_ID;

CREATE TRIGGER DRIVER_INSERT
AFTER INSERT ON FN45421.DRIVER
REFERENCING NEW AS N
FOR EACH ROW
WHEN(LEFT(N.DRIVER_ID,1) != 8)
    DELETE FROM DRIVER
    WHERE DRIVER_ID = N.DRIVER_ID;

CREATE TRIGGER PATIENT_INSERT
AFTER INSERT ON PATIENT
REFERENCING NEW AS N
FOR EACH ROW
WHEN((LEFT(N.PAT_ID,1) < 'A' OR LEFT(N.PAT_ID,1) > 'Z')
         OR (LENGTH(RTRIM(TRANSLATE(RIGHT(N.PAT_ID,7), '*', '0123456789'))) != 0))
    DELETE FROM PATIENT
    WHERE PAT_ID = N.PAT_ID;

CREATE TRIGGER APPOINTMENT_INSERT
AFTER INSERT ON APPOINTMENT
REFERENCING NEW AS N
FOR EACH ROW
WHEN ((LEFT(N.APP_ID,1) < 'A' OR LEFT(N.APP_ID,1) > 'Z') OR
      (RIGHT(LEFT(N.APP_ID,2),1) < 'A' OR RIGHT(LEFT(N.APP_ID,2),1) > 'Z') OR
      (LENGTH(RTRIM(TRANSLATE(RIGHT(N.APP_ID,4), '*', '0123456789'))) != 0))
    DELETE FROM APPOINTMENT
    WHERE APP_ID=N.APP_ID;

CREATE TRIGGER APPOINTMENT_UPDATE
BEFORE UPDATE OF APP_ID ON APPOINTMENT
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN ((LEFT(N.APP_ID,1) < 'A' OR LEFT(N.APP_ID,1) > 'Z') OR
      (RIGHT(LEFT(N.APP_ID,2),1) < 'A' OR RIGHT(LEFT(N.APP_ID,2),1) > 'Z') OR
      (LENGTH(RTRIM(TRANSLATE(RIGHT(N.APP_ID,4), '*', '0123456789'))) != 0))
    SET N.APP_ID = O.APP_ID;


CREATE TRIGGER MEDICATION_INSERT
AFTER INSERT ON MEDICATION
REFERENCING NEW AS N
FOR EACH ROW
WHEN (((LENGTH(RTRIM(TRANSLATE(LEFT(N.MED_ID,6), '*', 'QWERTYUIOPASDFGHJKLZXCVBNM')))) != 0) OR
      ((LENGTH(RTRIM(TRANSLATE(RIGHT(N.MED_ID,6), '*', '0123456789')))) != 0 ))
    DELETE FROM MEDICATION
    WHERE MED_ID = N.MED_ID;

CREATE TRIGGER MEDICATION_UPDATE
BEFORE UPDATE OF MED_ID ON MEDICATION
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN (((LENGTH(RTRIM(TRANSLATE(LEFT(N.MED_ID,6), '*', 'QWERTYUIOPASDFGHJKLZXCVBNM')))) != 0) OR
      ((LENGTH(RTRIM(TRANSLATE(RIGHT(N.MED_ID,6), '*', '0123456789')))) != 0 ))
    SET N.MED_ID = O.MED_ID;