title: Creating a MYSQL Trigger using PDO / PHP
body: <p>I've been trying to create a trigger within PHP / PDO. I need to use session variables, and only know how to handle them on PHP, hence this was my start point.</p>
  
  <p>My query is as follows:-</p>
  
  <pre><code>$updateTrigger = "DROP TRIGGER IF EXISTS `trigger_repair_update` ;
  CREATE TRIGGER `trigger_repair_update` BEFORE UPDATE ON $tbl_name
  FOR EACH ROW
  BEGIN
  IF (NEW.repaired_by != OLD.repaired_by) THEN
  INSERT INTO data_tracking
  (`table_name` , `data_id`, `field`, `old_value` , `new_value` , `date_modified`, `username`)
  VALUES
  ('$tbl_name' , NEW.id , 'repaired_by' , OLD.repaired_by , NEW.repaired_by , NOW() , '$user' );
  END IF;
  IF (NEW.must_have != OLD.must_have) THEN
  INSERT INTO data_tracking
  (`table_name` , `data_id`, `field`, `old_value` , `new_value` , `date_modified`, `username`)
  VALUES
  ('$tbl_name' , NEW.id , 'must_have' , OLD.must_have , NEW.must_have , NOW() , '$user' );
  END IF;
  IF (NEW.location != OLD.location) THEN
  INSERT INTO data_tracking
  (`table_name` , `data_id`, `field`, `old_value` , `new_value` , `date_modified`, `username`)
  VALUES
  ('$tbl_name' , NEW.id , 'location' , OLD.location , NEW.location , NOW() , '$user' );
  END IF;
  IF (NEW.status != OLD.status) THEN
  INSERT INTO data_tracking
  (`table_name` , `data_id`, `field`, `old_value` , `new_value` , `date_modified`, `username`)
  VALUES
  ('$tbl_name' , NEW.id , 'status' , OLD.status , NEW.status , NOW() , '$user' );
  END IF;
  IF (NEW.price != OLD.price) THEN
  INSERT INTO data_tracking
  (`table_name` , `data_id`, `field`, `old_value` , `new_value` , `date_modified`, `username`)
  VALUES
  ('$tbl_name' , NEW.id , 'price' , OLD.price , NEW.price , NOW() , '$user' );
  END IF;
  END;";
  
  $sth = $dbLink-&gt;prepare($updateTrigger);
  $sth-&gt;execute();
  </code></pre>
  
  <p>If I remove the variables and enter into PHPMYADMIN, everything works fine, so my conclusion is that I have not allocated the variables properly. Should I be binding parameters here?</p>
  
  <p>I've looked in many places for this:</p>
  
  <ul>
  <li><a href="http://code.tutsplus.com/articles/introduction-to-mysql-triggers--net-12226" rel="nofollow noreferrer">http://code.tutsplus.com/articles/introduction-to-mysql-triggers--net-12226</a></li>
  <li><a href="https://stackoverflow.com/questions/22407972/is-is-possible-to-see-what-data-was-changed-by-a-query">Is is possible to see what data was changed by a query?</a></li>
  <li><a href="https://stackoverflow.com/questions/779230/using-mysql-triggers-to-log-all-table-changes-to-a-secondary-table">Using MySQL triggers to log all table changes to a secondary table</a></li>
  <li><a href="https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records">Is there a MySQL option/feature to track history of changes to records?</a></li>
  <li><a href="http://codespatter.com/2008/05/06/how-to-use-triggers-to-track-changes-in-mysql/" rel="nofollow noreferrer">http://codespatter.com/2008/05/06/how-to-use-triggers-to-track-changes-in-mysql/</a></li>
  </ul>
  
  <p>I still seem to be missing something though.</p>
  
  <p>EDIT:-
  Thanks to Michael Berkowski, I have added the error code and get the following message:-</p>
  
  <blockquote>
    <p>Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 'CREATE TRIGGER <code>trigger_repair_update</code> BEFORE UPDATE ON repair FOR EACH ROW BEGI' at line 2' in /Applications/MAMP/htdocs/repair_list.php:58 Stack trace: #0 /Applications/MAMP/htdocs/repair_list.php(58): PDO->prepare('DROP TRIGGER IF...') #1 {main} thrown in /Applications/MAMP/htdocs/repair_list.php on line 58</p>
  </blockquote>
  
  <p>Line 58 is where the $sth = $dbLink->prepare($updateTrigger); sits.</p>