40.1. Overview of Event Trigger Behavior

40.1. Overview of Event Trigger Behavior
Prev	Up	Chapter 40. Event Triggers	Home	Next

40.1. Overview of Event Trigger Behavior #

+ 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. +

+ 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. +

+ 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. +

+ 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. +

+ 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. +

+ 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. +

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

+ 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. +

+ 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. +