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?
Best Answer
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 anyNEW
column in aBEFORE
trigger: