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

Question

I don't know why my trigger isn't working, the query works when I use it manually, but when I want to be updated by a trigger it doesn't work. Can someone help me to know why?

Here's my trigger:

    CREATE TRIGGER `upd_PTS` AFTER UPDATE ON `pos_table`
 FOR EACH ROW BEGIN
    IF (NEW.played_games <> OLD.played_games)
    THEN  
        update pos_table set PTS=((NEW.won_games*2)+(NEW.tie_games*1));
    END IF;
END

Simple, it's for a sport tournament, when the statistics person captures the score of the game, automatically he adds a played_game and it's respective won, tie or lost game, so my trigger should execute automatically when he played_games change and it's has to change the PTS column. But it isn't working.

Also when I edit the played_games column manually I get this error: "Can't update table pos_table in stored function/trigger because it's already used by statement which invoked this stored function/trigger".

Answer 1

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.