I'm in the process of trying to optimise my queries, part of which is storing a value like "number of comments" in its own column instead of having to use COUNT() each time. To do this, I plan to create a couple of triggers in the "comments" table. On insert/delete it will update the posts.numComments column by +/- 1 respectively.
However the trigger is where my problems are at, I'm getting this error:
General error: 1442 Can't update table 'posts' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Which I understand means you can't update the table that called the trigger, however as far as I can tell, no part of the trigger would attempt to alter the table it was being called from; the "comments" table. It should only be updating the posts table
The trigger causing this error:
CREATE TRIGGER `increase cache`
AFTER INSERT ON `comments`
FOR EACH ROW
UPDATE `posts`
SET `numComments` = (numComments + 1)
WHERE postID = NEW.postID
What's really stumping me is I'm also using this approach for numLikes too, and have an almost identical trigger that runs without any errors:
CREATE TRIGGER `increase cached likes`
AFTER INSERT ON `likes`
FOR EACH ROW
UPDATE `posts`
SET `numlikes` = (numlikes + 1)
WHERE postID = NEW.postID
There are no triggers on the post table, and only one trigger on the comments table that runs on DELETE so I really have no idea what could be causing this problem. Any help would be much appreciated!
If it helps, I'm using MySQL Cluster (I know about triggers not auto copying between nodes that's not my problem)
I've tried to find a solution to this online but I can't find anyone with this problem.
What should be happening is when a comment gets added to the comments table, the numComments column for the post commented on should increase by one, but I keep getting the error above.
This might have been caused by the INSERT INTO comments query selecting from posts. I fixed this by moving numLikes and numComments to their own interactions table