title: mysql trigger with inner join
body: <p>I have 3 tables in mysql database that is linked to my issue tracker.
  tables are:</p>
  
  <pre><code>1.Issues (contains ID, Issue_name &amp; assignee)
  2.change_group (contains ID, Issue_id, created_time)
  3.change_item  (contains id, group_id,field, old string , new string)
  </code></pre>
  
  <p>when any change occures about my isuues like changing status or assignee of issue,
  table change_group will have new record. one goupid is matched with one or more records in change_item table. for example on group_id can save change in status and assignee of an issue:
  now 
  according to changing assignees of issue I added a column with assignee name into issue table and I want to have a trigger that save snapshot of assigee field when a change happens.and I tried this:</p>
  
  <pre><code>create TRIGGER save_Assignee AFTER INSERT ON changeitem
  FOR EACH ROW 
  BEGIN 
  SET new.assignee=(select assignee from jiraissue INNER JOIN changegroup ON jiraissue.ID=changegroup.issueid)
  END
  </code></pre>
  
  <p>It seems that I cant Have inner join with trigger at same time.</p>
  
  <p>If anyone has any thoughts on this it would be much appreciated. Thanks</p>
  
  <p>MINA</p>