mysql - 触发更新Mysql中表的属性

有一个名为“coach”的mysql表，它具有以下属性。

coach_ID int
mileage_of_last_service_date double
mileage double
isAvailable boolean

对于所有行，isavailable最初设置为1。我想设置一个触发器来执行以下操作。
当mileage属性发生更新并且mileage大于100000时，trigger必须为同一个表的特定属性设置如下。

isAvailable = 0
mileage_of_last_service_date = mileage_of_last_service_date + mileage
mileage = 0

这是我写的问题。

create trigger set_availability_coach after update on coach
for each row
when old.mileage > 10000
begin
update coach
set isAvailable = 0, mileage_of_last_service_date = mileage_of_last_service_date + old.mileage, mileage = 0
where coach.coach_ID = old.coach_ID;
end;

运行此命令时，第3行出现语法错误。
有办法解决这个问题吗？
或者用其他方法来实现这个功能？

最佳答案

我已经改了，应该没问题

CREATE TRIGGER set_availability_coach BEFORE UPDATE ON coach
FOR EACH ROW BEGIN
  IF OLD.mileage > 10000 THEN
    SET NEW.isAvailable = 0,
        NEW.mileage_of_last_service_date = OLD.mileage_of_last_service_date + NEW.mileage,
        NEW.mileage = 0;
  END IF;
END

关于mysql - 触发更新Mysql中表的属性，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15566482/