title: Creating a trigger : ERROR 1064 (42000): You have an error in your SQL syntax;
body: <p>It seems to me like everything is ok with the syntax yet I get the error.</p>
  
  <p>Please note: I am creating the trigger, by copy pasting this code in the <code>mysql</code> console. Not sure if this matters.</p>
  
  <pre><code>DELIMITER $$
  CREATE TRIGGER after_nse_fo_insert AFTER INSERT
  ON NSE_FO
  FOR EACH ROW
  BEGIN
  DECLARE settle_orders_data VARCHAR(1000) DEFAULT '';
  DECLARE delimiter_pos INT DEFAULT 0;
  DECLARE start_pos INT DEFAULT 0;
  DECLARE fill_id VARCHAR(50) DEFAULT '';
  DECLARE if_settled VARCHAR(50) DEFAULT '';
  DECLARE units_settled INT DEFAULT 0;
  DECLARE temp_diff INT DEFAULT 0;
  
  CALL settle_trade_orders(NEW.FillId, NEW.SymbolName, NEW.TransactionType, NEW.AccountName, NEW.TradeDateTime, CAST(NEW.FillSize AS UNSIGNED), NEW.FillPrice, settle_orders_data);
  
  settling_loop: LOOP
  
  SET start_pos = delimiter_pos + 1;
  SET delimiter_pos = LOCATE(',', settle_orders_data, start_pos);
  
  IF delimiter_pos = 0 THEN
      LEAVE settling_loop;
  END IF;
  
  SET temp_diff = delimiter_pos - start_pos;
  SET fill_id = SUBSTRING(settle_orders_data, start_pos, temp_diff);
  
  SET start_pos = delimiter_pos + 1;
  SET delimiter_pos = LOCATE(',', settle_orders_data, start_pos);
  SET temp_diff = delimiter_pos - start_pos;
  SET if_settled = SUBSTRING(settle_orders_data, start_pos, temp_diff);
  
  SET start_pos = delimiter_pos + 1;
  SET delimiter_pos = LOCATE(',', settle_orders_data, start_pos);
  SET temp_diff = delimiter_pos - start_pos;
  SET settled_count = SUBSTRING(settle_orders_data, start_pos, temp_diff);
  
  -- settle in NSE_FO table
  -- UPDATE NSE_FO
  -- SET Settled=if_settled, settled_units=settled_count
  -- WHERE FillId=fill_id;
  END LOOP settling_loop;
  
  END $$
  DELIMITER ;
  </code></pre>
  
  <p>Below is the error that I see </p>
  
  <blockquote>
    <p>ERROR 1064 (42000): You have an error in your SQL syntax; check the
    manual that corresponds to your MySQL server version for the right
    syntax to use near 'IF;</p>
    
    <p>SET temp_diff = delimiter_pos - start_pos; SET fill_id =
    SUBSTRING(settle_o' at line 19</p>
  </blockquote>
  
  <p>I am not sure what are the syntax issues around those lines?</p>
  
  <p><strong>Update</strong>: Removed <code>IF</code> statement entirely. Another error throws.</p>
  
  <blockquote>
    <p>ERROR 1064 (42000): You have an error in your SQL syntax; check the
    manual that corresponds to your MySQL server version for the right
    syntax to use near '(settle_orders_data, start_pos, temp_diff); SET
    start_pos = delimiter_pos + 1; ' at line 17. Removed IF entirely.</p>
  </blockquote>