1

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?

1
  • The trick is to let the trigger modify the row that are about to be updated, see mustaccio's answer. Mar 4, 2020 at 17:56

1 Answer 1

2

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
2
  • So the above worked (as a test)- but here's the next step - add the correct syntax substituting the '5000' with the current_user . (that would be whomever is signed into the database) I've tried to get this to work but seem to run into syntax issues.
    – Mike
    Mar 5, 2020 at 20:02
  • That would be a different question
    – mustaccio
    Mar 5, 2020 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.