Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Prevent recursive trigger in PostgreSQL

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.

like image 640
Hao Avatar asked Apr 02 '09 06:04

Hao


People also ask

How can Recursive Triggers be prevented?

How to avoid Recursive Trigger: To avoid recursive triggers you can create a class with a static Boolean variable with default value true. In the trigger, before executing your code keep a check that the variable is true or not. Once you check make the variable false.

What is instead of trigger in PostgreSQL?

INSTEAD OF triggers do not support WHEN conditions. Typically, row-level BEFORE triggers are used for checking or modifying the data that will be inserted or updated. For example, a BEFORE trigger might be used to insert the current time into a timestamp column, or to check that two elements of the row are consistent.

Are Postgres Triggers transactional?

All PostgreSQL triggers execute in the same transaction as the transaction that has triggered them. Edit: You can also use LISTEN + NOTIFY to send a message from your trigger to a code that executes outside of the transaction. In that case, the message will only be delivered at the point of a successful commit.


2 Answers

This is what I do in PostgreSQL 9.2, although I must admit I did not find this approach documented. There is a function pg_trigger_depth() documented here, which I use to differentiate between original and nested calls in the trigger.

CREATE TRIGGER trg_taxonomic_positions
AFTER INSERT OR UPDATE OF taxonomic_position
ON taxon_concepts
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE trg_taxonomic_positions()
like image 154
agnessa Avatar answered Oct 23 '22 11:10

agnessa


In pg, it's up to you to track trigger recursion.

If a trigger function executes SQL commands then these commands might fire triggers again. This is known as cascading triggers. There is no direct limitation on the number of cascade levels. It is possible for cascades to cause a recursive invocation of the same trigger; for example, an INSERT trigger might execute a command that inserts an additional row into the same table, causing the INSERT trigger to be fired again. It is the trigger programmer's responsibility to avoid infinite recursion in such scenarios.

https://www.postgresql.org/docs/13/trigger-definition.html

like image 8
tpdi Avatar answered Oct 23 '22 11:10

tpdi