Event Triggers
event trigger
To supplement the trigger mechanism discussed in ,
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.
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
and ddl_command_end. 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. As an exception, however, this event does not occur for
DDL commands targeting shared objects - databases, roles, and tablespaces
- or for command 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.
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 .
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.
Event Trigger Firing Matrix
lists all commands
for which event triggers are supported.
Event Trigger Support by Command Tag
command tag
ddl_command_start
ddl_command_end
ALTER AGGREGATE
X
X
ALTER COLLATION
X
X
ALTER CONVERSION
X
X
ALTER DOMAIN
X
X
ALTER EXTENSION
X
X
ALTER FOREIGN DATA WRAPPER
X
X
ALTER FOREIGN TABLE
X
X
ALTER FUNCTION
X
X
ALTER LANGUAGE
X
X
ALTER OPERATOR
X
X
ALTER OPERATOR CLASS
X
X
ALTER OPERATOR FAMILY
X
X
ALTER SCHEMA
X
X
ALTER SEQUENCE
X
X
ALTER SERVER
X
X
ALTER TABLE
X
X
ALTER TEXT SEARCH CONFIGURATION
X
X
ALTER TEXT SEARCH DICTIONARY
X
X
ALTER TEXT SEARCH PARSER
X
X
ALTER TEXT SEARCH TEMPLATE
X
X
ALTER TRIGGER
X
X
ALTER TYPE
X
X
ALTER USER MAPPING
X
X
ALTER VIEW
X
X
CREATE AGGREGATE
X
X
CREATE CAST
X
X
CREATE COLLATION
X
X
CREATE CONVERSION
X
X
CREATE DOMAIN
X
X
CREATE EXTENSION
X
X
CREATE FOREIGN DATA WRAPPER
X
X
CREATE FOREIGN TABLE
X
X
CREATE FUNCTION
X
X
CREATE INDEX
X
X
CREATE LANGUAGE
X
X
CREATE OPERATOR
X
X
CREATE OPERATOR CLASS
X
X
CREATE OPERATOR FAMILY
X
X
CREATE RULE
X
X
CREATE SCHEMA
X
X
CREATE SEQUENCE
X
X
CREATE SERVER
X
X
CREATE TABLE
X
X
CREATE TABLE AS
X
X
CREATE TEXT SEARCH CONFIGURATION
X
X
CREATE TEXT SEARCH DICTIONARY
X
X
CREATE TEXT SEARCH PARSER
X
X
CREATE TEXT SEARCH TEMPLATE
X
X
CREATE TRIGGER
X
X
CREATE TYPE
X
X
CREATE USER MAPPING
X
X
CREATE VIEW
X
X
DROP AGGREGATE
X
X
DROP CAST
X
X
DROP COLLATION
X
X
DROP CONVERSION
X
X
DROP DOMAIN
X
X
DROP EXTENSION
X
X
DROP FOREIGN DATA WRAPPER
X
X
DROP FOREIGN TABLE
X
X
DROP FUNCTION
X
X
DROP INDEX
X
X
DROP LANGUAGE
X
X
DROP OPERATOR
X
X
DROP OPERATOR CLASS
X
X
DROP OPERATOR FAMILY
X
X
DROP RULE
X
X
DROP SCHEMA
X
X
DROP SEQUENCE
X
X
DROP SERVER
X
X
DROP TABLE
X
X
DROP TEXT SEARCH CONFIGURATION
X
X
DROP TEXT SEARCH DICTIONARY
X
X
DROP TEXT SEARCH PARSER
X
X
DROP TEXT SEARCH TEMPLATE
X
X
DROP TRIGGER
X
X
DROP TYPE
X
X
DROP USER MAPPING
X
X
DROP VIEW
X
X
SELECT INTO
X
X