SET TERM ^ ;

CREATE OR ALTER trigger ttbuf_b01i for tbuf
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    HISTORICO = '';

    IF ( NEW.UF <> '' OR (NEW.UF IS NOT NULL) ) THEN
    BEGIN
        HISTORICO = HISTORICO||' REGISTRO INCLUIDO';
    END

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

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbuf_b01d for tbuf
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    HISTORICO = '';

    IF ( OLD.UF <> '' OR (OLD.UF IS NOT NULL) ) THEN
    BEGIN
        HISTORICO = HISTORICO||' REGISTRO EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBUF',OLD.ID, HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbuf_b02d for tbuf
active before delete position 0
AS
BEGIN
   IF(OLD.UF <> '' OR (OLD.UF IS NOT NULL) ) THEN
   BEGIN
      DELETE FROM TBTRIBUTACAO_ICMS_INTERESTADUAL WHERE UF_ORIGEM = OLD.UF;
   END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ttbuf_b02i for tbuf
active before insert position 1
AS
DECLARE VARIABLE UF VARCHAR(2);
BEGIN
   UF = COALESCE((SELECT FIRST 1 A.UF FROM TBUF A WHERE TRIM(A.UF) = TRIM(NEW.UF)),'');
   IF(UF <> '') THEN
   BEGIN
         EXCEPTION "..." ' UF:'||NEW.UF||' JÁ CADASTRADA!';
   END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ttbuf_b01u for tbuf
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
    HISTORICO = '';

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

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

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

    IF ( NEW.CODIGO_IBGE IS DISTINCT FROM OLD.CODIGO_IBGE) THEN
    BEGIN
        HISTORICO = HISTORICO||' COD.IBGE'||COALESCE(OLD.CODIGO_IBGE, '')||'->'||COALESCE(NEW.CODIGO_IBGE, '');
    END

    IF ( NEW.ALIQUOTA_ICMS IS DISTINCT FROM OLD.ALIQUOTA_ICMS) THEN
    BEGIN
        HISTORICO = HISTORICO||' ALIQ.INT.ICMS:'||CAST(COALESCE(OLD.ALIQUOTA_ICMS, 0) AS NUMERIC (15,2))||'->'||CAST(COALESCE(NEW.ALIQUOTA_ICMS, 0) AS NUMERIC (15,2));
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = ' REGISTRO ALTERADO: '||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBUF',NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^



--------------------------------------------------------------------------------------------------------
SET TERM ^ ;

CREATE OR ALTER trigger ttbtributacao_icms_inter_b03i for tbtributacao_icms_interestadual
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE TIPO_HISTORICO INTEGER;
--DECLARE VARIABLE UF_DESCRICAO VARCHAR(100);
BEGIN

    HISTORICO = '';
--    UF_DESCRICAO = COALESCE((SELECT FIRST 1 P.DESCRICAO FROM TBUF P WHERE P.UF = NEW.UF_ORIGEM), '');

    IF (NEW.ID > 0 ) THEN
    BEGIN
        HISTORICO = HISTORICO||' TRIBUTACAO ICMS INCLUIDA: '||FN_LPAD(NEW.ID, 4, '0')||' - UF ORIGEM: '||NEW.UF_ORIGEM||', UF_DESTINO: '||NEW.UF_DESTINO;
    END

    SELECT T.ID FROM TBUF T WHERE T.UF = NEW.UF_ORIGEM INTO TIPO_HISTORICO;

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBUF', :TIPO_HISTORICO, HISTORICO);
    END
END
^

SET TERM ; ^



SET TERM ^ ;

CREATE OR ALTER trigger ttbtributacao_icms_inter_b01u for tbtributacao_icms_interestadual
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
declare variable TIPO_HISTORICO INTEGER;
BEGIN

    HISTORICO = '';

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

    SELECT T.ID FROM TBUF T WHERE T.UF = NEW.UF_ORIGEM INTO TIPO_HISTORICO;

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = ' TRIBUTACAO: '||' - '||NEW.ID||', UF ORIGEM: '||NEW.UF_ORIGEM||', UF DESTINO: '||NEW.UF_DESTINO||' ALTERADO: '||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBUF', :TIPO_HISTORICO, HISTORICO);
    END
END
^

SET TERM ; ^



SET TERM ^ ;

CREATE OR ALTER trigger ttbtributacao_icms_inter_b01d for tbtributacao_icms_interestadual
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
declare variable TIPO_HISTORICO INTEGER;
BEGIN

    HISTORICO = '';

    IF (OLD.ID > 0 ) THEN
    BEGIN
        HISTORICO = HISTORICO||' TRIBUTACAO ICMS EXCLUIDA: '||FN_LPAD(OLD.ID, 4, '0')||' - UF ORIGEM: '||OLD.UF_ORIGEM||', UF_DESTINO: '||OLD.UF_DESTINO;
    END

    SELECT T.ID FROM TBUF T WHERE T.UF = OLD.UF_ORIGEM INTO TIPO_HISTORICO;

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBUF', :TIPO_HISTORICO, HISTORICO);
    END
END
^

SET TERM ; ^




ALTER TABLE TBUF
    ADD ID INTEGER;

CREATE SEQUENCE GTBUF;

SET TERM ^ ;

create trigger ttbuf_b03i for tbuf
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbuf,1);
end
^

SET TERM ; ^

UPDATE TBUF SET ID = GEN_ID(GTBUF,1);

UPDATE TBMENU SET 
    CONTROLE = 21270,
    GRUPO = 'FIS',
    SUBGRUPO = 'FIS',
    TIPO = '1   ',
    URL = '',
    DESCRICAO = 'CADASTRO DE UNIDADES FEDERATIVAS',
    DATAHORA = '2022-08-02 11:18:31'
WHERE (CONTROLE = 21270);


COMMIT WORK;






