mySQL8.0触发语句

Question

我正在尝试创建如下所示:

Create a trigger on the Company table after an update that sets a company's budget to 0 if it is less than 0.

但是我遇到了诸如无法更新、设置表格之类的错误。我知道这是因为我需要从“更新后”更改为“更新前”。但我不能忽略预期的代码。我能做什么？

我的 mySQL 代码是:

 delimiter $$

 drop procedure if exists after_company_update $$ 

 create trigger after_company_update after update on Company 

 for each row begin 

 update Company set budget = 0 where budget < 0; 

 end $$ 

 delimiter ;

Error: ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG: Can't update table 'Company' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Answer 1

从逻辑上讲，这样的触发器应该在更新之前完成，而不是之后完成:

delimiter $$

drop trigger if exists before_company_update $$ 

create trigger before_company_update after update on Company 
for each row
begin 
    if old.budget < 0 then
        set new.budget := 0
    end if;
end $$ 

delimiter ;

您可以将其表示为之后更新触发器:

create trigger after_company_update after update on Company 
for each row
    update Company
        set budget = 0
        where Company.Id = new.Id;

语法有效，但触发器无效。它返回错误:

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

你可以看到这个here .