postgresql - Postgres : Which is the order execution of triggers that are specified for different releations but activated by the same event?

Question

我是数据库触发器的新手，我被这个问题困住了:

假设我有两个不同的关系，R1 和 R2，我为它们指定了两个“独立”触发器。我的意思是，为 R1 触发 TR1，为 R2 触发 TR2。并假设为同一事件触发了两个触发器。

他们的执行顺序是什么？

PostrgresSQL 文档说明了定义在相同 关系上的触发器会发生什么。但是对于定义在不同关系上但由同一事件触发的触发器尚不清楚。我想知道，在这种情况下，触发器是否以随机顺序触发。

36.1. Overview of Trigger Behavior: If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name. In the case of before triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If any before trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired.

Answer 1

... suppose that both triggers are fired for the same event.

这只会在两种情况下发生(在 PostgreSQL 中):

1. 具有参照完整性约束(例如外键)
2. 级联/递归触发器触发

参照完整性约束

这些类型的约束将创建触发器以确保参照完整性。对于外键，(自动)创建的触发器的名称将类似于 RI_ConstraintTrigger... SQLFiddle 。相同的规则适用于它们(它们将按触发器名称的字母顺序在其他触发器中被触发)除了在一种情况下:它们可以被推迟( globally 或 initially deferred per constraints )。这意味着它们只会在当前事务结束时执行(或者当 SET CONSTRAINTS 将约束模式从 DEFERRED 更改为 IMMEDIATE).

您还可以使用 CREATE CONSTRAINT TRIGGER ... 创建自定义约束触发器，这将表现相同。

级联/递归触发器触发

文档中明确提到了这一点:

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.

他们的顺序非常简单，f.ex:

如果 R1 上的 UPDATE 将触发 TR1 UPDATE 的 R2，这将触发 TR2(以及两个 BEFORE 触发器):

1) BEFORE triggers of R1 (f.ex. TR1)   1.1) BEFORE triggers of R2 (f.ex. TR2)   1.2) INSTEAD OF triggers of R2 (or do the actual operation)   1.3) AFTER triggers of R22) INSTEAD OF triggers of R1 (or do the actual operation)3) AFTER triggers of R1

你可能会想，还有一个:inheritance呢？ ？

继承表不仅不继承触发器，它们也不会触发其祖先的触发器。

SQLFiddle