SET TERM ^ ;

CREATE OR ALTER trigger ttbremessa_consumo_vinculo_bd9 for tbremessa_consumo_vinculo
active before delete position 9
as
--*****************************
--*                           *
--*       REGISTRA LOG        *
--*       DE  EXCLUS??O        *
--*                           *
--*****************************
declare variable historico varchar(500);
declare variable qtd_vinculos integer;
declare variable origem integer;
BEGIN

    :origem = (select first 1 r.origem from tbremessa r where r.numero = old.remessa_id) ;

    if(:origem <> '22595') then
        begin
    
       --*****************************
       -- corigir problema de ter mais de um viculo e ele ser excluido e o registro ficar sem imprimir etiqueta
       select
            count(1) as VINCULOS
       FROM TBREMESSA_CONSUMO_VINCULO V
         WHERE V.REMESSA_ID = OLD.REMESSA_ID
           AND V.CONSUMO_ID = OLD.CONSUMO_ID
           AND V.ID        <> OLD.ID
       into qtd_vinculos;
    
       qtd_vinculos = coalesce(qtd_vinculos,0);
    
       if(qtd_vinculos = 1)then
       begin
          UPDATE TBREMESSA_CONSUMO_VINCULO V
           SET V.ETIQUETA = '1'
         WHERE V.REMESSA_ID = OLD.REMESSA_ID
           AND V.CONSUMO_ID = OLD.CONSUMO_ID
           AND V.ID        <> OLD.ID
           AND V.ETIQUETA = '0';
       end
       --*****************************
    
       HISTORICO = 'VINCULO EXCLUIDO. CONS.: ' || OLD.CONSUMO_ID || ' - TAL.: ' || OLD.REMESSA_TALAO_ID || ' - TAL. DET.: ' || OLD.REMESSA_TALAO_DETALHE_ID || ' - QTD.: ' || OLD.QUANTIDADE;
    
       IF (HISTORICO <> '') THEN
       BEGIN
           INSERT INTO TBHISTORICO
                  (
                  TABELA,
                  TABELA_ID,
                  USUARIO, IP,
                  HISTORICO
                  )
           VALUES(
                  'TBREMESSA',
                  OLD.REMESSA_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
END
^

SET TERM ; ^

