使用触发器在同一张表上插入新行 [英] Inserting new row using a trigger on same table

问题描述

我有一张桌子 JOB .它包含以下列: cycle_id，job_id，状态.

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

每个周期执行7个作业.因此， cycle_id 和 job_id 形成了复合主键.

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

状态列可以具有以下值: WAITING_TO_START，正在运行.已完成.

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

每个工作都是Cron工作.而且每个工作都是不同人的责任，因此要同步我正在使用数据库表JOB的每个人.每个作业都会侦听JOB表，并查看是否有一行作业名称和状态为"WAITING_TO_START"的表.因此，每当作业状态更改为COMPLETED时，我想要的就是用 cycle_id 与更新的作业相同，Job_id作为更新的作业ID + 1，状态为"WAITING_TO_START"创建下一个作业行.因此，我为此创建了一个触发器:

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 ;

但是当我在JOB表上执行更新时，出现以下错误:

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;

错误1442(HY000):无法更新存储函数/触发器中的表'job'，因为调用该存储函数/触发器的语句已使用该表.

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.

有没有一种方法可以实现同步.我不希望每个作业都使用下一个作业的ID创建一行.我希望它可以自动处理.可以以其他方式编写此触发器，还是应该使用其他机制?

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.

推荐答案

您将无法从触发器.我将用过程替换触发器，然后通过该过程引导所有状态更新:

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

尽管该过程将需要对您的代码进行一些修改(即，您将需要调用该过程而不是直接更新数据)，但该过程具有更明显的好处-在后台自动执行操作的触发器可以是非-直观.

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.

这篇关于使用触发器在同一张表上插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！