DROP TRIGGER TBTABELA_PRECO_B01I;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_b01i for tbtabela_preco
active before insert position 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GTBTABELA_PRECO,1);
END
^

SET TERM ; ^

DROP TRIGGER TBTABELA_PRECO_B02I;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_b02i for tbtabela_preco
active before insert position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

SET TERM ; ^

DROP TRIGGER TBTABELA_PRECO_B02U;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_b02u for tbtabela_preco
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(8000);
BEGIN
  
  HISTORICO = '';

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

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

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

    IF(NEW.DATA_INICIAL <> OLD.DATA_INICIAL) THEN
        BEGIN                  
            HISTORICO = HISTORICO||' DT.INICIAL:'||FN_DATE(OLD.DATA_INICIAL, 'DD/MM/YYYY')||'->'||FN_DATE(NEW.DATA_INICIAL, 'DD/MM/YYYY');
        END

    IF(NEW.DATA_FINAL <> OLD.DATA_FINAL) THEN
        BEGIN                  
            HISTORICO = HISTORICO||' DT.FINAL:'||FN_DATE(OLD.DATA_FINAL, 'DD/MM/YYYY')||'->'||FN_DATE(NEW.DATA_FINAL, 'DD/MM/YYYY');
        END

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

SET TERM ; ^

DROP TRIGGER TBTABELA_PRECO_B01U;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_b01u for tbtabela_preco
active before update position 0
AS
DECLARE VARIABLE COUNT_REGISTERS_MODELO INTEGER;
DECLARE VARIABLE COUNT_REGISTERS_PRODUTO INTEGER;
BEGIN

  IF(NEW.ESTABELECIMENTO_ID <> OLD.ESTABELECIMENTO_ID) THEN
  BEGIN
    
    SELECT COUNT(T.ID) FROM TBMODELO_PRECO T WHERE T.TABELA_ID = NEW.ID INTO :COUNT_REGISTERS_MODELO;

    IF(:COUNT_REGISTERS_MODELO > 0) THEN
    BEGIN
        EXCEPTION "..." 'TABELA DE PRECO JA VINCULADA EM MODELOS. ATUALIZACAO DE ESTABELECIMENTO CANCELADA!';

    END ELSE
    BEGIN

        SELECT COUNT(T.ID) FROM TBPRODUTO_PRECO T WHERE T.TABELA_ID = NEW.ID INTO :COUNT_REGISTERS_PRODUTO;
    
        IF(:COUNT_REGISTERS_PRODUTO > 0) THEN
        BEGIN
            EXCEPTION "..." 'TABELA DE PRECO JA VINCULADA EM PRODUTOS. ATUALIZACAO DE ESTABELECIMENTO CANCELADA!';
    
        END

    END

  END
END
^

SET TERM ; ^

DROP TRIGGER TBTABELA_PRECO_B02D;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_b01d for tbtabela_preco
active before delete position 0
AS
DECLARE VARIABLE COUNT_REGISTERS_MODELO INTEGER;
DECLARE VARIABLE COUNT_REGISTERS_PRODUTO INTEGER;

BEGIN
    SELECT COUNT(T.ID) FROM TBMODELO_PRECO T WHERE T.TABELA_ID = OLD.ID INTO :COUNT_REGISTERS_MODELO;

    IF(:COUNT_REGISTERS_MODELO > 0) THEN
    BEGIN
        EXCEPTION "..." 'TABELA DE PRECO JA VINCULADA EM MODELOS. EXCLUSAO CANCELADA!';

    END ELSE
    BEGIN

        SELECT COUNT(T.ID) FROM TBPRODUTO_PRECO T WHERE T.TABELA_ID = OLD.ID INTO :COUNT_REGISTERS_PRODUTO;
    
        IF(:COUNT_REGISTERS_PRODUTO > 0) THEN
        BEGIN
            EXCEPTION "..." 'TABELA DE PRECO JA VINCULADA EM PRODUTOS. EXCLUSAO CANCELADA!';
    
        END

    END
END
^

SET TERM ; ^

DROP TRIGGER TBTABELA_PRECO_B01D;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_b02d for tbtabela_preco
active before delete position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO EXCLUIDO.';
    END

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

SET TERM ; ^SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_b03d for tbtabela_preco
active before delete position 2
AS
BEGIN
    DELETE FROM TBTABELA_PRECO_FAMILIA A WHERE A.TABELA_PRECO_ID = OLD.ID;
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_b03d for tbtabela_preco
active before delete position 2
AS
BEGIN
    DELETE FROM TBTABELA_PRECO_FAMILIA A WHERE A.TABELA_PRECO_ID = OLD.ID;
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ttbtabela_preco_b03d for tbtabela_preco
active before delete position 2
AS
BEGIN
    DELETE FROM TBTABELA_PRECO_FAMILIA A WHERE A.TABELA_PRECO_ID = OLD.ID;
END
^
-------------- TBTABELA_PRECO_FAMILIA 

SET TERM ; ^

DROP TRIGGER TBTABELA_PRECO_FAMILIA_BI;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_familia_b01i for tbtabela_preco_familia
active before insert position 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GTBTABELA_PRECO_FAMILIA,1);
END
^

SET TERM ; ^

DROP TRIGGER TBTABELA_PRECO_FAMILIA_B02I;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_familia_b02i for tbtabela_preco_familia
active before insert position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE FAMILIA VARCHAR(500);
BEGIN

    HISTORICO = '';
    FAMILIA = COALESCE((SELECT FN_LPAD(A.CODIGO, 4, '0')||' - '||A.DESCRICAO FROM TBFAMILIA A WHERE A.CODIGO = NEW.FAMILIA_ID), '');

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

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

SET TERM ; ^

DROP TRIGGER TBTABELA_PRECO_FAMILIA_BD0;

SET TERM ^ ;

CREATE OR ALTER trigger ttbtabela_preco_familia_b01d for tbtabela_preco_familia
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE FAMILIA VARCHAR(500);
BEGIN

    HISTORICO = '';
    FAMILIA = COALESCE((SELECT FN_LPAD(A.CODIGO, 4, '0')||' - '||A.DESCRICAO FROM TBFAMILIA A WHERE A.CODIGO = OLD.FAMILIA_ID), '');

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'FAMILIA: '||FAMILIA||' EXCLUIDA';
    END

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

SET TERM ; ^

