CREATE OR REPLACE TRIGGER TR_UPDATE_EMRMedicationsInfo

AFTER INSERT OR UPDATE OR  DELETE ON EMRUNCODEDMEDICATIONSLKUP

FOR EACH ROW  

DECLARE

      EZEMRX_MED_ID NUMBER(20);

      MED_DICTIONARY VARCHAR2(5);

      ROWCNT NUMBER;

BEGIN

        IF INSERTING THEN

        MED_DICTIONARY := 'U';

        ELSIF UPDATING THEN

        MED_DICTIONARY := 'U';

        ELSIF DELETING THEN

        MED_DICTIONARY := 'U';

        END IF;

 

  SELECT EZEMRXID INTO EZEMRX_MED_ID FROM EMRIDS WHERE PROPERTY_NAME='EZEMRX_MED_ID';

  EZEMRX_MED_ID := EZEMRX_MED_ID +1;

  SELECT COUNT(EZEMRX_MED_ID) INTO ROWCNT FROM EMRCommonMedicationsLkup WHERE MEDICATIONS_ID = :new.UNCODED_MEDICATIONS_ID;

  IF(ROWCNT > 0) THEN

    DELETE FROM EMRCommonMedicationsLkup WHERE medications_id = :new.UNCODED_MEDICATIONS_ID;

  END IF;

  INSERT INTO EMRCommonMedicationsLkup (

              EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 

          COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS

          )

            VALUES (

          EZEMRX_MED_ID,:new.UNCODED_MEDICATIONS_ID, :new.MEDICATION, :new.DOSAGE, :new.DISPENSE, :new.GENERIC_NAME, 

          :new.STRENGTH, rtrim(ltrim(NVL(:new.MEDICATION,'') || ' ' || NVL(:new.STRENGTH,'') || ' ' || NVL(:new.DOSAGE,''))), 

          MED_DICTIONARY,:new.GROUP_ID, :new.STATUS 

          );

  UPDATE EMRIDS SET EZEMRXID = EZEMRX_MED_ID Where Property_Name='EZEMRX_MED_ID';

 END;