Inserting new row using a trigger on same table

Question

I have a table JOB. It has following columns : cycle_id, job_id, status.

There are 7 jobs that are executed for every cycle. So cycle_id and job_id form composite primary key.

status column can have the following values : WAITING_TO_START, RUNNING. COMPLETED.

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 cycle_id 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 :

DELIMITER $$

CREATE TRIGGER start_new_job
AFTER UPDATE ON job
FOR EACH ROW
BEGIN
IF NEW.status = 'COMPLETED' AND OLD.job_id <=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 ;

But when i execute update on JOB table, I get following error :

UPDATE job SET status='COMPLETED' WHERE cycle_id=1 AND job_id=1;

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.

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.

score 1 · Accepted Answer · edited May 23 '17 at 12:21

You won't be able to insert into the same table from a trigger. I would replace your trigger with a Procedure, and then channel all status updates through the procedure:

CREATE PROCEDURE UpdateJobStatus(jobId INT, NewStatus NVARCHAR(50))
BEGIN
  UPDATE job
     SET `Status` = NewStatus 
     WHERE job_id = jobId;

  IF NewStatus = 'COMPLETED' AND jobId <=6 THEN
    INSERT INTO job(cycle_id, job_id, status) 
       SELECT cycle_id, job_id+1, 'WATING_TO_START'
       FROM job 
       WHERE job_id = jobId;
  END IF;
END;

Sql Fiddle here

Although the procedure will require some modification to your code (i.e. you will need to call the procedure rather than update the data directly), the procedure has the benefit of being more obvious - triggers doing things automagically in the background can be non-intuitive.

Inserting new row using a trigger on same table

1 Answers1

Linked