/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE TBREP (
    CODIGO              VARCHAR(20),
    DESCRICAO           VARCHAR(50),
    CONSULTA            CHAR(1),
    SEQ                 BIGINT DEFAULT 0,
    ID                  INTEGER,
    STATUS              INTEGER,
    INDICADOR_MARCACAO  VARCHAR(1) DEFAULT 0,
    TIPO                INTEGER,
    USUARIO             VARCHAR(50),
    SENHA               VARCHAR(50),
    SERVIDOR            VARCHAR(100),
    ACCESS_TOKEN        VARCHAR(300),
    REFRESH_TOKEN       VARCHAR(300),
    PORTARIA_671        SMALLINT DEFAULT 0,
    FLAG                INTEGER DEFAULT 0
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBREP ADD PRIMARY KEY (CODIGO);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBREP_B01D */
CREATE OR ALTER TRIGGER TTBREP_B01D FOR TBREP
ACTIVE BEFORE DELETE POSITION 0
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('TBREP',old.id, HISTORICO);
    END
end
^


/* Trigger: TTBREP_B01I */
CREATE OR ALTER TRIGGER TTBREP_B01I FOR TBREP
ACTIVE BEFORE INSERT POSITION 0
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('TBREP',NEW.id, HISTORICO);
    END
end
^


/* Trigger: TTBREP_B01U */
CREATE OR ALTER TRIGGER TTBREP_B01U FOR TBREP
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF ( OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||', STATUS: '|| OLD.STATUS||'->'||NEW.STATUS;
    END
    
    IF ( OLD.SEQ <> NEW.SEQ AND new.FLAG = 1) THEN
    BEGIN
        HISTORICO = HISTORICO||', SEQ.: '|| OLD.SEQ||'->'||NEW.SEQ;
    END 
    new.FLAG = 0;
    
    IF ( OLD.servidor <> NEW.servidor) THEN
    BEGIN
        HISTORICO = HISTORICO||', Serv.: '|| OLD.servidor||'->'||NEW.servidor;
    END 
    
    IF ( OLD.usuario <> NEW.usuario) THEN
    BEGIN
        HISTORICO = HISTORICO||', Usuario: '|| OLD.usuario||'->'||NEW.usuario;
    END 
    
    IF ( OLD.senha <> NEW.senha) THEN
    BEGIN
        HISTORICO = HISTORICO||', Senha Alterada';
    END 
    
    IF ( COALESCE(OLD.PORTARIA_671,0) <> COALESCE(NEW.PORTARIA_671,0)) THEN
    BEGIN
        HISTORICO = HISTORICO||', PORT.671 '|| COALESCE(OLD.PORTARIA_671,0)||'->'||COALESCE(NEW.PORTARIA_671,0);
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBREP',NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^



/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBREP.PORTARIA_671 IS 
'INDICA SE OS REGISTROS DE PONTO IMPORTADOS DEVEM SEGUIR A NORMA DA PORTARIA 671';



/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/