My MySQL trigger doesn’t work, simple syntax, not complicated

by

A stored function or trigger cannot modify a table that is already
being used (for reading or writing) by the statement that invoked the
function or trigger.

from: Stored program restrictions.

Generally with a trigger that is fired on insert, if you want to change the value that is being inserted you make the trigger of the type BEFORE INSERT and change the values in NEW

also noticed that the following statement isn’t what you want anyway.

update pos_table set PTS=((NEW.won_games*2)+(NEW.tie_games*1));

It updates the entire table while I think you are only trying to update a sepcific row. Anyway, this is a simple calculation so you don’t really need to store this column. You can just as easily calculate the value at display time and make your code a whole lot simpler + avoid the issue with the trigger.

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 Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
  4. MySQL Trigger after update only if row has changed
  5. mysql after insert trigger which updates another table’s column
  6. How to program a MySQL trigger to insert row into another table?
  7. TRIGGERs that cause INSERTs to fail? Possible?
  8. Use a trigger to stop an insert or update
  9. MySQL Fire Trigger for both Insert and Update
  10. MySQL trigger On Insert/Update events
  11. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  12. MySQL error 1436: Thread stack overrun, with simple query
  13. MySQL trigger to update a field to the value of id
  14. How can I set a maximum number of rows in MySQL table?
  15. mysql trigger stored trigger is already used by statement which invoked stored trigger
  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. When I want to install MySQL I faced this issue “root account password” I forgot it
  26. Restoring MySQL database from physical files
  27. MySQL ‘user_id’ in where clause is ambiguous problem
  28. Commit data in a mysql container
  29. How do I count columns of a table
  30. MySQL Trigger cannot update table – getting ERROR 1442

Leave a Comment