AFTER UPDATE trigger that modifies the table on which it is defined

Question

On MySQL 8.0 Trigger created:

DROP TRIGGER `Metrosky_spotters_2020_au`;
DELIMITER ;;
CREATE TRIGGER `Metrosky_spotters_2020_au` AFTER UPDATE ON `Metrosky_spotters_2020` FOR EACH ROW
UPDATE Metrosky_spotters_2020 SET NEW.trnmswid = 5000;;
DELIMITER ;

The trigger loads okay. The function to edit a row but in a change to a field in that row- then t=fails the trigger functionality with:

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

The idea here is to add the current users MSWID (or login) (using 5000 as test) to trnmswid whenever a row item is updated.

I've also tried this in BEFORE UPDATE and still fails.

Is SET command incorrect or what am I missing?

Answer 1

You don't need, neither you can have (as the error message suggests), the UPDATE statement against the table on which the trigger fires. You can, however, directly set the value any NEW column in a BEFORE trigger:

CREATE TRIGGER `Metrosky_spotters_2020_au` 
BEFORE UPDATE ON `Metrosky_spotters_2020` FOR EACH ROW
SET NEW.trnmswid = 5000