创建触发器，在更新表后自动更新列

I have a table which looks like this:

我有一个看起来像这样的表:

everything is int except AvailabilityRate which is float. I want to create a trigger which when updating the AvailableRooms column, automatically because of the existance of the trigger the column AvailabilityRate update as well.

除了AvailabilityRate之外,一切都是int,它是float。我想创建一个触发器,当更新AvailableRooms列时,由于触发器的存在而自动更新了AvailabilityRate列。

This is what I have done so far.

这就是我到目前为止所做的。

CREATE TRIGGER availability_BEFORE_UPDATE
BEFORE UPDATE ON availability
FOR EACH ROW
BEGIN
  update availability
    set new.AvailabilityRate = new.AvailableRooms/new.SumRooms;
END

But it throws me this error:

但它给我抛出了这个错误:

Executing: UPDATE ergasia.availability SET AvailableRooms=15 WHERE idAvailability=1;

执行:UPDATE ergasia.availability SET AvailableRooms = 15 WHERE idAvailability = 1;

ERROR 1442: 1442: Can't update table availability in stored function/trigger because it is already used by statement which invoked this stored function/trigger. SQL Statement: UPDATE ergasia.availability SET AvailableRooms=15 WHERE idAvailability=1

错误1442:1442:无法更新存储函数/触发器中的表可用性,因为它已被调用此存储函数/触发器的语句使用。 SQL语句:UPDATE ergasia.availability SET AvailableRooms = 15 WHERE idAvailability = 1

2 个解决方案

#1

You can not update the same table where the trigger is getting executed. Since you are using before update all you need to set the new value of AvailabilityRate

您无法更新执行触发器的同一个表。由于您在更新之前使用,所以您需要设置AvailabilityRate的新值

So the trigger becomes

所以触发器就变成了

delimiter //
CREATE TRIGGER availability_BEFORE_UPDATE BEFORE UPDATE ON availability 
FOR EACH ROW 
 BEGIN 
  set new.AvailabilityRate=new.AvailableRooms/new.SumRooms;
 END;//
delimiter;

#2

You should only need this part:

你应该只需要这个部分:

SET NEW.AvailabilityRate := NEW.AvailableRooms/NEW.SumRooms;

SET NEW.AvailabilityRate:= NEW.AvailableRooms / NEW.SumRooms;

You are not updating a row, you are modifying the values a row is being updated with.

您没有更新行,您正在修改正在更新行的值。

#1