To supplement the trigger mechanism discussed in Chapter 36, Triggers, PostgreSQL™ also provides event triggers. Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.
Like regular triggers, event triggers can be written in any procedural language that includes event trigger support, or in C, but not in plain SQL.
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
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
, or DROP
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.
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
the section called “Event Trigger Functions”). 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.
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 the section called “Event Trigger Firing Matrix”.
Event triggers are created using the command CREATE EVENT TRIGGER(7).
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.