PL/pgSQL
Triggers
17
Copyright
© Postgres Professional, 2017–2025
Authors Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by Liudmila Mantrova and Alexander Meleshko
Photo by Oleg Bartunov (Tukuche peak, Nepal)
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.com
Disclaimer
In no event shall Postgres Professional company be liable for any damages
or loss, including loss of profits, that arise from direct or indirect, special or
incidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Triggers and trigger functions
Trigger behavior
Execution context of a trigger function
Return values
Do’s and Don’ts
3
Triggers and functions
Trigger
a database object: a list of events to process
once an event occurs, the trigger function is called, and the call context is
passed to this function
Trigger function
a database object: event-processing code
executed in the same transaction as the main operation
convention: the function does not take any parameters,
returns a value of the trigger pseudotype (which is virtually record)
can be reused with multiple triggers
Triggers are used to set off particular actions in response to particular
events. A trigger consists of two parts: the trigger itself (which defines the
events) and a trigger function (which defines the actions). Both the trigger
and the function are independent database objects.
When an event occurs which the trigger is waiting for, the trigger function is
called. It receives the context of the call, which defines the exact trigger that
has called the function and the exact conditions that have led to this call.
A trigger function is a regular function that follows some conventions:
It can be written in any language except pure SQL.
It must have no parameters.
Its return value is of the trigger type (which is actually a pseudotype; a
record corresponding to a table row is returned instead).
The trigger function is executed in the same transaction as the main
operation. Thus, if a trigger function results in an error, the whole transaction
is aborted.
https://postgrespro.com/docs/postgresql/17/trigger-definition
4
Events
INSERT, UPDATE, DELETE
tables before/after statement
before/after row
views before/after statement
instead of row
TRUNCATE
tables before/after statement
WHEN condition
sets an additional filter
Triggers can fire for INSERT, UPDATE, or DELETE operations performed
on tables or views, as well as for the TRUNCATE operation on tables.
A trigger can fire before the specified action (BEFORE), after the action
(AFTER), or instead of the action (INSTEAD OF).
A trigger can fire once for the whole operation (FOR EACH STATEMENT)
or for each affected row (FOR EACH ROW).
There are some combinations of these conditions that are not supported.
For example, INSTEAD OF triggers can be defined only for views at the row
level, while TRUNCATE triggers can be defined only for tables and only at
the statement level. Possible combinations are listed on this slide.
Besides, you can limit the area controlled by the trigger by specifying the
WHEN condition: if this condition is false, the trigger does not fire.
https://postgrespro.com/docs/postgresql/17/sql-createtrigger
5
Before statement
Triggers
before the operation
Return values
is ignored
Context
TG variables
BEFORE STATEMENT
operation execution
Let’s take a closer look at different trigger types.
The BEFORE STATEMENT trigger fires only once per operation, regardless
of the number of affected rows (even if there are none). It happens before
the start of the operation.
The return value of the trigger function is ignored. If there is an error in the
trigger, the operation is canceled. Since the trigger function has no
parameters, the call context in PL/pgSQL is passed via predefined TG
variables, such as:
TG_WHEN = BEFORE
TG_LEVEL = STATEMENT
TG_OP = INSERT/UPDATE/DELETE/TRUNCATE
etc. You can also pass a user-defined context (which is analogous to the
absent parameters) via the TG_ARGV variable, although it is usually
advisable to create several specialized functions instead of a single generic
one.
https://postgrespro.com/docs/postgresql/17/plpgsql-trigger
6
Before row
Triggers
before the action on the row is taken
during the statement execution
Return values
a row (possibly modified)
null cancels the action
Context
OLD update, delete
NEW insert, update
TG variables
BEFORE STATEMENT
operation execution
BEFORE ROW
BEFORE ROW triggers fire each time an operation is about to process a
row. It happens right during the operation execution.
Trigger functions get the context via variables, such as:
OLD — an old state of the row (undefined for insertion)
NEW — an updated state of the row (undefined for deletion)
TG_WHEN = BEFORE
TG_LEVEL = ROW
TG_OP = INSERT/UPDATE/DELETE
etc.
The NULL return value is interpreted as cancellation of the action for the
current row. The execution of the operation itself will continue, but the
current row won’t be processed, and other triggers won’t fire for this row.
To avoid interfering with the operation, the trigger must return the received
row without any modifications, so it must always return NEW for insert and
update operations. For delete operations, it can return any value except
NULL (usually OLD is used).
But the trigger function can also change the NEW value to affect the result
of the operation; this trigger is often defined exactly for this purpose.
7
Instead of row
Triggers
instead of the action on the row
for views
Return values
a row (possibly modified)
is shown in RETURNING
null cancels the action
Context
OLD update, delete
NEW insert, update
TG variables
BEFORE STATEMENT
operation execution
BEFORE ROW
INSTEAD OF ROW
INSTEAD OF triggers are very similar to BEFORE triggers, but they can
be defined only for views and fire instead of the specified operation, not
before it.
Such triggers usually perform operations on the base tables for views. The
trigger can also return a modified NEW value: this value will be available
when performing an operation with the RETURNING clause.
8
After row
Triggers
after operation execution
queue of rows satisfying the WHEN condition
Return values
are ignored
Context
OLD, OLD TABLE update, delete
NEW, NEW TABLE insert, update
TG variables
BEFORE STATEMENT
AFTER ROW
operation execution
BEFORE ROW
INSTEAD OF ROW
Just as BEFORE ROW, AFTER ROW triggers fire for each affected row; but
it happens only after the whole operation is complete, not immediately after
processing the row, to avoid any inconsistencies due to the order the rows
were processed in. For this purpose, all events are placed in a queue and
processed after the operation has finished. The fewer events get queued,
the smaller overhead will be incurred; that’s why it is recommended to use
the WHEN clause in this case, which can filter out the rows that we
definitely won't need.
The return value of the AFTER ROW triggers is ignored (because the
operation is already complete).
The context of the trigger function is constituted by the following variables:
OLD — an old state of the row (undefined for insertion)
NEW — an updated value of the row (undefined for deletion)
Apart from these variables, the trigger function can access special transition
tables. The table specified as OLD TABLE when creating the trigger
contains the old values of the rows processed by the trigger, and the NEW
TABLE contains the new values of the same rows.
Regular TG variables are also available, including the following ones:
TG_WHEN = AFTER
TG_LEVEL = ROW
TG_OP = INSERT/UPDATE/DELETE
9
After statement
Triggers
after the operation
(even if none of the rows are affected)
Return values
are ignored
Context
OLD TABLE update, delete
NEW TABLE insert, update
TG variables
BEFORE STATEMENT
AFTER STATEMENT
AFTER ROW
operation execution
BEFORE ROW
INSTEAD OF ROW
The AFTER STATEMENT trigger fires after the operation has completed
(including all the AFTER ROW triggers, if any). This trigger fires only once
regardless of the number of the affected rows.
The return value of the trigger function is ignored.
The call context is passed using transition tables. The trigger function can
access these table to analyze all the affected rows. Transition tables are
usually used with AFTER STATEMENT, not with AFTER ROW triggers.
Besides, regular TG variables are defined, such as:
TG_WHEN = AFTER
TG_LEVEL = STATEMENT
TG_OP = INSERT/UPDATE/DELETE/TRUNCATE
etc.
11
Possible use cases
changing the base tables for views
consistency checks,
including table-level checks;
logging operations for audit purposes;
cascading table updates (denormalization,
asynchronous processing...)
operation applicability checks
validation,
row modifications
BEFORE STATEMENT
AFTER STATEMENT
AFTER ROW
operation execution
BEFORE ROW
INSTEAD OF ROW
What are triggers actually used for?
BEFORE triggers can be used to check if the operation is valid and to raise
errors if required.
BEFORE ROW triggers can be used to modify a row (for example, fill an
empty field with the required value). It is convenient to use such triggers to
avoid repeating the logic of filling out “technical” fields in each operation, as
well as tweak the application behavior if its code cannot be modified.
INSTEAD OF ROW triggers are used to translate operations on views into
the corresponding operations on the underlying base tables.
AFTER ROW and AFTER STATEMENT triggers can be useful for getting
the exact state after the operation (BEFORE triggers may affect the result,
so the state is not yet clear at this stage):
To check consistency of the operation.
To perform audit operations, i.e., logging all changes in a separate
storage.
To cascade changes to other tables (for example, to update
denormalized data if the base tables have changed, or queue changes
for subsequent processing outside of the current transaction).
If the operation affects multiple rows, it may be more efficient to use AFTER
STATEMENT on transition tables instead of AFTER ROW as it can process
changes in batches.
12
Challenges
The code is called implicitly
the execution logic is hard to track
Visibility rules for volatile trigger functions
the result of BEFORE ROW and INSTEAD OF ROW triggers is visible
The order of calling triggers for one and the same event
triggers fire in the alphabetical order
Infinite looping can occur
a trigger can activate other triggers
Integrity constraints can be broken
for example, by excluding rows that have to be deleted
Triggers should not be overused. As they fire implicitly, the logic of the
application becomes obscure, thus making its maintenance hugely
complicated. Attempts to use triggers for implementing complex logic are
usually quite unfortunate.
In some cases, you can use generated columns instead of triggers
(GENERATED ALWAYS AS ... STORED). If applicable, this solution is sure
to be more transparent and easier to implement.
There is a number of subtle points related to using triggers; we consciously
skip their detailed discussion here:
visibility rules of volatile functions in BEFORE ROW and INSTEAD OF
ROW triggers (do not rely on the order of triggers when accessing a
table)
the order of calling several triggers on one and the same event (do not
aggravate implicit firing of triggers by relying on their exact processing
sequence)
a possibility of infinite looping if cascade firing of triggers leads to another
activation of the first trigger
a risk of integrity constraint violation (for example, referential integrity can
be compromised when skipping a row deleted by the ON DELETE
CASCADE condition)
If you see that these subtleties are important for your application, you
should seriously consider redesigning it.
14
Takeaways
A trigger is a way to address a particular event
Using triggers, you can cancel an operation, modify its outcome,
or perform additional actions
Triggers are executed as part of the main transaction; an error in
a trigger aborts this transaction
Using AFTER ROW triggers and transition tables makes
processing more expensive
Everything is good in moderation: complex logic is hard to
debug because of implicit trigger execution
15
Practice
1. Create a trigger that handles updates of the onhand_qty field in
the catalog_v view.
Check that the Catalog tab now allows ordering books.
2. Make sure that the following consistency requirement is met:
the amount of available books cannot be negative
(it is impossible to buy a book if it is not in stock).
Check your implementation carefully, keeping in mind that the
application can be accessed by several users simultaneously.
2. It may seem that it’s enough to define the AFTER trigger on the
operations table to calculate the qty_change sum. However, at the READ
COMMITTED isolation level used in the Bookstore application, we will have
to acquire an exclusive lock on this table: otherwise, the check may not
function properly in some scenarios.
Here is a better approach: extend the books table with the onhand_qty
column and create a trigger that will be modifying onhand_qty values when
the operations table is changed (i.e., you should virtually perform data
denormalization). You can now define the CHECK constraint on the
onhand_qty field to ensure data consistency. The onhand_qty() function
created earlier is no longer required.
You should pay special attention to setting the initial value, keeping in mind
that the database system may be serving some users while we apply these
changes.