防止 PostgreSQL 中的递归触发器 [英] Prevent recursive trigger in PostgreSQL

问题描述

如何防止触发器的递归执行?假设我想在科目表上构建一个树状"描述.所以我所做的是在插入/更新新记录时，我更新父记录的 down_qty，因此这将递归触发更新触发器.

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.

现在，我的代码没问题 - 我把它放在 UPDATE 触发器的第一行:

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;

我在考虑 PostgreSQL 中是否有一种方法可以在不引入新字段的情况下检测递归触发器，类似于 MSSQL 的 trigger_nestlevel.

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.

我在树内循环，我需要将每个 account_category 的 down_qty 冒泡回其根.例如，我插入一个新的帐户类别，它需要增加其父account_category的down_qty，同样当我更改帐户类别的父account_category>，我需要减少account_category 的前一个父account_category 的down_qty.尽管我认为可以，但我不会让 PostgreSQL 执行递归触发器.我之前使用过 MSSQL，其中触发器递归深度级别仅限于 16 级别.

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.

推荐答案

在 pg 中，跟踪触发器递归由您决定.

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

如果触发器函数执行 SQL命令那么这些命令可能火再次触发.这被称为级联触发器.没有直接级联数量限制水平.级联是可能的导致递归调用相同的触发器；例如，插入触发器可能会执行一个命令插入一个额外的行到同一个表，导致 INSERT 触发器再次被解雇.这是触发器程序员避免的责任在这种情况下无限递归.

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

这篇关于防止 PostgreSQL 中的递归触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！