有一个名为“Coach”的MySQL表,具有以下属性。
coach_ID int
mileage_of_last_service_date double
mileage double
isAvailable boolean
所有行的isAvailable初始设置为1。我想设置一个触发器,触发器执行以下操作: 当里程表属性更新且里程数大于100000时,触发器必须将同一表格的该特定行的属性设置如下。
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;
当我运行这个程序时,在第三行出现了语法错误。 有没有修复的方法? 或者其他实现此功能的方式?