Postgresql 中文操作指南
39.1. Overview of Trigger Behavior #
触发器是一项规范，即无论何时执行特定类型的操作，数据库都应该自动执行特定函数。触发器可以附加到表格（分区或非分区）、视图和外部表格上。
A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be attached to tables (partitioned or not), views, and foreign tables.
在表格和外部表格上，可以定义触发器在任何_INSERT_、_UPDATE_或_DELETE_操作之前或之后执行，无论是对每行的修改还是对 SQL 语句的修改。此外，可以将_UPDATE_触发器设置为仅在_UPDATE_语句的_SET_子句中提到特定列时才触发。触发器也可以触发_TRUNCATE_语句。如果触发事件发生，将在适当的时间调用触发器的函数来处理该事件。
On tables and foreign tables, triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. UPDATE triggers can moreover be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement. Triggers can also fire for TRUNCATE statements. If a trigger event occurs, the trigger’s function is called at the appropriate time to handle the event.
在视图上，可以定义触发器在_INSERT_、UPDATE_或_DELETE_操作的替代操作中执行。对此类_INSTEAD OF_触发器，每个需要在视图中修改的行都会触发一次。有责任的触发器函数对视图的底层基本表格执行必要的修改，并在适当的情况下以它将在视图中显示的形式返回修改后的行。还可以定义视图上的触发器，使它们在 _INSERT、_UPDATE_或_DELETE_操作之前或之后针对每个 SQL 语句执行一次。然而，此类触发器仅在视图上还有_INSTEAD OF_触发器时才会触发。否则，任何针对视图的语句都必须重写成影响其底层基本表格的语句，然后将触发的触发器附加到基本表格上。
On views, triggers can be defined to execute instead of INSERT, UPDATE, or DELETE operations. Such INSTEAD OF triggers are fired once for each row that needs to be modified in the view. It is the responsibility of the trigger’s function to perform the necessary modifications to the view’s underlying base table(s) and, where appropriate, return the modified row as it will appear in the view. Triggers on views can also be defined to execute once per SQL statement, before or after INSERT, UPDATE, or DELETE operations. However, such triggers are fired only if there is also an INSTEAD OF trigger on the view. Otherwise, any statement targeting the view must be rewritten into a statement affecting its underlying base table(s), and then the triggers that will be fired are the ones attached to the base table(s).
在创建触发器本身之前必须先定义触发器函数。触发器函数必须声明为不接收任何参数且返回类型_trigger_的函数。（触发器函数通过特殊传递的_TriggerData_结构接收其输入，而不是以常规函数参数的形式接收。）
The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)
一旦创建了合适的触发器函数，就使用 CREATE TRIGGER 建立该触发器。同一个触发器函数可用于多个触发器。
Once a suitable trigger function has been created, the trigger is established with CREATE TRIGGER. The same trigger function can be used for multiple triggers.
PostgreSQL 既提供 per-row 触发器，又提供 per-statement 触发器。对于每行触发器，触发器函数对于因引发触发器的语句而受到影响的每一行调用一次。相比之下，每语句触发器仅在执行适用的语句时调用一次，无论此语句影响多少行。具体来说，影响零行的语句仍将导致执行任何适用的每语句触发器。这两种类型的触发器有时分别称为 row-level 触发器和 statement-level 触发器。TRUNCATE 的触发器只能在语句级别定义，而不是每行。
PostgreSQL offers both per-row triggers and per-statement triggers. With a per-row trigger, the trigger function is invoked once for each row that is affected by the statement that fired the trigger. In contrast, a per-statement trigger is invoked only once when an appropriate statement is executed, regardless of the number of rows affected by that statement. In particular, a statement that affects zero rows will still result in the execution of any applicable per-statement triggers. These two types of triggers are sometimes called row-level triggers and statement-level triggers, respectively. Triggers on TRUNCATE may only be defined at statement level, not per-row.
触发器还会根据它们是在 before、after 还是 instead of 操作期间触发，对其进行分类。它们分别称为 BEFORE 触发器、AFTER 触发器和 INSTEAD OF 触发器。语句级别的 BEFORE 触发器自然会在语句开始执行任何操作之前触发，而语句级别的 AFTER 触发器则在语句的最末尾触发。这些类型的触发器可以在表、视图或外部表上定义。行级别的 BEFORE 触发器在对特定行进行操作之前立即触发，而行级别的 AFTER 触发器在语句末尾触发（但在任何语句级别的 AFTER 触发器之前）。这些类型的触发器仅可定义在表和外部表上，但不可定义在视图上。INSTEAD OF 触发器仅可定义在视图上，且仅可在行级别；当将视图中的每一行识别为需要进行操作时，它们会立即触发。
Triggers are also classified according to whether they fire before, after, or instead of the operation. These are referred to as BEFORE triggers, AFTER triggers, and INSTEAD OF triggers respectively. Statement-level BEFORE triggers naturally fire before the statement starts to do anything, while statement-level AFTER triggers fire at the very end of the statement. These types of triggers may be defined on tables, views, or foreign tables. Row-level BEFORE triggers fire immediately before a particular row is operated on, while row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers). These types of triggers may only be defined on tables and foreign tables, not views. INSTEAD OF triggers may only be defined on views, and only at row level; they fire immediately as each row in the view is identified as needing to be operated on.
AFTER 触发器的执行可以推迟到事务末尾（而非语句末尾），如果它被定义为 constraint trigger。在所有情况下，触发器都是作为触发它的语句的同一事务的一部分执行的，因此，如果语句或触发器导致错误，两个错误的影响都会被回滚。
The execution of an AFTER trigger can be deferred to the end of the transaction, rather than the end of the statement, if it was defined as a constraint trigger. In all cases, a trigger is executed as part of the same transaction as the statement that triggered it, so if either the statement or the trigger causes an error, the effects of both will be rolled back.
针对继承或分区层次结构中父表的一个语句不会导致受影响的子表的语句级别的触发器被触发；只有父表的语句级别触发器才会被触发。但是，任何受影响的子表的行级别的触发器都会被触发。
A statement that targets a parent table in an inheritance or partitioning hierarchy does not cause the statement-level triggers of affected child tables to be fired; only the parent table’s statement-level triggers are fired. However, row-level triggers of any affected child tables will be fired.
如果 INSERT 包含一个 ON CONFLICT DO UPDATE 子句，那么行级别的 BEFORE INSERT 触发器和行级别的 BEFORE UPDATE 触发器的影响都可以通过已更新行的最终状态来应用（如果一个 EXCLUDED 列被引用的话）。但是，这两组行级别的 BEFORE 触发器执行并不需要存在 EXCLUDED 列引用。如果两个 BEFORE INSERT 和 BEFORE UPDATE 行级别的触发器更改插入/更新的行（即使修改或多或少等价，但也存在它们不幂等的问题），便应考虑出现意外结果的可能性。请注意，无条件 UPDATE 触发器在指定 ON CONFLICT DO UPDATE 时执行，无论 UPDATE 是否影响了任何行（并且不管是否采用了 UPDATE 替代方案）。包含 ON CONFLICT DO UPDATE 子句的 INSERT 将首先执行语句级别的 BEFORE INSERT 触发器，然后执行语句级别的 BEFORE UPDATE 触发器，接着执行语句级别的 AFTER UPDATE 触发器，最后执行语句级别的 AFTER INSERT 触发器。
If an INSERT contains an ON CONFLICT DO UPDATE clause, it is possible that the effects of row-level BEFORE INSERT triggers and row-level BEFORE UPDATE triggers can both be applied in a way that is apparent from the final state of the updated row, if an EXCLUDED column is referenced. There need not be an EXCLUDED column reference for both sets of row-level BEFORE triggers to execute, though. The possibility of surprising outcomes should be considered when there are both BEFORE INSERT and BEFORE UPDATE row-level triggers that change a row being inserted/updated (this can be problematic even if the modifications are more or less equivalent, if they’re not also idempotent). Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by the UPDATE (and regardless of whether the alternative UPDATE path was ever taken). An INSERT with an ON CONFLICT DO UPDATE clause will execute statement-level BEFORE INSERT triggers first, then statement-level BEFORE UPDATE triggers, followed by statement-level AFTER UPDATE triggers and finally statement-level AFTER INSERT triggers.
如果对分区表上的 UPDATE 导致一行移动到另一分区，它将作为一个 DELETE 从原始分区执行，后面跟着一个 INSERT 到新分区。在这种情况下，所有行级别的 BEFORE UPDATE 触发器和所有行级别的 BEFORE DELETE 触发器都在原始分区上触发。然后，目的地分区上触发所有行级别的 BEFORE INSERT 触发器。当所有这些触发器影响正在移动的行时，应考虑出现意外结果的可能性。就 AFTER ROW 触发器而言，AFTER DELETE 和 AFTER INSERT 触发器适用；但 AFTER UPDATE 触发器不适用，因为 UPDATE 已转换为 DELETE 和 INSERT。就语句级别的触发器而言，即使发生行移动，也不触发任何 DELETE 或 INSERT 触发器；仅 UPDATE 在 UPDATE 语句中使用，在目标表上定义的触发器会被触发。
If an UPDATE on a partitioned table causes a row to move to another partition, it will be performed as a DELETE from the original partition followed by an INSERT into the new partition. In this case, all row-level BEFORE UPDATE triggers and all row-level BEFORE DELETE triggers are fired on the original partition. Then all row-level BEFORE INSERT triggers are fired on the destination partition. The possibility of surprising outcomes should be considered when all these triggers affect the row being moved. As far as AFTER ROW triggers are concerned, AFTER DELETE and AFTER INSERT triggers are applied; but AFTER UPDATE triggers are not applied because the UPDATE has been converted to a DELETE and an INSERT. As far as statement-level triggers are concerned, none of the DELETE or INSERT triggers are fired, even if row movement occurs; only the UPDATE triggers defined on the target table used in the UPDATE statement will be fired.
没有为 MERGE 定义单独的触发器。而是根据 (对于语句级别的触发器) MERGE 查询中指定的操作是什么，(对于行级别的触发器) 执行了什么操作，来触发语句级别的或行级别的 UPDATE、DELETE 和 INSERT 触发器。
No separate triggers are defined for MERGE. Instead, statement-level or row-level UPDATE, DELETE, and INSERT triggers are fired depending on (for statement-level triggers) what actions are specified in the MERGE query and (for row-level triggers) what actions are performed.
在运行 MERGE 命令时，针对 MERGE 命令操作中指定事件的语句级别的 BEFORE 和 AFTER 触发器会被触发，而不管是否最终执行操作。这与无条件 UPDATE 语句相同，尽管语句级别触发器被触发，但不会更新任何行。行级别的触发器仅在实际上更新、插入或删除行时触发。因此，对于某类动作，触发语句级别的触发器而没有行级别的触发器被触发时，这完全合法。
While running a MERGE command, statement-level BEFORE and AFTER triggers are fired for events specified in the actions of the MERGE command, irrespective of whether or not the action is ultimately performed. This is the same as an UPDATE statement that updates no rows, yet statement-level triggers are fired. The row-level triggers are fired only when a row is actually updated, inserted or deleted. So it’s perfectly legal that while statement-level triggers are fired for certain types of action, no row-level triggers are fired for the same kind of action.
由每条语句触发器调用的触发器函数应始终返回 NULL。由每行触发器调用的触发器函数可以选择向调用执行器返回一个表行（类型为 HeapTuple 的一个值）。在操作之前触发的行级触发器有以下选择：
Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row triggers can return a table row (a value of type HeapTuple) to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices:
一个不打算导致这两种行为的行级 BEFORE 触发器在返回结果时必须小心，其返回的行和传递给触发器的行保持一致（即对于 INSERT 和 UPDATE 触发器为 NEW 行，对于 DELETE 触发器为 OLD 行）。
A row-level BEFORE trigger that does not intend to cause either of these behaviors must be careful to return as its result the same row that was passed in (that is, the NEW row for INSERT and UPDATE triggers, the OLD row for DELETE triggers).
一个行级 INSTEAD OF 触发器应返回 NULL 以指示它没有修改视图底层基本表的任何数据，或者它应该返回已传递的视图行（对于 INSERT 和 UPDATE 操作的 NEW 行，或者对于 DELETE 操作的 OLD 行）。使用非空返回值来指示触发器在视图中执行了必要的数据修改。这将导致受命令影响的行数计数增加。仅对于 INSERT 和 UPDATE 操作，触发器可以在返回 NEW 行之前对其进行修改。这将更改 INSERT RETURNING 或 UPDATE RETURNING 返回的数据，并在视图未能显示与提供数据完全相同的数据时很有用。
A row-level INSTEAD OF trigger should either return NULL to indicate that it did not modify any data from the view’s underlying base tables, or it should return the view row that was passed in (the NEW row for INSERT and UPDATE operations, or the OLD row for DELETE operations). A nonnull return value is used to signal that the trigger performed the necessary data modifications in the view. This will cause the count of the number of rows affected by the command to be incremented. For INSERT and UPDATE operations only, the trigger may modify the NEW row before returning it. This will change the data returned by INSERT RETURNING or UPDATE RETURNING, and is useful when the view will not show exactly the same data that was provided.
在操作之后触发的行级触发器的返回值将被忽略，因此它们可以返回 NULL。
The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.
针对生成列有一些注意事项。存储的生成列是在 BEFORE 触发器之后和 AFTER 触发器之前计算的。因此，可以在 AFTER 触发器中检查生成的值。在 BEFORE 触发器中，如预期的那样，OLD 行包含旧的生成值，但 NEW 行还不包含新的生成值且不应访问。在 C 语言接口中，此时列的内容未定义；更高级的编程语言应防止在 BEFORE 触发器中的 NEW 行中访问存储的生成列。BEFORE 触发器中生成列值的更改将被忽略并会被覆盖。
Some considerations apply for generated columns. Stored generated columns are computed after BEFORE triggers and before AFTER triggers. Therefore, the generated value can be inspected in AFTER triggers. In BEFORE triggers, the OLD row contains the old generated value, as one would expect, but the NEW row does not yet contain the new generated value and should not be accessed. In the C language interface, the content of the column is undefined at this point; a higher-level programming language should prevent access to a stored generated column in the NEW row in a BEFORE trigger. Changes to the value of a generated column in a BEFORE trigger are ignored and will be overwritten.
如果在同一关系的同一起始事件上定义了多个触发器，那么将按照触发器名称的字母顺序触发触发器。对于 BEFORE 和 INSTEAD OF 触发器，每个触发器返回的可能经过修改的行成为下一个触发器的输入。如果任何 BEFORE 或 INSTEAD OF 触发器返回 NULL，则放弃对该行的操作，并且不会触发后续触发器（对于该行）。
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 and INSTEAD OF triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If any BEFORE or INSTEAD OF trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired (for that row).
触发器定义还可以指定一个布尔 WHEN 条件，将对其进行测试以查看是否应触发触发器。在行级触发器中，WHEN 条件可以检查该行的列的旧值和/或新值。（语句级触发器也可以有 WHEN 条件，尽管该特性对它们不是那么有用。）在 BEFORE 触发器中，WHEN 条件在函数被执行或将被执行之前进行评估，因此使用 WHEN 与在触发器函数开头测试相同条件并无实质性不同。但是，在 AFTER 触发器中，WHEN 条件将在行更新发生后立即进行评估，并且它确定是否将事件排队以便在语句末尾触发触发器。因此，当 AFTER 触发器的 WHEN 条件未返回 true 时，无需排队事件，也无需在语句末尾重新获取行。如果只针对一些行触发触发器，那么这将显着加快修改多行的语句的运行速度。INSTEAD OF 触发器不支持 WHEN 条件。
A trigger definition can also specify a Boolean WHEN condition, which will be tested to see whether the trigger should be fired. In row-level triggers the WHEN condition can examine the old and/or new values of columns of the row. (Statement-level triggers can also have WHEN conditions, although the feature is not so useful for them.) In a BEFORE trigger, the WHEN condition is evaluated just before the function is or would be executed, so using WHEN is not materially different from testing the same condition at the beginning of the trigger function. However, in an AFTER trigger, the WHEN condition is evaluated just after the row update occurs, and it determines whether an event is queued to fire the trigger at the end of statement. So when an AFTER trigger’s WHEN condition does not return true, it is not necessary to queue an event nor to re-fetch the row at end of statement. This can result in significant speedups in statements that modify many rows, if the trigger only needs to be fired for a few of the rows. INSTEAD OF triggers do not support WHEN conditions.
通常，行级 BEFORE 触发器用于检查或修改将要插入或更新的数据。例如，BEFORE 触发器可用于将当前时间插入 timestamp 列，或用于检查行的两个元素是否一致。 AFTER 行级触发器最明智的用途是将更新传播到其他表或对其他表执行一致性检查。进行这种劳动分工的原因是，AFTER 触发器可以确定它正在查看行的最终值，而 BEFORE 触发器则不能；它后面还有其他 BEFORE 触发器正在触发。如果您没有特定的理由使触发器变为 BEFORE 或 AFTER，那么 BEFORE 的情况更高效，因为有关操作的信息不必保存到语句结束时。
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. Row-level AFTER triggers are most sensibly used to propagate the updates to other tables, or make consistency checks against other tables. The reason for this division of labor is that an AFTER trigger can be certain it is seeing the final value of the row, while a BEFORE trigger cannot; there might be other BEFORE triggers firing after it. If you have no specific reason to make a trigger BEFORE or AFTER, the BEFORE case is more efficient, since the information about the operation doesn’t have to be saved until end of statement.
如果触发器函数执行 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.
在定义触发器时，可以为其指定参数。在触发器定义中包含参数的目的是允许具有类似要求的不同触发器调用同一个函数。作为一个示例，可以有一个通用的触发器函数，以两个列名作为其参数，并将当前用户放入第一个参数，将当前时间戳放入另一个参数。如果编写正确，该触发器函数将独立于触发其的特定表。因此，同一个函数可用于任何表上的 INSERT 事件，以自动跟踪事务表中的记录创建，例如。如果定义为 UPDATE 触发器，该函数还可用于跟踪上次更新事件。
When a trigger is being defined, arguments can be specified for it. The purpose of including arguments in the trigger definition is to allow different triggers with similar requirements to call the same function. As an example, there could be a generalized trigger function that takes as its arguments two column names and puts the current user in one and the current time stamp in the other. Properly written, this trigger function would be independent of the specific table it is triggering on. So the same function could be used for INSERT events on any table with suitable columns, to automatically track creation of records in a transaction table for example. It could also be used to track last-update events if defined as an UPDATE trigger.
支持触发器的每种编程语言都有自己的方法可以将触发器输入数据提供给触发器函数。此输入数据包括触发器事件的类型（例如，INSERT 或 UPDATE）以及 CREATE TRIGGER 中列出的任何参数。对于行级触发器，输入数据还包括 INSERT 和 UPDATE 触发器的 NEW 行，和/或 UPDATE 和 DELETE 触发器的 OLD 行。
Each programming language that supports triggers has its own method for making the trigger input data available to the trigger function. This input data includes the type of trigger event (e.g., INSERT or UPDATE) as well as any arguments that were listed in CREATE TRIGGER. For a row-level trigger, the input data also includes the NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers.
在默认情况下，语句级触发器没有任何方法能够检查语句修改的各个行。但是 AFTER STATEMENT 触发器可以请求创建 transition tables，使受影响的行集合可用于触发器。AFTER ROW 触发器还可以请求转换表，以便既能看到表中的全部更改，又能看到当前正在为其触发的各行中的更改。再次检查转换表的方法取决于所使用的编程语言，但典型的方法是使转换表充当可通过触发器函数中发出的 SQL 命令访问的只读临时表。
By default, statement-level triggers do not have any way to examine the individual row(s) modified by the statement. But an AFTER STATEMENT trigger can request that transition tables be created to make the sets of affected rows available to the trigger. AFTER ROW triggers can also request transition tables, so that they can see the total changes in the table as well as the change in the individual row they are currently being fired for. The method for examining the transition tables again depends on the programming language that is being used, but the typical approach is to make the transition tables act like read-only temporary tables that can be accessed by SQL commands issued within the trigger function.