MySql trigger to update fields on the own table

MySql’s trigger confusion…

Often we need to update a field in the table which is just updated, the immediate impression is we have to use a ‘after update’ trigger:
#1

Create Trigger `update_flag` After Update on `sales` for each row BEGIN
  set new.flag = new.flag2;
END;

this will not compile as ‘new’ can not be updated in the ‘After Update’ trigger. so we ended up:
#2

Create Trigger `update_flag` After Update on `sales` for each row BEGIN
  update table sales set flag = flag2 where id = new.id;
END;

but you will get a complain:
“can not update table performer in trigger because it is already used by statement which invoked this trigger”

actually situation like this requires a ‘before update’ trigger:
#3

Create Trigger `update_flag` Before Update on `sales` for each row BEGIN
	set new.flag = new.flag2;
END;

Conclusion: to update the same table where trigger issues, use ‘before update’ so that you ‘new’ to use, if to update fields in other table then you can use #2