MySql Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

by

You cannot change a table while the INSERT trigger is firing. The INSERT might do some locking which could result in a deadlock. Also, updating the table from a trigger would then cause the same trigger to fire again in an infinite recursive loop. Both of these reasons are why MySQL prevents you from doing this.

However, depending on what you’re trying to achieve, you can access the new values by using NEW.fieldname or even the old values –if doing an UPDATE— with OLD.

If you had a row named full_brand_name and you wanted to use the first two letters as a short name in the field small_name you could use:

CREATE TRIGGER `capital` BEFORE INSERT ON `brandnames`
FOR EACH ROW BEGIN
  SET NEW.short_name = CONCAT(UCASE(LEFT(NEW.full_name,1)) , LCASE(SUBSTRING(NEW.full_name,2)))
END

More Related Contents:

  1. Throw an error preventing a table update in a MySQL trigger
  2. How to abort INSERT operation in MySql trigger?
  3. MySQL Trigger after update only if row has changed
  4. mysql after insert trigger which updates another table’s column
  5. How to program a MySQL trigger to insert row into another table?
  6. TRIGGERs that cause INSERTs to fail? Possible?
  7. Use a trigger to stop an insert or update
  8. MySQL Fire Trigger for both Insert and Update
  9. MySQL trigger On Insert/Update events
  10. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  11. MySQL error 1436: Thread stack overrun, with simple query
  12. MySQL trigger to update a field to the value of id
  13. How can I set a maximum number of rows in MySQL table?
  14. mysql trigger stored trigger is already used by statement which invoked stored trigger
  15. My MySQL trigger doesn’t work, simple syntax, not complicated
  16. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  17. MySQL Trigger: Delete From Table AFTER DELETE
  18. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  19. Can I launch a trigger on select statement in mysql?
  20. Using MySQL triggers to log all table changes to a secondary table
  21. MySQL: How do I use delimiters in triggers?
  22. MySQL listen notify equivalent
  23. MySQL disable all triggers
  24. Calling an url from a trigger in mysql
  25. Change MySQL default character set to UTF-8 in my.cnf?
  26. MySQL offset infinite rows
  27. How to get an age from a D.O.B field in MySQL?
  28. Most efficient way to store IP Address in MySQL [duplicate]
  29. What is the best way to do incremental backups in MySQL?
  30. Optimistic locking in MySQL

Leave a Comment