A trigger is a specification that the database should
automatically execute a particular function whenever a certain type
of operation is performed. Triggers can be attached to tables,
views, and foreign tables.

On tables and foreign tables, triggers can be defined to execute
either before or after any INSERT,
UPDATE, or DELETE
operation, either once per modified row, or once per
SQL statement. If an INSERT contains an ON CONFLICT
DO UPDATE clause, it is possible that the effects of a BEFORE
insert trigger and a BEFORE update trigger can both be applied
together, if a reference to an EXCLUDED
column appears. UPDATE triggers can
moreover be set to fire only if certain columns are mentioned in
the SET clause of the UPDATE statement. Triggers can also fire for
TRUNCATE statements. If a trigger event
occurs, the trigger's function is called at the appropriate time to
handle the event. Foreign tables do not support the TRUNCATE
statement at all.

On views, triggers can be defined to execute instead of
INSERT, UPDATE,
or DELETE operations. Such INSTEAD OF triggers are fired once for each row that
needs to be modified in the view. It is the responsibility of the
trigger's function to perform the necessary modifications to the
view's underlying base table(s) and, where appropriate, return the
modified row as it will appear in the view. Triggers on views can
also be defined to execute once per SQL statement, before or after INSERT, UPDATE, or
DELETE operations. However, such triggers
are fired only if there is also an INSTEAD
OF trigger on the view. Otherwise, any statement targeting the
view must be rewritten into a statement affecting its underlying
base table(s), and then the triggers that will be fired are the
ones attached to the base table(s).

The trigger function must be defined before the trigger itself
can be created. The trigger function must be declared as a function
taking no arguments and returning type trigger. (The trigger function receives its input
through a specially-passed TriggerData
structure, not in the form of ordinary function arguments.)

Once a suitable trigger function has been created, the trigger
is established with CREATE
TRIGGER. The same trigger function can be used for multiple
triggers.

PostgreSQL offers both
per-row triggers and per-statement triggers. With a per-row trigger, the
trigger function is invoked once for each row that is affected by
the statement that fired the trigger. In contrast, a per-statement
trigger is invoked only once when an appropriate statement is
executed, regardless of the number of rows affected by that
statement. In particular, a statement that affects zero rows will
still result in the execution of any applicable per-statement
triggers. These two types of triggers are sometimes called
row-level triggers and statement-level triggers, respectively. Triggers on
TRUNCATE may only be defined at statement
level. On views, triggers that fire before or after may only be
defined at statement level, while triggers that fire instead of an
INSERT, UPDATE,
or DELETE may only be defined at row
level.

Triggers are also classified according to whether they fire
before, after, or
instead of the operation. These are
referred to as BEFORE triggers, AFTER triggers, and INSTEAD
OF triggers respectively. Statement-level BEFORE triggers naturally fire before the statement
starts to do anything, while statement-level AFTER triggers fire at the very end of the
statement. These types of triggers may be defined on tables or
views. Row-level BEFORE triggers fire
immediately before a particular row is operated on, while row-level
AFTER triggers fire at the end of the
statement (but before any statement-level AFTER triggers). These types of triggers may only be
defined on tables and foreign tables. Row-level INSTEAD OF triggers may only be defined on views,
and fire immediately as each row in the view is identified as
needing to be operated on.

If an INSERT contains an ON CONFLICT DO UPDATE clause, it is possible that
the effects of all row-level BEFOREINSERT triggers and all row-level BEFORE
UPDATE triggers can both be applied in a
way that is apparent from the final state of the updated row, if an
EXCLUDED column is referenced. There need
not be an EXCLUDED column reference for
both sets of BEFORE row-level triggers to execute, though. The
possibility of surprising outcomes should be considered when there
are both BEFOREINSERT and BEFOREUPDATE row-level triggers that both affect
a row being inserted/updated (this can still be problematic if the
modifications are more or less equivalent if they're not also
idempotent). Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of
whether or not any rows were affected by the UPDATE (and regardless of whether the alternative
UPDATE path was ever taken). An INSERT with an ON CONFLICT DO
UPDATE clause will execute statement-level BEFOREINSERT triggers
first, then statement-level BEFOREUPDATE triggers, followed by
statement-level AFTERUPDATE triggers and finally statement-level
AFTERINSERT
triggers.

Trigger functions invoked by per-statement triggers should
always return NULL. Trigger functions
invoked by per-row triggers can return a table row (a value of type
HeapTuple) to the calling executor, if
they choose. A row-level trigger fired before an operation has the
following choices:

It can return NULL to skip the operation
for the current row. This instructs the executor to not perform the
row-level operation that invoked the trigger (the insertion,
modification, or deletion of a particular table row).

For row-level INSERT and UPDATE triggers only, the returned row becomes the
row that will be inserted or will replace the row being updated.
This allows the trigger function to modify the row being inserted
or updated.

A row-level BEFORE trigger that does
not intend to cause either of these behaviors must be careful to
return as its result the same row that was passed in (that is, the
NEW row for INSERT and UPDATE triggers,
the OLD row for DELETE triggers).

A row-level INSTEAD OF trigger should
either return NULL to indicate that it did
not modify any data from the view's underlying base tables, or it
should return the view row that was passed in (the NEW row for INSERT and
UPDATE operations, or the OLD row for DELETE
operations). A nonnull return value is used to signal that the
trigger performed the necessary data modifications in the view.
This will cause the count of the number of rows affected by the
command to be incremented. For INSERT and
UPDATE operations, the trigger may modify
the NEW row before returning it. This will
change the data returned by INSERT
RETURNING or UPDATE RETURNING, and is
useful when the view will not show exactly the same data that was
provided.

The return value is ignored for row-level triggers fired after
an operation, and so they can return NULL.

If more than one trigger is defined for the same event on the
same relation, the triggers will be fired in alphabetical order by
trigger name. In the case of BEFORE and
INSTEAD OF triggers, the possibly-modified
row returned by each trigger becomes the input to the next trigger.
If any BEFORE or INSTEAD OF trigger returns NULL, the operation is abandoned for that row and
subsequent triggers are not fired (for that row).

A trigger definition can also specify a Boolean WHEN condition, which will be tested to see whether
the trigger should be fired. In row-level triggers the WHEN condition can examine the old and/or new values
of columns of the row. (Statement-level triggers can also have
WHEN conditions, although the feature is
not so useful for them.) In a BEFORE
trigger, the WHEN condition is evaluated
just before the function is or would be executed, so using
WHEN is not materially different from
testing the same condition at the beginning of the trigger
function. However, in an AFTER trigger,
the WHEN condition is evaluated just after
the row update occurs, and it determines whether an event is queued
to fire the trigger at the end of statement. So when an AFTER trigger's WHEN
condition does not return true, it is not necessary to queue an
event nor to re-fetch the row at end of statement. This can result
in significant speedups in statements that modify many rows, if the
trigger only needs to be fired for a few of the rows. INSTEAD OF triggers do not support WHEN conditions.

Typically, row-level BEFORE triggers
are used for checking or modifying the data that will be inserted
or updated. For example, a BEFORE trigger
might be used to insert the current time into a timestamp column, or to check that two elements of the
row are consistent. Row-level AFTER
triggers are most sensibly used to propagate the updates to other
tables, or make consistency checks against other tables. The reason
for this division of labor is that an AFTER trigger can be certain it is seeing the final
value of the row, while a BEFORE trigger
cannot; there might be other BEFORE
triggers firing after it. If you have no specific reason to make a
trigger BEFORE or AFTER, the BEFORE case is
more efficient, since the information about the operation doesn't
have to be saved until end of statement.

If a trigger function executes SQL commands then these commands
might fire triggers again. This is known as cascading triggers.
There is no direct limitation on the number of cascade levels. It
is possible for cascades to cause a recursive invocation of the
same trigger; for example, an INSERT
trigger might execute a command that inserts an additional row into
the same table, causing the INSERT trigger
to be fired again. It is the trigger programmer's responsibility to
avoid infinite recursion in such scenarios.

When a trigger is being defined, arguments can be specified for
it. The purpose of including arguments in the trigger definition is
to allow different triggers with similar requirements to call the
same function. As an example, there could be a generalized trigger
function that takes as its arguments two column names and puts the
current user in one and the current time stamp in the other.
Properly written, this trigger function would be independent of the
specific table it is triggering on. So the same function could be
used for INSERT events on any table with
suitable columns, to automatically track creation of records in a
transaction table for example. It could also be used to track
last-update events if defined as an UPDATE
trigger.

Each programming language that supports triggers has its own
method for making the trigger input data available to the trigger
function. This input data includes the type of trigger event (e.g.,
INSERT or UPDATE)
as well as any arguments that were listed in CREATE TRIGGER. For a row-level trigger, the input
data also includes the NEW row for
INSERT and UPDATE
triggers, and/or the OLD row for
UPDATE and DELETE
triggers. Statement-level triggers do not currently have any way to
examine the individual row(s) modified by the statement.

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.