title: Can't update table 'transaction' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
body: <p>I have problem with my trigger.</p>
  
  <pre><code>DELIMITER $$
  CREATE TRIGGER complete_transation
  AFTER INSERT ON transaction
  FOR EACH ROW BEGIN
  
  DECLARE id_trans INT;
  DECLARE id_stock INT;
  DECLARE type_transaction BIT(1);
  DECLARE id_match INT;
  
  SET @id_trans = NEW.idTransaction;
  SET @id_stock = NEW.stock_idStocks;
  SET @type_transaction = NEW.type;
  
  IF(@type_transaction = 1) THEN
      SET @id_match = (SELECT idTransaction FROM transaction WHERE stock_idStocks = @id_stock AND type = 0);
      UPDATE transaction SET status = 1 WHERE idTransaction = @id_match;
  END IF;
  IF(@type_transaction = 0) THEN
      SET @id_match = (SELECT idTransaction FROM transaction WHERE stock_idStocks = @id_stock AND type = 1);
      UPDATE transaction SET status = 1 WHERE idTransaction = @id_match;
  END IF;
  
  
  
  END$$
  DELIMITER ;
  </code></pre>
  
  <p>Is there any chance to change the other record in table where trigger was invoked?</p>