SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_feed_b02i for tbcaso_feed
active before insert position 0
AS
  declare variable corpo1 varchar(8000);
  declare variable corpo2 varchar(8000);
  declare variable corpo3 varchar(8000);
  declare variable id integer;
  declare variable ARQUIVO_ID integer;
begin

  if(new.origem = 0)then
  begin

    if(new.de <> '' and new.para <> '' and new.assunto <> '' and new.mensagem <> '' and (new.tipo = 1 /*or new.tipo = 4*/) and new.usuario_id > 0 and new.subfeed = 0)then
    begin

        for
           select * from SPC_EMAIL_CASO(new.caso_id, new.painel_id, new.mensagem)
        into
          :corpo1,
          :corpo2,
          :corpo3
        do
        begin

        end
    
        id = Gen_ID(gtbemail,1);

        insert into tbemail (id, status, email, assunto, corpo, usuario_id, cc, cco, painel_id, datahora, de, caso_feed_id)
           values (
            :id,
            0,
            new.para,
            new.assunto,
            :corpo1 || :corpo2 || :corpo3,
            new.usuario_id,
            new.em_copia,
            new.em_copia_oculta,
            new.painel_id,
            current_timestamp,
            (SELECT FIRST 1 CP.EMAIL FROM TBCASO_PAINEL CP WHERE CP.ID = NEW.PAINEL_ID),
            new.id
         );

         if(char_length(new.lista_file) > 0)then
         begin
           for
             select OSPLIT from split_blob(new.lista_file,',')
           into
             :ARQUIVO_ID
           do begin
              INSERT INTO TBEMAIL_ANEXO (EMAIL_ID, ARQUIVO_ID, DESCRICAO) VALUES (:id, :ARQUIVO_ID, '');
           end
         end

    end
  end

end
^

SET TERM ; ^

