In MySQL trigger to update parent record in the same table?
Table comments with fields id, parent_id and child_count.
After you add a child to increment the value of child_count parent.
Since the update the same table in the trigger are prohibited, you may receive the following error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
What are the solutions (or workaround) this problem?
To abandon the trigger, in a single transaction to do two requests to add a review and increase of the counter of the parent.
murphy90 answered on April 4th 20 at 13:22
Well, if in the forehead, it seems like this option.
Create a table with one field comments_buffer comments_id and add in the trigger line that writes to this table the row id in which you want to increment the child_count field.
And on the table comments_buffer hang a trigger on update, which will update the comments table (child_count = child_count + 1) .