ALTER TABLE TBCOLETOR_WEB
    ADD USUARIO INTEGER;
	
ALTER TABLE TBCOLETOR_COMPARATIVO
    ADD TIPO_COMPARATIVO INTEGER;

ALTER TABLE TBCOLETOR_COMPARATIVO
    ADD LISTA_MODELOS BLOB SUB_TYPE 1 SEGMENT SIZE 80;

CREATE TABLE TBCOLETOR_COMPARATIVO_MOD (
    COLETOR_COMPARATIVO_ID INTEGER,
    MODELO_ID INTEGER,
    ID INTEGER);

CREATE SEQUENCE GTBCOLETOR_COMPARATIVO_MOD;

SET TERM ^ ;

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

SET TERM ; ^

CREATE INDEX TTBCOLETOR_COMPARATIVO_MOD01
ON TBCOLETOR_COMPARATIVO_MOD (COLETOR_COMPARATIVO_ID);

CREATE INDEX TTBCOLETOR_COMPARATIVO_MOD02
ON TBCOLETOR_COMPARATIVO_MOD (ID);

CREATE INDEX TTBCOLETOR_COMPARATIVO_MOD03
ON TBCOLETOR_COMPARATIVO_MOD (MODELO_ID);

ALTER TABLE TBCOLETOR_COMPARATIVO_MOD
    ADD DELETAR INTEGER;

ALTER TABLE TBCOLETOR_COMPARATIVO
    ADD ESTABELECIMENTO_ID INTEGER,
    ADD LOCALIZACAO_ID INTEGER,
    ADD FAMILIA_ID INTEGER;

ALTER TABLE TBCOLETOR_COMPARATIVO_MOD DROP DELETAR;

SET TERM ^ ;

CREATE trigger ttbcoletor_web_b02i for tbcoletor_web
active before insert position 2
AS
begin
   INSERT INTO TBHISTORICO (
   TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
   'TBCOLETOR_WEB', NEW.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),
   'Registro incluido.');
end
^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ttbcoletor_web_b01u for tbcoletor_web
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(20);
BEGIN

   HISTORICO = '';

   IF (OLD.descricao <> NEW.descricao) THEN
   BEGIN
        HISTORICO = HISTORICO||' DESC:'||OLD.descricao||'->'||NEW.descricao;
   END
 
   IF (HISTORICO <> '') THEN
   BEGIN
       USUARIO = (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION);

       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBCOLETOR_WEB', OLD.id, :USUARIO,
       (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
  end
end
^

SET TERM ; ^


SET TERM ^ ;

CREATE trigger ttbcoletor_web_b01d for tbcoletor_web
active before delete position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(20);
BEGIN

   HISTORICO = 'Registro excluido.';
 
   IF (HISTORICO <> '') THEN
   BEGIN
       USUARIO = (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION);

       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBCOLETOR_WEB', OLD.id, :USUARIO,
       (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
  end
end
^

SET TERM ; ^

UPDATE OR INSERT INTO TBPARAMETRO (TABELA, CODIGO, DESCRICAO, VALOR, OBSERVACAO, GRUPO, DATAHORA, USUARIO_ID)
                           VALUES ('TBUSUARIO', 'PERMITE_AJUSTAR_ESTOQUE', 'PERMITE AJUSTE ESTOQUE NA TELA 15231', '0', '0 - NAO
1 - SIM', 'WEB15231', '2021-12-14 15:26:17', 186)
                         MATCHING (TABELA, CODIGO);

ALTER TABLE TBCOLETOR_COMPARATIVO
    ADD EXECUTADO INTEGER;



SET TERM ^ ;

CREATE OR ALTER trigger ttbcoletor_comparativo_b02i for tbcoletor_comparativo
active before insert position 2
AS
begin
   INSERT INTO TBHISTORICO (
   TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
   'TBCOLETOR_COMPARATIVO', NEW.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),
   'Registro incluido.');
end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbcoletor_comparativo_b02u for tbcoletor_comparativo
active before update position 2
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(20);
BEGIN

   HISTORICO = '';

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

   IF (OLD.executado <> NEW.executado) THEN
   BEGIN
        if(NEW.executado = 1)then
        begin
            HISTORICO = HISTORICO||'AJUSTE EXECUTADO';
        end else
        begin
            HISTORICO = HISTORICO||'AJUSTE EXCLUIDO';
        end
   END
 
   IF (HISTORICO <> '') THEN
   BEGIN
       USUARIO = (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION);

       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBCOLETOR_COMPARATIVO', OLD.id, :USUARIO,
       (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 ttbcoletor_comparativo_b01d for tbcoletor_comparativo
active before delete position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE USUARIO VARCHAR(20);
BEGIN

   HISTORICO = 'Registro excluido.';
 
   IF (HISTORICO <> '') THEN
   BEGIN
       USUARIO = (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION);

       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBCOLETOR_COMPARATIVO', OLD.id, :USUARIO,
       (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       :HISTORICO);
    end
end
^

SET TERM ; ^


