CREATE OR ALTER TRIGGER TTBFORMULARIO_TIPO_B0I FOR TBFORMULARIO_TIPO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF ( (NEW.ID IS NULL) OR (NEW.ID = 0) ) THEN
        NEW.ID = GEN_ID(GTBFORMULARIO_TIPO, 1);
END;

COMMENT ON TRIGGER TTBFORMULARIO_TIPO_B0I IS 'Gerar ID automaticamente';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_TIPO_B0U FOR TBFORMULARIO_TIPO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBFORMULARIO_TIPO',
            NEW.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;

COMMENT ON TRIGGER TTBFORMULARIO_TIPO_B0U IS
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';




---------
---------




CREATE OR ALTER TRIGGER TTBFORMULARIO_B0I FOR TBFORMULARIO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBFORMULARIO, 1);
END;

COMMENT ON TRIGGER TTBFORMULARIO_B0I IS 'Gerar ID automaticamente';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_B0U FOR TBFORMULARIO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBFORMULARIO',
            NEW.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;

COMMENT ON TRIGGER TTBFORMULARIO_B0U IS
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_B1U FOR TBFORMULARIO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.FORMULARIO_TIPO_ID <> NEW.FORMULARIO_TIPO_ID) THEN
    BEGIN
       HISTORICO = HISTORICO||' TIPO FORM.:'||OLD.FORMULARIO_TIPO_ID||'->'||NEW.FORMULARIO_TIPO_ID;
    END

    IF (OLD.TITULO <> NEW.TITULO) THEN
    BEGIN
        HISTORICO = HISTORICO||' TITULO:'||OLD.TITULO||'->'||NEW.TITULO;
    END

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
       HISTORICO = HISTORICO||' DESC.:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBFORMULARIO', OLD.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;

COMMENT ON TRIGGER TTBFORMULARIO_B1U IS 'Registra historico de alteracoes';


-----


CREATE OR ALTER TRIGGER TTBFORMULARIO_B2U FOR TBFORMULARIO
ACTIVE BEFORE UPDATE POSITION 2
AS
begin

    IF (NEW.STATUSEXCLUSAO = '1') THEN
    BEGIN

        --Excluir destinatarios.
        UPDATE
            TBFORMULARIO_DESTINATARIO FD
        SET
            FD.STATUSEXCLUSAO = '1'
        WHERE
            FD.FORMULARIO_ID = OLD.ID;

        --Excluir perguntas.
        UPDATE
            TBFORMULARIO_PERGUNTA FP
        SET
            FP.STATUSEXCLUSAO = '1'
        WHERE
            FP.FORMULARIO_ID = OLD.ID;

        --Excluir alternativas.
        UPDATE
            TBFORMULARIO_ALTERNATIVA FA
        SET
            FA.STATUSEXCLUSAO = '1'
        WHERE
            FA.FORMULARIO_ID = OLD.ID;

        --Excluir respostas.
        UPDATE
            TBFORMULARIO_RESPOSTA FR
        SET
            FR.STATUSEXCLUSAO = '1'
        WHERE
            FR.FORMULARIO_ID = OLD.ID;

    END

end;

COMMENT ON TRIGGER TTBFORMULARIO_B2U IS 'Acoes ao excluir um formulario:
- Excluir destinatarios;
- Excluir perguntas;
- Excluir alternativas;
- Excluir respostas.';




---------
---------




CREATE OR ALTER TRIGGER TTBFORMULARIO_DESTINATARIO_B0I FOR TBFORMULARIO_DESTINATARIO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBFORMULARIO_DESTINATARIO, 1);
END;

COMMENT ON TRIGGER TTBFORMULARIO_DESTINATARIO_B0I IS 'Gerar ID automaticamente';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_DESTINATARIO_B0U FOR TBFORMULARIO_DESTINATARIO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBFORMULARIO_DESTINATARIO',
            NEW.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;

COMMENT ON TRIGGER TTBFORMULARIO_DESTINATARIO_B0U IS
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_DESTINATARIO_B1U FOR TBFORMULARIO_DESTINATARIO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.USUARIO_ID <> NEW.USUARIO_ID) THEN
    BEGIN
       HISTORICO = HISTORICO||' USUARIO:'||OLD.USUARIO_ID||'->'||NEW.USUARIO_ID;
    END

    IF (OLD.CCUSTO <> NEW.CCUSTO) THEN
    BEGIN
       HISTORICO = HISTORICO||' CCUSTO:'||OLD.CCUSTO||'->'||NEW.CCUSTO;
    END

    IF (OLD.PESO_RESPOSTA <> NEW.PESO_RESPOSTA) THEN
    BEGIN
       HISTORICO = HISTORICO||' PESO:'||OLD.PESO_RESPOSTA||'->'||NEW.PESO_RESPOSTA;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBFORMULARIO_DESTINATARIO', OLD.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;

COMMENT ON TRIGGER TTBFORMULARIO_DESTINATARIO_B1U IS 'Registra historico de alteracoes.';



---------
---------




CREATE OR ALTER TRIGGER TTBFORMULARIO_RESPOSTA_TIPO_B0I FOR TBFORMULARIO_RESPOSTA_TIPO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBFORMULARIO_RESPOSTA_TIPO, 1);
END;

COMMENT ON TRIGGER TTBFORMULARIO_RESPOSTA_TIPO_B0I IS 'Gerar ID automaticamente';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_RESPOSTA_TIPO_B0U FOR TBFORMULARIO_RESPOSTA_TIPO
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBFORMULARIO_RESPOSTA_TIPO',
            NEW.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;

COMMENT ON TRIGGER TTBFORMULARIO_RESPOSTA_TIPO_B0U IS
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';




---------
---------




CREATE OR ALTER TRIGGER TTBFORMULARIO_PERGUNTA_B0I FOR TBFORMULARIO_PERGUNTA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF ( (NEW.ID IS NULL) OR (NEW.ID = 0) ) THEN
        NEW.ID = GEN_ID(GTBFORMULARIO_PERGUNTA, 1);
END;

COMMENT ON TRIGGER TTBFORMULARIO_PERGUNTA_B0I IS 'Gerar ID automaticamente';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_PERGUNTA_B0U FOR TBFORMULARIO_PERGUNTA
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBFORMULARIO_PERGUNTA',
            NEW.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;

COMMENT ON TRIGGER TTBFORMULARIO_PERGUNTA_B0U IS
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_PERGUNTA_B1U FOR TBFORMULARIO_PERGUNTA
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
       HISTORICO = HISTORICO||' DESC.:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (OLD.FORMULARIO_RESPOSTA_TIPO_ID <> NEW.FORMULARIO_RESPOSTA_TIPO_ID) THEN
    BEGIN
       HISTORICO = HISTORICO||' TIPO RESP.:'||OLD.FORMULARIO_RESPOSTA_TIPO_ID||'->'||NEW.FORMULARIO_RESPOSTA_TIPO_ID;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBFORMULARIO_PERGUNTA', OLD.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;

COMMENT ON TRIGGER TTBFORMULARIO_PERGUNTA_B1U IS 'Registra historico de alteracoes.';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_PERGUNTA_B0D FOR TBFORMULARIO_PERGUNTA
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
    --Excluir alternativas.
    DELETE FROM TBFORMULARIO_ALTERNATIVA FA
    WHERE FA.FORMULARIO_ID = OLD.ID;

    --Excluir respostas.
    DELETE FROM TBFORMULARIO_RESPOSTA FR
    WHERE FR.FORMULARIO_ID = OLD.ID;
END;

COMMENT ON TRIGGER TTBFORMULARIO_PERGUNTA_B0D IS 'Acoes ao excluir uma pergunta:
- Excluir alternativas;
- Excluir respostas.';



---------
---------




CREATE OR ALTER TRIGGER TTBFORMULARIO_ALTERNATIVA_B0I FOR TBFORMULARIO_ALTERNATIVA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF ( (NEW.ID IS NULL) OR (NEW.ID = 0) ) THEN
        NEW.ID = GEN_ID(GTBFORMULARIO_ALTERNATIVA, 1);
END;

COMMENT ON TRIGGER TTBFORMULARIO_ALTERNATIVA_B0I IS 'Gerar ID automaticamente';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_ALTERNATIVA_B0U FOR TBFORMULARIO_ALTERNATIVA
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBFORMULARIO_ALTERNATIVA',
            NEW.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;

COMMENT ON TRIGGER TTBFORMULARIO_ALTERNATIVA_B0U IS
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_ALTERNATIVA_B1U FOR TBFORMULARIO_ALTERNATIVA
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
       HISTORICO = HISTORICO||' DESC.:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBFORMULARIO_ALTERNATIVA', OLD.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;

COMMENT ON TRIGGER TTBFORMULARIO_ALTERNATIVA_B1U IS 'Registra historico de alteracoes';




---------
---------




CREATE OR ALTER TRIGGER TTBFORMULARIO_RESPOSTA_B0I FOR TBFORMULARIO_RESPOSTA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBFORMULARIO_RESPOSTA, 1);
END;

COMMENT ON TRIGGER TTBFORMULARIO_RESPOSTA_B0I IS 'Gerar ID automaticamente';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_RESPOSTA_A0I FOR TBFORMULARIO_RESPOSTA
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN

    IF(NEW.COLABORADOR_ID IS NULL) THEN
    BEGIN
        UPDATE TBFORMULARIO_DESTINATARIO FD
        SET FD.STATUS_RESPOSTA = '1'
        WHERE
            FD.FORMULARIO_ID = NEW.FORMULARIO_ID
        AND FD.USUARIO_ID = NEW.USUARIO_ID;
    END

END;

COMMENT ON TRIGGER TTBFORMULARIO_RESPOSTA_A0I IS 'Atualizar status de resposta do destinatario.';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_RESPOSTA_B0U FOR TBFORMULARIO_RESPOSTA
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBFORMULARIO_RESPOSTA',
            NEW.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;

COMMENT ON TRIGGER TTBFORMULARIO_RESPOSTA_B0U IS
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_RESPOSTA_B1U FOR TBFORMULARIO_RESPOSTA
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
       HISTORICO = HISTORICO||' DESC.:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (OLD.ALTERNATIVA_ESCOLHIDA_ID <> NEW.ALTERNATIVA_ESCOLHIDA_ID) THEN
    BEGIN
       HISTORICO = HISTORICO||' ALT.:'||OLD.ALTERNATIVA_ESCOLHIDA_ID||'->'||NEW.ALTERNATIVA_ESCOLHIDA_ID;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBFORMULARIO_RESPOSTA', OLD.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;

COMMENT ON TRIGGER TTBFORMULARIO_RESPOSTA_B1U IS 'Registra historico de alteracoes.';




-----
-----




CREATE OR ALTER TRIGGER TTBFORMULARIO_ALTERN_NIVEL_B0I FOR TBFORMULARIO_ALTERN_NIVEL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF ( (NEW.ID IS NULL) OR (NEW.ID = 0) ) THEN
        NEW.ID = GEN_ID(GTBFORMULARIO_ALTERN_NIVEL, 1);
END;

COMMENT ON TRIGGER TTBFORMULARIO_ALTERN_NIVEL_B0I IS 'Gerar ID automaticamente';

-----

CREATE OR ALTER TRIGGER TTBFORMULARIO_ALTERN_NIVEL_B0U FOR TBFORMULARIO_ALTERN_NIVEL
ACTIVE BEFORE UPDATE POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBFORMULARIO_ALTERN_NIVEL',
            NEW.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;

COMMENT ON TRIGGER TTBFORMULARIO_ALTERN_NIVEL_B0U IS
'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao';




-----
-----




CREATE OR ALTER TRIGGER TTBFORMULARIO_DESTINATARIO_B2U FOR TBFORMULARIO_DESTINATARIO
ACTIVE BEFORE UPDATE POSITION 2
AS
BEGIN

    /**
     * Se estiver reabilitando o usuario para responder,
     * a resposta do usuario em questao devera ser excluida.
     */
    IF (OLD.STATUS_RESPOSTA = '1' AND NEW.STATUS_RESPOSTA = '0') THEN
    BEGIN
        UPDATE TBFORMULARIO_RESPOSTA FR
        SET FR.STATUSEXCLUSAO = '1'
        WHERE FR.FORMULARIO_ID = OLD.FORMULARIO_ID
        AND FR.USUARIO_ID = OLD.USUARIO_ID;
    END

END;

COMMENT ON TRIGGER TTBFORMULARIO_DESTINATARIO_B2U IS 'Reabilitar usuario para responder.';