防止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的down_qty"的前一个父级account_category.尽管我认为可以，但是我不让PostgreSQL进行递归触发器.在触发递归深度级别仅限制为16级别之前，我曾使用过MSSQL.

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 触发器可能会执行以下命令 在该行中插入另一行 同一张表，导致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.

http://www.postgresql.org/docs/8.3/static/trigger-definition.html

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