ALTER TABLE TBWORKFLOW_CAMPO_SQL
    ADD DESC_TABELA BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8;

COMMENT ON COLUMN TBWORKFLOW_CAMPO_SQL.DESC_TABELA IS
'Descricao dos campos de retorno do SQL';


ALTER TABLE TBWORKFLOW_ITEM_CAMPO
    ADD VALOR_JSON BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8 DEFAULT '{}',
    ADD VALOR_DADOS_JSON BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8 DEFAULT '[]';

COMMENT ON COLUMN TBWORKFLOW_ITEM_CAMPO.VALOR_JSON IS
'Quando campo de consulta unica, salvar json de valores nesse campo';

COMMENT ON COLUMN TBWORKFLOW_ITEM_CAMPO.VALOR_DADOS_JSON IS
'Quando campo de consulta multipla, salvar array de valores nesse campo';

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

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_campo_b1u for tbworkflow_item_campo
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

  HISTORICO = '';

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

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

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

  IF (OLD.VALOR_JSON <> NEW.VALOR_JSON) THEN
  BEGIN
     HISTORICO = HISTORICO||' VLR DA CONSULTA ALTERADO!';
  END

  IF (OLD.VALOR_DADOS_JSON <> NEW.VALOR_DADOS_JSON) THEN
  BEGIN
     HISTORICO = HISTORICO||' VLR DE DADOS SELECIONADOS ALTERADO!';
  END

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_ITEM_CAMPO', OLD.ID,
    (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
    (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
     FN_ELIPSE(:HISTORICO, 500));
  END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ttbworkflow_item_campo_b02u for tbworkflow_item_campo
active before update position 2
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

    IF (OLD.VALOR_JSON <> NEW.VALOR_JSON) THEN
    BEGIN
        HISTORICO = HISTORICO||' '||FN_ELIPSE2(OLD.VALOR_JSON, 200)||'->'||FN_ELIPSE2(NEW.VALOR_JSON, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'VLR DA CONSULTA ALTERADO:'||HISTORICO;

        INSERT INTO TBHISTORICO (
         TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
         'TBWORKFLOW_ITEM_CAMPO', OLD.ID,
        (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
        (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
         FN_ELIPSE(:HISTORICO, 500));
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ttbworkflow_item_campo_b03u for tbworkflow_item_campo
active before update position 3
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

    IF (OLD.VALOR_DADOS_JSON <> NEW.VALOR_DADOS_JSON) THEN
    BEGIN
        HISTORICO = HISTORICO||' '||FN_ELIPSE2(OLD.VALOR_DADOS_JSON, 200)||'->'||FN_ELIPSE2(NEW.VALOR_DADOS_JSON, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'VLR DA CONSULTA ALTERADO:'||HISTORICO;

        INSERT INTO TBHISTORICO (
         TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
         'TBWORKFLOW_ITEM_CAMPO', OLD.ID,
        (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
        (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
         FN_ELIPSE(:HISTORICO, 500));
    END
END
^

SET TERM ; ^

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

SET TERM ^ ;

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

    HISTORICO = '';
    
    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' CONFIGURACOES DE CONSULTA DO CAMPO '||NEW.WORKFLOW_CAMPO_ID||' INCLUIDAS';
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
         INSERT INTO TBHISTORICO (
         TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
         'TBWORKFLOW_TAREFA', NEW.WORKFLOW_TAREFA_ID,
        (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
        (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
         :HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

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

    HISTORICO = '';
    
    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' CONFIGURACOES DE CONSULTA DO CAMPO '||OLD.WORKFLOW_CAMPO_ID||' EXCLUIDAS';
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
         INSERT INTO TBHISTORICO (
         TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
         'TBWORKFLOW_TAREFA', OLD.WORKFLOW_TAREFA_ID,
        (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
        (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
         :HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_campo_sql_b01u for tbworkflow_campo_sql
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';
    
    IF (OLD.SQL <> NEW.SQL) THEN
    BEGIN
        HISTORICO = HISTORICO||' SQL ALTERADO!';
    END

    IF (OLD.CAMPOS <> NEW.CAMPOS) THEN
    BEGIN
        HISTORICO = HISTORICO||' CAMPOS ALTERADOS!';
    END

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

    IF (OLD.CAMPOS_TABELA <> NEW.CAMPOS_TABELA) THEN
    BEGIN
        HISTORICO = HISTORICO||' CAMPOS DA CONSULTA ALTERADOS!';
    END

    IF (OLD.CAMPO_RETORNO <> NEW.CAMPO_RETORNO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CAMPO DE RETORNO:'||OLD.CAMPO_RETORNO||'->'||NEW.CAMPO_RETORNO;
    END

    IF (OLD.CAMPOS_SUBTABELA_INC <> NEW.CAMPOS_SUBTABELA_INC) THEN
    BEGIN
        HISTORICO = HISTORICO||' CAMPOS DA TABELA DE SELECAO ALTERADOS!';
    END

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

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

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

    IF (OLD.DESC_TABELA <> NEW.DESC_TABELA) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC. DOS CAMPOS DA CONSULTA ALTERADA!';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'CONFIGURACOES DE CONSULTA DO CAMPO '||OLD.WORKFLOW_CAMPO_ID||' ALTERADAS:'||HISTORICO;

         INSERT INTO TBHISTORICO (
         TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
         'TBWORKFLOW_TAREFA', NEW.WORKFLOW_TAREFA_ID,
        (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
        (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
         :HISTORICO);
    END
END
^

SET TERM ; ^

