-- TRIGGERS DE INSERT
-- PRINCIPAL

CREATE trigger tbavaliacao_des_resp_base_b02i for tbavaliacao_des_resp_base
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_RESP_BASE',NEW.id, HISTORICO);
    END
END;

-- INSERÇÃO DE ITENS
CREATE trigger tbavaliacao_des_resp_base_ccub2 for tbavaliacao_des_resp_base_ccust
active before insert position 0
AS
declare variable historico varchar(500);
declare variable descricao_ccusto varchar(500);
BEGIN
  historico = '';
  IF(NEW.id > 0 AND NEW.ccusto_codigo > 0) then
  BEGIN
    select TRIM(T.DESCRICAO) FROM tbcentro_de_custo T WHERE T.codigo = NEW.ccusto_codigo into descricao_ccusto;

    historico = historico||'CCusto '||descricao_ccusto||' Incluido';
  END

  IF(HISTORICO <> '') then
  BEGIN
    execute procedure spi_historico('TBAVALIACAO_DES_RESP_BASE',NEW.avaliacao_des_resp_base_id,historico);
  END
END;

-- DELETE

CREATE OR ALTER trigger ttbaval_des_resp_base_ccust_b0u for tbavaliacao_des_resp_base_ccust
active before update position 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
declare variable descricao_ccusto 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;
        select TRIM(T.DESCRICAO) FROM tbcentro_de_custo T WHERE T.codigo = NEW.ccusto_codigo into descricao_ccusto;
        historico = historico||'CCusto '||descricao_ccusto||' Excluido';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        historico = historico||'CCusto '||descricao_ccusto||' Recuperado';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBAVALIACAO_DES_RESP_BASE',
            NEW.avaliacao_des_resp_base_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 ttbavaliacao_des_resp_bas_b1u for tbavaliacao_des_resp_base
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    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 M. GERAL:'||SUBSTRING(OLD.meta_media_geral from 1 FOR 5)||'->'||SUBSTRING(NEW.meta_media_geral from 1 FOR 5);
    END

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

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

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

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