Use Procedure instead of trigger in MySQL

Question

I'm trying to make trigger to update one field in inserted row but even with this answer: https://stackoverflow.com/a/15300941/4018940 i cant make it work.

Every time i get error:

General error: 1442 Can't update table 'people' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Here is my code:

CREATE TRIGGER short_name_trigger
BEFORE INSERT ON `people`
FOR EACH ROW BEGIN
UPDATE `people` SET NEW.short_name = "wohahaha";
END

Is there any chance that stored procedure will make it work? How to write that procedure?

You're trying to update the same table that the trigger is defined on within the trigger itself, that creates a circular reference which is not allowed in `MySQL`. — Sachin, Dec 17 '22 at 19:26

score 3 · Accepted Answer · answered Dec 17 '22 at 19:40

If you want to change the value being inserted in the new row, you don't need to use UPDATE people. Just set NEW.short_name and this will replace the value that's being inserted.

CREATE TRIGGER short_name_trigger
BEFORE INSERT ON `people`
FOR EACH ROW BEGIN
    SET NEW.short_name = "wohahaha";
END

Use Procedure instead of trigger in MySQL

1 Answers1