SET TERM ^ ;

CREATE OR ALTER trigger ttbconciliacao_cartao_b03i for tbconciliacao_cartao
active before insert position 3
AS
DECLARE VARIABLE BANDEIRA_ID INTEGER;

BEGIN
  IF(COALESCE(NEW.BANDEIRA_DESCRICAO, '') <> '' AND COALESCE(NEW.BANDEIRA, 0) = 0) THEN
  BEGIN
        
        :BANDEIRA_ID = COALESCE((SELECT FIRST 1 T.ID FROM TBCARTAO_BANDEIRA T WHERE UPPER(T.DESCRICAO) LIKE SUBSTRING(UPPER(NEW.BANDEIRA_DESCRICAO) FROM 1 FOR 4)||'%'), 0);

        NEW.BANDEIRA = :BANDEIRA_ID;

  END
END
^

SET TERM ; ^

