title: MYSQL Trigger to insert column value in same table
body: <p>I have a mysql Innodb table 'classrooms_subjects' as </p>
  
  <pre><code>id|classroom_id|subject_id
  </code></pre>
  
  <p>classroom_id &amp; subject_id are composite keys. Whenever i insert a row with classroom_id &amp; subject_id, my id field is inserted as 0.</p>
  
  <p>Now i want to create a trigger which will enter id field as last_inserted_id()+1.</p>
  
  <p>Also I need to take care of multiple records inserted at a time. My trigger is like below:</p>
  
  <pre><code>CREATE TRIGGER `increment_id` AFTER INSERT ON `classrooms_subjects`
   FOR EACH ROW BEGIN
  UPDATE classrooms_subjects
  SET classrooms_subjects.id = LAST_INSERT_ID() + 1 WHERE id=0;
  END
  </code></pre>
  
  <p>when i am inserting a record I am getting the error as:
  "Cant update table in trigger because it is already used by statement which invoked this trigger</p>