ALTER TABLE TBPERFIL_CLIENTE_DESCONTO
    ADD DATA_INICIAL DATE,
    ADD DATA_FINAL DATE,
    ADD USUARIO_ID INTEGER,
    ADD VALOR_MINIMO NUMERIC(15,2),
    ADD OBSERVACAO BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    ADD DATAHORA TIMESTAMP DEFAULT 'NOW';

SET TERM ^ ;

CREATE OR ALTER trigger ttbperfil_cliente_desconto_b02u for tbperfil_cliente_desconto
active before update position 0
AS
BEGIN
   IF(NEW.DATAHORA IS NULL OR (NEW.DATAHORA <> CURRENT_TIMESTAMP)) THEN
       NEW.DATAHORA = CURRENT_TIMESTAMP;
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbperfil_cliente_desconto_b01u for tbperfil_cliente_desconto
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE PERFIL_DESC VARCHAR(100);
DECLARE VARIABLE PDV_DESC VARCHAR(100);

BEGIN

    HISTORICO   = '';
    PERFIL_DESC = COALESCE((SELECT FIRST 1 P.DESCRICAO FROM TBPERFIL P WHERE P.ID = NEW.PERFIL), '');
    PDV_DESC    = COALESCE((SELECT FIRST 1 D.DESCRICAO FROM TBPDV D WHERE D.ID = NEW.PDV_ID), '');


        IF ( OLD.ORIGEM IS DISTINCT FROM NEW.ORIGEM ) THEN
        BEGIN
            HISTORICO = HISTORICO||' ORIGEM: '||COALESCE(OLD.ORIGEM, '')||' - '||COALESCE(NEW.ORIGEM, '');
        END
    
        IF ( OLD.PERFIL IS DISTINCT FROM NEW.PERFIL ) THEN
        BEGIN
            HISTORICO = HISTORICO||' PERFIL: '||COALESCE(OLD.PERFIL, '')||' - '||COALESCE(NEW.PERFIL, '');
        END

        IF ( OLD.PDV_ID IS DISTINCT FROM NEW.PDV_ID ) THEN
        BEGIN
            HISTORICO = HISTORICO||' PERFIL: '||COALESCE(OLD.PDV_ID, '')||' - '||COALESCE(NEW.PDV_ID, '');
        END

        IF ( OLD.VALOR_DESCONTO IS DISTINCT FROM NEW.VALOR_DESCONTO ) THEN
        BEGIN
            HISTORICO = HISTORICO||' VR.DESC: '||CAST(COALESCE(OLD.VALOR_DESCONTO, 0) AS NUMERIC (15,2))||' - '||CAST(COALESCE(NEW.VALOR_DESCONTO, 0) AS NUMERIC (15,2));
        END

        IF ( OLD.DATA_INICIAL IS DISTINCT FROM NEW.DATA_INICIAL ) THEN
        BEGIN
            HISTORICO = HISTORICO||' DT.INI: '||COALESCE(FORMATDATE(OLD.DATA_INICIAL), '')||' - '||COALESCE(FORMATDATE(NEW.DATA_INICIAL), '');
        END

        IF ( OLD.DATA_FINAL IS DISTINCT FROM NEW.DATA_FINAL ) THEN
        BEGIN
            HISTORICO = HISTORICO||' DT.FIM: '||COALESCE(FORMATDATE(OLD.DATA_FINAL), '')||' - '||COALESCE(FORMATDATE(NEW.DATA_FINAL), '');
        END

        IF ( OLD.VALOR_MINIMO IS DISTINCT FROM NEW.VALOR_MINIMO ) THEN
        BEGIN
            HISTORICO = HISTORICO||' VR.MIN: '||CAST(COALESCE(OLD.VALOR_MINIMO, 0) AS NUMERIC (15,2))||' - '||CAST(COALESCE(NEW.VALOR_MINIMO, 0) AS NUMERIC (15,2));
        END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = ' DESCONTO: ORIGEM:'||OLD.ORIGEM||', DESCRICAO: '||IIF(OLD.ORIGEM = 1, OLD.PERFIL||' - '||PERFIL_DESC, FN_LPAD(OLD.PDV_ID, 4, '0')||' - '||PDV_DESC)||' ALTERADO: '||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBEMPRESA',NEW.CLIENTE_ID, HISTORICO);
    END
END
^

SET TERM ; ^









