title: mysql trigger to add or subtract from in-stock quantity, subtract 1 when item hired & add 1 on when item return
body: <p>Hi Im having a problem with my mysql trigger syntax, I want it to update my copy_instock row on NEW transaction_id, when movie is hired to subtract 1 from copy_instock, then update when movie is returned to add 1 back on to copy_instock. OR how can I achieve this in a different way from MySql.
  My table is below and my trigger that is not correct</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `hires` (
  `transaction_id` int(11) NOT NULL,
  `movie_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `hire_cost` double(4,2) NOT NULL DEFAULT '3.00',
  `date_hired` date DEFAULT NULL,
  `days_hire` int(11) NOT NULL DEFAULT '1',
  `date_returned` date DEFAULT NULL,
  `total_cost` double(4,2) DEFAULT NULL,
  `copy_instock` int(3) NOT NULL
  ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
  
  
  
  CREATE TRIGGER update_instock AFTER INSERT ON hires
  FOR EACH ROW
  BEGIN
  if (date_returned != NULL) {
  UPDATE hires SET (NEW.copy_instock = OLD.copy_instock - 1)
  }
  Elseif (date_returned != date) {
  UPDATE hires SET (NEW.copy_instock = OLD.copy_instock + 1)
  END
  </code></pre>