防止 PostgreSQL 中的递归触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/708562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prevent recursive trigger in PostgreSQL
提问by Hao
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.
如何防止触发器的递归执行？假设我想在科目表上构建一个“树状”描述。所以我所做的是当插入/更新新记录时，我更新父记录的down_qty，因此这将递归触发更新触发器。
Right now, my code is ok - I put this on UPDATEtrigger's first line:
现在，我的代码没问题——我把它放在UPDATE触发器的第一行：
-- 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.
我在想 PostgreSQL 中是否有一种方法可以在不引入新字段的情况下检测递归触发器，类似于 MSSQL 的trigger_nestlevel.
[EDIT]
[编辑]
I loop inside the tree, I need to bubble up the down_qtyof each account_categoryback to its root. For example, I insert a new account category, it needs to increment the down_qtyof its parent account_category, likewise when I change the account category's parent account_category, I need to decrement the down_qtyof 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 16levels.
我在树内循环，我需要将down_qty每个的冒泡account_category回到它的根。例如，我插入一个新的账户类别，它需要递增down_qty其父的account_category，同样，当我更改帐户类别的父母account_category，我需要递减down_qty的account_category的早先父account_category。尽管我认为可以，但我不会让 PostgreSQL 执行递归触发器。我之前使用过 MSSQL，其中触发器递归深度级别仅限于16级别。
采纳答案by tpdi
In pg, it's up to you to track trigger recursion.
在 pg 中，由您来跟踪触发器递归。
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.
如果触发器函数执行 SQL 命令，那么这些命令可能会再次触发触发器。这称为级联触发器。级联级别的数量没有直接限制。级联可能会导致对同一触发器的递归调用；例如，INSERT 触发器可能会执行将额外行插入同一个表中的命令，从而导致再次触发 INSERT 触发器。在这种情况下避免无限递归是触发器程序员的责任。
http://www.postgresql.org/docs/8.3/static/trigger-definition.html
http://www.postgresql.org/docs/8.3/static/trigger-definition.html
回答by agnessa
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.
这就是我在 PostgreSQL 9.2 中所做的，尽管我必须承认我没有找到这种方法的文档。这里pg_trigger_depth()记录了一个函数，我用它来区分触发器中的原始调用和嵌套调用。
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()
回答by diegom_ch
At the beggining of the definition of the trigger you can disable triggers on that particular table, and reenable them at the end (and make sure an exception doesn't terminate the execution before expected!). This has many deep holes, but may work for some light implementations. Notice that for this implementation, you will also need priviliges to disable triggers.
在触发器定义的开始，您可以禁用该特定表上的触发器，并在最后重新启用它们（并确保异常不会在预期之前终止执行！）。这有很多深洞，但可能适用于一些轻量级的实现。请注意，对于此实现，您还需要特权来禁用触发器。
回答by wildplasser
To avoid unbounded recursion, see my answer here. As others have commented, if your data structure is a true tree (the root(s) will have no parent(s)) and the recursion will always stopat the root(s). For nodeswith only one parent pointer, the only way for unbounded recursion would be if there were loops present. (the method in my link will visit any node at mostonce)
为避免无限递归，请在此处查看我的答案。正如其他人评论的那样，如果您的数据结构是真正的树（根将没有父级）并且递归将始终在根处停止。对于只有一个父指针的节点，无界递归的唯一方法是存在循环。（我链接中的方法最多访问任何节点一次）
