title: Prevent recursive trigger in PostgreSQL
body: <p>How to prevent recursive execution of trigger?  Let's say I want to construct a "tree-able" description on chart of account. So what I do is when a new record is inserted/updated, I update the the parent record's <code>down_qty</code>, so this would trigger the update trigger recursively.</p>
  
  <p>Right now, my code is ok - I put this on <code>UPDATE</code> trigger's first line:</p>
  
  <pre><code>-- prevents recursive trigger
  if new.track_recursive_trigger &lt;&gt; old.track_recursive_trigger then
      return new;
  end if;
  </code></pre>
  
  <p>And this is the sample code from my trigger when I need to update the parent record's qty:</p>
  
  <pre><code>update account_category set 
      track_recursive_trigger = track_recursive_trigger + 1, -- i put this line to prevent recursive trigger
      down_qty = down_qty - (old.down_qty + 1)
  where account_category_id = m_parent_account;
  </code></pre>
  
  <p>I'm thinking if there's a way in PostgreSQL to detect recursive trigger without introducing a new field, something analogous to MSSQL's <code>trigger_nestlevel</code>.</p>
  
  <p><strong>[EDIT]</strong></p>
  
  <p>I loop inside the tree, I need to bubble up the <code>down_qty</code> of each <code>account_category</code> back to its root.  For example, I insert a new account category, it needs to increment the <code>down_qty</code> of its parent <code>account_category</code>, likewise when I change the account category's parent <code>account_category</code>, I need to decrement the <code>down_qty</code> of <code>account_category</code>'s previous parent <code>account_category</code>.  Though I think it can, I'm not letting PostgreSQL do the recursive trigger.  I used MSSQL before where the trigger recursive depth level is limited only up to <code>16</code> levels.</p>