ALTER TABLE TBRELATORIO_WEB
    ADD USUARIO_ID INTEGER,
    ADD DATAHORA_REGISTRO TIMESTAMP,
    ADD DATAHORA_ALTERACAO TIMESTAMP;


ALTER TABLE TBRELATORIO_WEB ALTER COLUMN DATAHORA_REGISTRO
SET DEFAULT CURRENT_TIMESTAMP;

/************************************************************************************/
/************************************************************************************/

UPDATE TBRELATORIO_WEB A
    SET A.USUARIO_ID = COALESCE((SELECT FIRST 1 X.USUARIO_CODIGO FROM TBHISTORICO X WHERE X.TABELA = 'TBRELATORIO_WEB' AND X.TABELA_ID = A.ID AND X.HISTORICO LIKE '%'||'INSERIDO'||'%'), 0),
        A.DATAHORA_REGISTRO = COALESCE((SELECT FIRST 1 X.DATAHORA FROM TBHISTORICO X WHERE X.TABELA = 'TBRELATORIO_WEB' AND X.TABELA_ID = A.ID AND X.HISTORICO LIKE '%'||'INSERIDO'||'%'), NULL),
        A.DATAHORA_ALTERACAO = CURRENT_TIMESTAMP
    WHERE A.DATAHORA_ALTERACAO IS NULL;
	
/************************************************************************************/
/************************************************************************************/

CREATE INDEX ITBRELATORIO_WEB_02
ON TBRELATORIO_WEB (TIPO);

CREATE INDEX ITBRELATORIO_WEB_03
ON TBRELATORIO_WEB (TEMPLATE_ID);

CREATE INDEX ITBRELATORIO_WEB_04
ON TBRELATORIO_WEB (STATUS);

CREATE INDEX ITBRELATORIO_WEB_05
ON TBRELATORIO_WEB (VERSAO_IMP);

CREATE INDEX ITBRELATORIO_WEB_06
ON TBRELATORIO_WEB (CODIGO);

CREATE INDEX ITBRELATORIO_WEB_07
ON TBRELATORIO_WEB (USUARIO_ID);

/************************************************************************************/
/************************************************************************************/

SET TERM ^ ;

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

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbrelatorio_web_b02i for tbrelatorio_web
active before insert position 9
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('TBRELATORIO_WEB', NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbrelatorio_web_b02u for tbrelatorio_web
active before update position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

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

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

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

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

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

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

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

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

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

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

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

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

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

    IF (OLD.OBSERVACAO IS DISTINCT FROM NEW.OBSERVACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CAMPO HISTORICO DE ALTERACOES ALTERADO!';
    END

    IF (OLD.OBSERVACAO_VISUAL IS DISTINCT FROM NEW.OBSERVACAO_VISUAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' CAMPO OBSERVACOES DO RELATORIO ALTERADO!';
    END

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

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbrelatorio_web_b02d for tbrelatorio_web
active before delete position 9
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('TBRELATORIO_WEB', OLD.ID, HISTORICO);
    END
END
^

SET TERM ; ^

