CREATE TABLE TBUPDATE_ECOM_LACLOFIT (
    ID INTEGER NOT NULL,
    TABELA_ID INTEGER,
    TABELA VARCHAR(50),
    TAMANHO INTEGER,
    SUB_ID INTEGER,
    TIPO INTEGER);

ALTER TABLE TBUPDATE_ECOM_LACLOFIT
ADD CONSTRAINT PK_TBUPDATE_ECOM_LACLOFIT
PRIMARY KEY (ID);

CREATE SEQUENCE GTBUPDATE_ECOM_LACLOFIT;

create trigger ttbupdate_ecom_laclofit_b01i for tbupdate_ecom_laclofit
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbupdate_ecom_laclofit,1);
end;

CREATE OR ALTER trigger ttbpedido_b10u for tbpedido
active after update position 10
AS
begin

  if(   (new.ecommerce = 1 and new.status_ecommerce <> old.status_ecommerce)
     or (new.codigo_rastreio <> old.codigo_rastreio)
     or (new.cte <> old.cte)  )then
  begin

      UPDATE OR INSERT INTO TBUPDATE_ECOMMERCE (TABELA, TABELA_ID, SUB_ID)
            VALUES ('TBPEDIDO', new.pedido, 0)
          MATCHING (TABELA, TABELA_ID, SUB_ID);

  end

  if(   (new.ecommerce = 2 and new.status_ecommerce <> old.status_ecommerce)
     or (new.codigo_rastreio <> old.codigo_rastreio)
     or (new.cte <> old.cte)  )then
  begin

    if(new.codigo_rastreio <> old.codigo_rastreio or new.cte <> old.cte)then
    begin
      UPDATE OR INSERT INTO TBUPDATE_ECOM_LACLOFIT (TABELA, TABELA_ID, SUB_ID, TIPO)
            VALUES ('TBPEDIDO', new.pedido, 0, 1 )
          MATCHING (TABELA, TABELA_ID, SUB_ID, TIPO);
    end

    if(new.status_ecommerce <> old.status_ecommerce)then
    begin
      UPDATE OR INSERT INTO TBUPDATE_ECOM_LACLOFIT (TABELA, TABELA_ID, SUB_ID, TIPO)
            VALUES ('TBPEDIDO', new.pedido, 0, 2 )
          MATCHING (TABELA, TABELA_ID, SUB_ID, TIPO);
    end

  end



end;

