title: Inserting new row using a trigger on same table
body: <p>I have a table <code>JOB</code>. It has following columns : <code>cycle_id, job_id, status</code>.</p>
  
  <p>There are 7 jobs that are executed for every cycle. So <code>cycle_id</code> and <code>job_id</code> form composite primary key.</p>
  
  <p>status column can have the following values : <code>WAITING_TO_START, RUNNING. COMPLETED</code>. </p>
  
  <p>Each job is a cron job. And each job is responsibility of different person, so to sync each person I am using a database table JOB. Each job listen to JOB table and watch it if there's a row with their job_id and status as 'WAITING_TO_START'. So what I want whenever a job status is changed to COMPLETED, the next job row is created with <code>cycle_id</code> as same as updated job, job_id as updated job's id + 1, status as 'WAITING_TO_START'. So I have created a trigger for this as : </p>
  
  <pre><code>DELIMITER $$
  
  CREATE TRIGGER start_new_job
  AFTER UPDATE ON job
  FOR EACH ROW
  BEGIN
  IF NEW.status = 'COMPLETED' AND OLD.job_id &lt;=6 THEN
     INSERT INTO job(cycle_id, job_id, status) VALUES (OLD.cycle_id, OLD.job_id+1, 'WATING_TO_START');
  END IF;
  END$$
  DELIMITER ;
  </code></pre>
  
  <p>But when i execute update on JOB table, I get following error : </p>
  
  <pre><code>UPDATE job SET status='COMPLETED' WHERE cycle_id=1 AND job_id=1;
  </code></pre>
  
  <blockquote>
    <p>ERROR 1442 (HY000): Can't update table 'job' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.</p>
  </blockquote>
  
  <p>So is there a way to achieve the synchronization. I don't want each job to create a row with next job's id. I want it to be handled automatically. Can this trigger be written in different way or should I use some other mechanism.</p>