-- TRIGGER INSERT PRINCIPAL
CREATE OR ALTER trigger tbavaliacao_des_modelo_b02i for tbavaliacao_des_modelo
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Reg. incluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBAVALIACAO_DES_MODELO',NEW.id, HISTORICO);
    END
END;

-- TRIGGER INSERT FATOR
CREATE trigger tbavaliacao_des_mod_fator_b02i for tbavaliacao_des_mod_fator
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'FATOR:'||NEW.avaliacao_des_fator_id||' INCLUIDO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBAVALIACAO_DES_MODELO',NEW.avaliacao_des_modelo_id, HISTORICO);
    END
END;

-- TRIGGER DELETE FATOR
CREATE OR ALTER trigger ttbavaliacao_des_mod_fator_b0u for tbavaliacao_des_mod_fator
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 = HISTORICO||' FATOR:'||OLD.avaliacao_des_fator_id||' 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 (
            'TBAVALIACAO_DES_MODELO',
            OLD.avaliacao_des_modelo_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;

-- TRIGGER INSERT FORMAÇÃO
CREATE trigger tbavaliacao_des_mod_formacao_b2 for tbavaliacao_des_mod_formacao
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' FORMACAO:'||NEW.avaliacao_des_formacao_id||' INCLUIDA.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBAVALIACAO_DES_MODELO',NEW.avaliacao_des_modelo_id, HISTORICO);
    END
END;

-- TRIGGER DELETE FORMAÇÃO

CREATE OR ALTER trigger ttbavaliacao_des_mod_form_b0u for tbavaliacao_des_mod_formacao
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 = HISTORICO||' FORMACAO:'||OLD.avaliacao_des_formacao_id||' 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 (
            'TBAVALIACAO_DES_MODELO',
            OLD.avaliacao_des_modelo_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;

-- TRIGGER INSERT RESUMO
CREATE trigger tbavaliacao_des_mod_resumo_b02i for tbavaliacao_des_mod_resumo
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' RESUMO:'||NEW.avaliacao_des_resumo_id||' INCLUIDO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBAVALIACAO_DES_MODELO',NEW.avaliacao_des_modelo_id, HISTORICO);
    END
END;

-- TRIGGER DELETE RESUMO
CREATE OR ALTER trigger ttbavaliacao_des_mod_resu_b0u for tbavaliacao_des_mod_resumo
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 = HISTORICO||' RESUMO:'||OLD.avaliacao_des_resumo_id||' 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 (
            'TBAVALIACAO_DES_MODELO',
            OLD.avaliacao_des_modelo_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;

-- UPDATE PRINCIPAL
CREATE OR ALTER trigger ttbavaliacao_des_modelo_b1u for tbavaliacao_des_modelo
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

    IF (OLD.instrucao_inicial <> NEW.instrucao_inicial) THEN
    BEGIN
        HISTORICO = HISTORICO||' INST. INI.'||OLD.instrucao_inicial||'->'||NEW.instrucao_inicial;
    END

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



