在MySQL触发器中更新同一行

Question

我有一张名为SCHEDULES_CLASS的表，其中有两个字段：CLASS_ID（从CLASS表中获取外键）、STATUS和NUMBER_OF_ENROLLED_STUDENTS。我还有另一张表CLASS，其中只有一个字段MAX_SEATS。

当某个计划班级的NUMBER_OF_ENROLLED_STUDENTS等于相应班级的可用MAX_SEATS时，我需要将该SCHEDULES_CLASS的状态从最初的“OPEN”更改为“FULL”。

为此，我创建了以下触发器：

USE mydb;
DELIMITER ##
dROP TRIGGER IF EXISTS updateClassStatusTrigger ##
CREATE TRIGGER updateClassStatusTrigger
BEFORE UPDATE ON SCHEDULED_CLASS
  FOR EACH ROW
  BEGIN
        UPDATE SCHEDULED_CLASS SET STATUS="FULL" WHERE CLASS_ID=NEW.CLASS_ID
        AND EXISTS (SELECT 1 FROM SCHEDULED_CLASS sc, CLASS cl WHERE cl.CLASS_ID=NEW.CLASS_ID AND NEW.NUMBER_OF_ENROLLED_STUDENTS=cl.MAX_SEATS);
END##

我正在使用phpmyadmin，当对SCHEDULED_CLASS表进行更新时，出现以下错误：

#1442 - Can't update table 'SCHEDULED_CLASS' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 

有什么想法可以完成这个任务吗？即，当已报名学生人数达到最大可用座位时，将预定课程的状态更新为“已满”。谢谢。

- mtk

---

你为什么不在业务层面做这个，而是使用触发器？ - CodeZombie

我正在使用PHP + MySQL进行此操作，并调用MySQL查询。我认为，由于这是在固定条件触发器上的一次性更新，触发器会是一个不错的选择。谢谢。 - mtk

Answer 1

更新同一行的方法是简单地使用SET NEW.column_name = some_Value。为了简化您的触发器，可以考虑以下事项：

CREATE TRIGGER updateClassStatusTrigger
BEFORE UPDATE ON SCHEDULED_CLASS
FOR EACH ROW
  SET NEW.STATUS='FULL'

现在，你只需要根据条件更新给定的值。由于我们不再使用标准的UPDATE语句，所以这不能在同一查询中完成。因此，需要进行拆分：

USE mydb;
DELIMITER ##
dROP TRIGGER IF EXISTS updateClassStatusTrigger ##
CREATE TRIGGER updateClassStatusTrigger
BEFORE UPDATE ON SCHEDULED_CLASS
  FOR EACH ROW
  BEGIN
        SELECT EXISTS (SELECT 1 FROM SCHEDULED_CLASS sc, CLASS cl WHERE cl.CLASS_ID=NEW.CLASS_ID AND NEW.NUMBER_OF_ENROLLED_STUDENTS=cl.MAX_SEATS) INTO @row_exists;
        IF @row_exists THEN
          SET NEW.STATUS='FULL';
        END IF;
  END##

我希望上面没有语法错误。我已经在我的表格上测试过它，但随后进行了编辑以适应您的模式。

您自己的尝试失败了，因为在触发器内部，MySQL不允许您修改触发器执行的表格。它无法分析您的查询以确定您实际上尝试的是否可以以有效的方式完成 - 它甚至都没有尝试。它只是禁止修改同一张表。

- Shlomi Noach

---

非常有帮助，非常感谢！ - mtk