title: Recursive MySQL trigger which calls the same table and the same trigger
body: <p>I'm writing a simple forum for a php site. I'm trying to calculate the post counts for each category. Now a category can belong to another category with root categories being defined as having a NULL parent_category_id. With this architecture a category can have an unlimited number of sub-categories and keeps the table structure fairly simple.</p>
  
  <p>To keep things simple lets say the categories table has 3 fields: <code>category_id</code>, <code>parent_category_id</code>, <code>post_count</code>. I don't think the remaining database structure is relevant so I'll leave it out for now.</p>
  
  <p>Another trigger is calling the categories table causing this trigger to run. What I want is it to update the post count and then recursively go through each parent category increasing that post count.</p>
  
  <pre><code>DELIMITER $$
  
  CREATE TRIGGER trg_update_category_category_post_count BEFORE UPDATE ON categories FOR EACH ROW
  BEGIN
  IF OLD.post_count != NEW.post_count THEN
    IF OLD.post_count &lt; NEW.post_count THEN
      UPDATE categories SET post_count = post_count + 1 WHERE categories.category_id = NEW.parent_category_id;
    ELSEIF OLD.post_count &gt; NEW.post_count THEN
      UPDATE categories SET post_count = post_count - 1 WHERE categories.category_id = NEW.parent_category_id;
    END IF;
  END IF;
  END $$
  
  DELIMITER ;
  </code></pre>
  
  <p>The error I'm getting is:</p>
  
  <pre><code>#1442 - Can't update table 'categories' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 
  </code></pre>
  
  <p>I figure you can do a count() on each page load to calculate the total posts but on large forums this will slow things down as discussed many times on here (e.g. <a href="https://stackoverflow.com/questions/1969849/count-posts-with-php-or-store-in-database">Count posts with php or store in database</a>). Therefore for future proofing i'm storing the post count in the table. To go one step further I thought i'd use triggers to update these counts rather than PHP.</p>
  
  <p>I understand there are limitations in MySQL for running triggers on the same table that's being updated which is what is causing this error (i.e. to stop an infinite loop) but in this case surely the loop would stop once it reaches a category with a NULL parent_category_id? There must be some kind of solution whether it's adjusting this trigger or something different entirely. Thanks.</p>
  
  <p><strong>EDIT</strong> I appreciate this might not be the best way of doing things but it is the best thing I can think of. I suppose if you changed a parents category to another it would mess things up, but this could be fixed by another trigger which re-syncs everything. I'm open to other suggestions on how to solve this problem.</p>