Postgresql 中文操作指南

40.1. Overview of Event Trigger Behavior #

每当与其关联的事件在定义它的数据库中发生时，事件触发器就会执行。目前，仅支持 ddl_command_start、ddl_command_end、table_rewrite 和 sql_drop 事件。未来版本中可能会添加对其他事件的支持。

An event trigger fires whenever the event with which it is associated occurs in the database in which it is defined. Currently, the only supported events are ddl_command_start, ddl_command_end, table_rewrite and sql_drop. Support for additional events may be added in future releases.

ddl_command_start 事件在 CREATE、ALTER、DROP、SECURITY LABEL、COMMENT、GRANT 或 REVOKE 命令执行前发生。在事件触发器执行前，不会执行任何检查以判断受影响的对象是否存在。但是，作为一个例外，对于针对共享对象（数据库、角色和表空间）的 DDL 命令或针对事件触发器自身的命令，此事件不会发生。事件触发器机制不支持此类对象类型。ddl_command_start 也在 SELECT INTO 命令执行前发生，因为这相当于 CREATE TABLE AS。

The ddl_command_start event occurs just before the execution of a CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT or REVOKE command. No check whether the affected object exists or doesn’t exist is performed before the event trigger fires. As an exception, however, this event does not occur for DDL commands targeting shared objects — databases, roles, and tablespaces — or for commands targeting event triggers themselves. The event trigger mechanism does not support these object types. ddl_command_start also occurs just before the execution of a SELECT INTO command, since this is equivalent to CREATE TABLE AS.

ddl_command_end 事件发生在执行此组命令之后的瞬间。要获得所发生的 DDL 操作的更多详细信息，请使用 ddl_command_end 事件触发器代码的 set-returning 函数 pg_event_trigger_ddl_commands() (请参阅 Section 9.29)。请注意，触发器是在动作发生后（但在事务提交之前）触发的，因此系统目录可以读取为已更改。

The ddl_command_end event occurs just after the execution of this same set of commands. To obtain more details on the DDL operations that took place, use the set-returning function pg_event_trigger_ddl_commands() from the ddl_command_end event trigger code (see Section 9.29). Note that the trigger fires after the actions have taken place (but before the transaction commits), and thus the system catalogs can be read as already changed.

在发生释放数据库对象的任何操作的 ddl_command_end 事件触发器之前，会发生 sql_drop 事件。如需列出已被释放的对象，请从 sql_drop 事件触发器代码 (详见 Section 9.29) 使用返回集合的功能 pg_event_trigger_dropped_objects()。注意，该触发器在从系统目录中删除对象后执行，因此不再能够查找它们。

The sql_drop event occurs just before the ddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning function pg_event_trigger_dropped_objects() from the sql_drop event trigger code (see Section 9.29). Note that the trigger is executed after the objects have been deleted from the system catalogs, so it’s not possible to look them up anymore.

table_rewrite 事件在使用 ALTER TABLE 和 ALTER TYPE 命令中的部分操作对表重新写入之前发生。虽然可用其他控制语句（例如 CLUSTER 和 VACUUM）对表重新写入，但 table_rewrite 事件不会触发它们。

The table_rewrite event occurs just before a table is rewritten by some actions of the commands ALTER TABLE and ALTER TYPE. While other control statements are available to rewrite a table, like CLUSTER and VACUUM, the table_rewrite event is not triggered by them.

事件触发器（以及其他函数）无法在已中止的事务中执行。因此，如果 DDL 命令因某个错误而失败，则任何关联的 ddl_command_end 触发器都无法执行。相反，如果 ddl_command_start 触发器因某个错误而失败，则后续事件触发器将无法执行，并且系统不会尝试执行命令本身。同样，如果 ddl_command_end 触发器因某个错误而失败，则 DDL 语句的效果将回滚，就像在包含事务中止的任何其他情况下一样。

Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associated ddl_command_end triggers will not be executed. Conversely, if a ddl_command_start trigger fails with an error, no further event triggers will fire, and no attempt will be made to execute the command itself. Similarly, if a ddl_command_end trigger fails with an error, the effects of the DDL statement will be rolled back, just as they would be in any other case where the containing transaction aborts.

有关事件触发机制支持的命令完整列表，请参阅 Section 40.2。

For a complete list of commands supported by the event trigger mechanism, see Section 40.2.

事件触发器使用 CREATE EVENT TRIGGER 命令创建。为了创建事件触发器，您首先必须使用特殊返回类型 event_trigger 创建一个函数。此函数不需要（并且可能不）返回值；返回类型仅用作指示该函数应作为事件触发器调用的信号。

Event triggers are created using the command CREATE EVENT TRIGGER. In order to create an event trigger, you must first create a function with the special return type event_trigger. This function need not (and may not) return a value; the return type serves merely as a signal that the function is to be invoked as an event trigger.

如果针对特定事件定义了多个事件触发器，则按事件触发器名称以字母顺序执行这些事件触发器。

If more than one event trigger is defined for a particular event, they will fire in alphabetical order by trigger name.

触发器定义也可以指定 WHEN 条件，这样，例如，仅针对用户希望拦截的特定命令触发 ddl_command_start 触发器。这类触发器的常用方法是限制用户可以执行的 DDL 操作范围。

A trigger definition can also specify a WHEN condition so that, for example, a ddl_command_start trigger can be fired only for particular commands which the user wishes to intercept. A common use of such triggers is to restrict the range of DDL operations which users may perform.