mysql - 创建触发器以对同一个表进行条件更新

Question

 delimiter //
 create trigger T1 before update on account
     for each row
        if NEW.balance <= 0 then
          update account set balance=OLD.balance;
       end if;
     //
Query OK, 0 rows affected (0.09 sec)

 delimiter ;
 update account set balance=-1 where id=101;

ERROR 1442 (HY000): Can't update table 'account' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Answer 1

MySQL 使用行级触发器，因此针对正在更改的每一行都会触发触发器。因此没有理由更新目标表，只需分配值:

create trigger T1 before update on account
 for each row
    if NEW.balance <= 0 then
      set new.balance = OLD.balance; -- this is the difference
   end if;

SQLFiddle 示例:http://sqlfiddle.com/#!2/34aebb/1

<小时/>

您的更新无论如何都不会起作用，因为它缺少 where 子句，这意味着您将仅因为更改了一行就更新了整个表。