Movatterモバイル変換

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 areddl_command_start,ddl_command_end,table_rewrite andsql_drop. Support for additional events may be added in future releases.

Theddl_command_start event occurs just before the execution of aCREATE,ALTER,DROP,SECURITY LABEL,COMMENT,GRANT orREVOKE 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 aSELECT INTO command, since this is equivalent toCREATE TABLE AS.

Theddl_command_end event occurs just after the execution of this same set of commands. To obtain more details on theDDL operations that took place, use the set-returning functionpg_event_trigger_ddl_commands() from theddl_command_end event trigger code (seeSection 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.

Thesql_drop event occurs just before theddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning functionpg_event_trigger_dropped_objects() from thesql_drop event trigger code (seeSection 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.

Thetable_rewrite event occurs just before a table is rewritten by some actions of the commandsALTER TABLE andALTER TYPE. While other control statements are available to rewrite a table, likeCLUSTER andVACUUM, thetable_rewrite event is not triggered by them. To find the OID of the table that was rewritten, use the functionpg_event_trigger_table_rewrite_oid() (seeSection 9.29). To discover the reason(s) for the rewrite, use the functionpg_event_trigger_table_rewrite_reason().

Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associatedddl_command_end triggers will not be executed. Conversely, if addl_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 addl_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.

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

Event triggers are created using the commandCREATE EVENT TRIGGER. In order to create an event trigger, you must first create a function with the special return typeevent_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.

A trigger definition can also specify aWHEN condition so that, for example, addl_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.