title: function inside trigger returns null in mysql
body: <p>HI Friends I'm facing the below issue since a week and not able to find a solution your help would really appreciated..</p>
  <p>I have a trigger which is getting invoked on insertion on a table called transaction. in the trigger a function is called with a parameter (this parameter is a new value (XXX) from the new record which is inserted)..</p>
  <p>Here is the function</p>
  <pre><code>DELIMITER //
  CREATE DEFINER=`root`@`localhost` FUNCTION `getaccountno`(id int) RETURNS varchar(30)
      DETERMINISTIC
  BEGIN
  
  RETURN (Select tp.account_number  from transaction t join trx_pay tp on t.id = tp.transaction_id where tp.transaction_id= id);
  
  END
  // 
  </code></pre>
  <p>if I run this function as below, it returns a result. but when this is used inside a trigger the function renders null result. I don't understand the logic behind this. struggling a lot on this. your help would really save my time.</p>
  <pre><code>  select getaccountno(45); 
  </code></pre>
  <p><strong>NEW.id</strong> is the new inserted value in transaction table which is passed inside the function</p>
  <p>the trigger is like below</p>
  <pre><code> DELIMITER //
          CREATE TRIGGER transet
      AFTER INSERT ON transaction
       
      FOR EACH ROW
      BEGIN
      declare reference  varchar(10);
      declare account_number varchar(15);
      declare amt decimal(19,2);
      
      set @reference = (select getreference());
      set @account_number = (select getaccountno(NEW.id));
      set @amt = new.transaction_amount;
      
      IF NEW.status='PENDING' THEN
                                     
                      insert into store_ref ( ref, actno, amt)
                      values(@reference , @account_number, @amt);
      
      END IF;
      END;
      //
  </code></pre>