ALTER TABLE TBCLINICA
    ADD CLASSIFICACAO INTEGER;

COMMENT ON COLUMN TBCLINICA.CLASSIFICACAO IS
'1 - PREMMIUM
2 - MASTER
3 - STANDARD';

SET TERM ^ ;

CREATE OR ALTER trigger ttbclinica_b09u for tbclinica
active before update position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    HISTORICO = '';

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

    IF (OLD.NUMERO_CNES IS DISTINCT FROM NEW.NUMERO_CNES) THEN
    BEGIN
        HISTORICO = HISTORICO||' CNES: '||OLD.NUMERO_CNES||'->'||NEW.NUMERO_CNES;
    END

    IF (OLD.CNPJ IS DISTINCT FROM NEW.CNPJ) THEN
    BEGIN
        HISTORICO = HISTORICO||' CNPJ: '||OLD.CNPJ||'->'||NEW.CNPJ;
    END

    IF (OLD.TELEFONE IS DISTINCT FROM NEW.TELEFONE) THEN
    BEGIN
        HISTORICO = HISTORICO||' TEL: '||OLD.TELEFONE||'->'||NEW.TELEFONE;
    END

    IF (OLD.CELULAR IS DISTINCT FROM NEW.CELULAR) THEN
    BEGIN
        HISTORICO = HISTORICO||' CEL: '||OLD.CELULAR||'->'||NEW.CELULAR;
    END

    IF (OLD.EMAIL IS DISTINCT FROM NEW.EMAIL) THEN
    BEGIN
        HISTORICO = HISTORICO||' EMAIL: '||OLD.EMAIL||'->'||NEW.EMAIL;
    END

    IF (OLD.ENDERECO_CEP IS DISTINCT FROM NEW.ENDERECO_CEP) THEN
    BEGIN
        HISTORICO = HISTORICO||' CEP: '||OLD.ENDERECO_CEP||'->'||NEW.ENDERECO_CEP;
    END

    IF (OLD.ENDERECO_LOGRADOURO IS DISTINCT FROM NEW.ENDERECO_LOGRADOURO) THEN
    BEGIN
        HISTORICO = HISTORICO||' LOGRAD: '||OLD.ENDERECO_LOGRADOURO||'->'||NEW.ENDERECO_LOGRADOURO;
    END

    IF (OLD.ENDERECO_CIDADE_ID IS DISTINCT FROM NEW.ENDERECO_CIDADE_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' CID: '||OLD.ENDERECO_CIDADE_ID||'->'||NEW.ENDERECO_CIDADE_ID;
    END

    IF (OLD.ENDERECO_UF IS DISTINCT FROM NEW.ENDERECO_UF) THEN
    BEGIN
        HISTORICO = HISTORICO||' UF: '||OLD.ENDERECO_UF||'->'||NEW.ENDERECO_UF;
    END

    IF (OLD.ENDERECO_BAIRRO IS DISTINCT FROM NEW.ENDERECO_BAIRRO) THEN
    BEGIN
        HISTORICO = HISTORICO||' BAIRRO: '||OLD.ENDERECO_BAIRRO||'->'||NEW.ENDERECO_BAIRRO;
    END

    IF (OLD.ENDERECO_NUMERO IS DISTINCT FROM NEW.ENDERECO_NUMERO) THEN
    BEGIN
        HISTORICO = HISTORICO||' NR: '||OLD.ENDERECO_NUMERO||'->'||NEW.ENDERECO_NUMERO;
    END

    IF (OLD.ENDERECO_COMPLEMENTO IS DISTINCT FROM NEW.ENDERECO_COMPLEMENTO) THEN
    BEGIN
        HISTORICO = HISTORICO||' COMPLEM: '||OLD.ENDERECO_COMPLEMENTO||'->'||NEW.ENDERECO_COMPLEMENTO;
    END

    IF (OLD.CLASSIFICACAO IS DISTINCT FROM NEW.CLASSIFICACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CLASS: '||OLD.CLASSIFICACAO||'->'||NEW.CLASSIFICACAO;
    END

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

END
^

SET TERM ; ^

