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 down_qty, so this would trigger the update trigger recursively.
Right now, my code is ok - I put this on UPDATE trigger's first line:
-- prevents recursive trigger
if new.track_recursive_trigger <> old.track_recursive_trigger then
return new;
end if;
And this is the sample code from my trigger when I need to update the parent record's qty:
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;
I'm thinking if there's a way in PostgreSQL to detect recursive trigger without introducing a new field, something analogous to MSSQL's trigger_nestlevel.
[EDIT]
I loop inside the tree, I need to bubble up the down_qty of each account_category back to its root. For example, I insert a new account category, it needs to increment the down_qty of its parent account_category, likewise when I change the account category's parent account_category, I need to decrement the down_qty of account_category's previous parent account_category. 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 16 levels.
