mySQL8.0 trigger statements

Question

I am trying to create as following given:

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

But I struggle with errors such as being unable to update, set the table. I know it is because of I need to change from 'after update' to 'before update'. But I cannot dismiss the expected code. What could I do?

The mySQL code I have is:

 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

Such a trigger should logically be done before the update rather than afterwards:

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 ;

You can express this as an after update trigger:

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

The syntax works, but the trigger doesn't. It returns the error:

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

You can see this here.