CREATE OR ALTER trigger ttbpront_diagnostico_b02i for tbprontuario_diagnostico
active before insert position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE DESC_CID VARCHAR(100);
DECLARE VARIABLE CODIGO_CID VARCHAR(5);
DECLARE VARIABLE DATA_ATENDIMENTO DATE;
DECLARE VARIABLE MEDICO INTEGER;

BEGIN


   HISTORICO = '';
   SELECT FIRST 1 C.CODIGO    FROM TBCID C WHERE C.ID = NEW.CID_ID INTO CODIGO_CID;
   SELECT FIRST 1 C.DESCRICAO FROM TBCID C WHERE C.ID = NEW.CID_ID INTO DESC_CID;

   SELECT FIRST 1 T.DATA_ATENDIMENTO FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = NEW.ATENDIMENTO_ID INTO DATA_ATENDIMENTO;
   SELECT FIRST 1 T.MEDICO_ID        FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = NEW.ATENDIMENTO_ID INTO MEDICO;

   HISTORICO = HISTORICO||'ATENDIMENTO ALTERADO: '||FORMATDATE(DATA_ATENDIMENTO)||', MÉDICO: '||MEDICO||', DIAGNOSTICO INCLUIDO: '||NEW.HIPOTESE_DIAGNOSTICA||', CID: '||CODIGO_CID||' - '||DESC_CID;

   IF (HISTORICO <> '') THEN
       BEGIN
           INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
      'TBPRONTUARIO', (SELECT FIRST 1 T.PRONTUARIO_ID FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = NEW.ATENDIMENTO_ID),
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
       END
END;


CREATE OR ALTER trigger ttbpront_diagnostico_b01d for tbprontuario_diagnostico
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE DESC_CID VARCHAR(100);
DECLARE VARIABLE CODIGO_CID VARCHAR(5);
DECLARE VARIABLE DATA_ATENDIMENTO DATE;
DECLARE VARIABLE MEDICO INTEGER;
BEGIN


   HISTORICO = '';
   SELECT FIRST 1 C.CODIGO FROM TBCID C WHERE C.ID = OLD.CID_ID INTO CODIGO_CID;
   SELECT FIRST 1 C.DESCRICAO FROM TBCID C WHERE C.ID = OLD.CID_ID INTO DESC_CID;

   SELECT FIRST 1 T.DATA_ATENDIMENTO FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = OLD.ATENDIMENTO_ID INTO DATA_ATENDIMENTO;
   SELECT FIRST 1 T.MEDICO_ID        FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = OLD.ATENDIMENTO_ID INTO MEDICO;

   HISTORICO = HISTORICO||'ATENDIMENTO ALTERADO: '||FORMATDATE(DATA_ATENDIMENTO)||', MÉDICO: '||MEDICO||', DIAGNOSTICO EXCLUÍDO: '||OLD.HIPOTESE_DIAGNOSTICA||', CID: '||CODIGO_CID||' - '||DESC_CID;

   IF (HISTORICO <> '') THEN
       BEGIN
           INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
      'TBPRONTUARIO', (SELECT FIRST 1 T.PRONTUARIO_ID FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = OLD.ATENDIMENTO_ID),
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
       END
END;



CREATE OR ALTER trigger ttbpront_diagnostico_b01u for tbprontuario_diagnostico
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE DESC_CID VARCHAR(100);
DECLARE VARIABLE CODIGO_CID VARCHAR(5);
DECLARE VARIABLE DATA_ATENDIMENTO DATE;
DECLARE VARIABLE MEDICO INTEGER;
BEGIN

   HISTORICO = '';

   SELECT FIRST 1 C.DESCRICAO FROM TBCID C WHERE C.ID = NEW.CID_ID INTO DESC_CID;
   SELECT FIRST 1 C.CODIGO    FROM TBCID C WHERE C.ID = NEW.CID_ID INTO CODIGO_CID;

   SELECT FIRST 1 T.DATA_ATENDIMENTO FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = NEW.ATENDIMENTO_ID INTO DATA_ATENDIMENTO;
   SELECT FIRST 1 T.MEDICO_ID        FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = NEW.ATENDIMENTO_ID INTO MEDICO;

   IF (OLD.HIPOTESE_DIAGNOSTICA <> NEW.HIPOTESE_DIAGNOSTICA) THEN
       BEGIN
       HISTORICO = HISTORICO||' HIP.DIAGNOSTICA: '||OLD.HIPOTESE_DIAGNOSTICA||'->'||NEW.HIPOTESE_DIAGNOSTICA;
       END
   IF (OLD.CID_ID <> NEW.CID_ID) THEN
       BEGIN
       HISTORICO = HISTORICO||' CID:'||OLD.CID_ID||' - '||CODIGO_CID||'->'||NEW.CID_ID||' - '||CODIGO_CID;
       END

   IF (HISTORICO <> '') THEN
       BEGIN

        HISTORICO = ' DIAGNOSTICO ALTERADO: ATENDIMENTO: '||FORMATDATE(DATA_ATENDIMENTO)||', MEDICO: '||MEDICO||', '||HISTORICO;

       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
      'TBPRONTUARIO', (SELECT FIRST 1 T.PRONTUARIO_ID FROM TBPRONTUARIO_ATENDIMENTO T WHERE T.ID = OLD.ATENDIMENTO_ID),
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
       END
END;





