Triggers
Overview
A trigger is a specification that the PostgreSQL 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, you can define triggers to execute either before or after any INSERT
, UPDATE
, or DELETE
operation, either once per modified row, or once per SQL statement. UPDATE
triggers can also be configured 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 to handle the event. Trigger functions can be written in most of the available procedural languages, including PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python.
On views, triggers can be defined to execute instead of INSERT
, UPDATE
, or DELETE
operations. INSTEAD OF
triggers are fired once for each view row that should be modified. The trigger function is responsible for making changes to the view’s underlying tables and, if necessary, returning the modified rows as they will appear in the view. Triggers on views can also be defined to execute before or after INSERT
, UPDATE
, or DELETE
operations. However, these 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 operating with its underlying base tables, and the triggers attached to the base tables will be fired.
The trigger function must be defined before the trigger creation. The trigger function must be declared as a function with no arguments and returning the TRIGGER
type. The same function can be used for multiple triggers.
Trigger types
Row-level and statement-level triggers
PostgreSQL offers per-row triggers and per-statement triggers. With a per-row trigger, PostgreSQL invokes a trigger function once for each row affected by the statement firing the trigger.
PostgreSQL invokes a per-statement trigger only once when an appropriate statement is executed, regardless of the number of rows processed by this statement. If a statement changes zero rows, it also raises per-statement triggers.
These two types of triggers are called row-level triggers and statement-level triggers, respectively.
Triggers on the TRUNCATE
statement can only be defined at statement level, not at row level.
BEFORE, AFTER, and INSTEAD OF triggers
Triggers are also classified according to whether they fire before, after, or instead of the operation. Statement-level BEFORE
triggers fire before the statement starts doing anything, statement-level AFTER
triggers raise at the end of the statement. These types of triggers may be defined on tables, views, or foreign tables.
Row-level BEFORE
triggers fire before a particular row is processed, row-level AFTER
triggers fire at the end of the statement but before any statement-level AFTER
triggers. These types of triggers are not recommended to use with views.
INSTEAD OF
triggers can only be defined on views, and only at row level. They fire as soon as the view row is identified as being processed.
Typically, row-level BEFORE
triggers are used for checking or modifying the data that will be inserted or updated. For example, a BEFORE
trigger can 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 effective for cascading updates of other tables or checking that changes made are consistent with data in other tables.
An AFTER
trigger can access the final value of the row, while a BEFORE
trigger does not, because there may be other BEFORE
triggers that fire later. If there is no special reason to choose between BEFORE
or AFTER
triggers, the BEFORE
trigger is preferable because it does not require the information about the operation be retained until the end of the statement.
Operations with triggers
Create a trigger
To create a new trigger, you need to perform the following steps:
-
Create a trigger function.
-
Use the CREATE TRIGGER statement to bind the trigger function to a relation.
Consider an example that logs changes — stores changes to an existing table in a separate table. The example uses a BEFORE UPDATE
trigger.
There is a books
table that keeps information about books:
CREATE TABLE books (
id SERIAL PRIMARY KEY,
book_name VARCHAR NOT NULL,
price NUMERIC NOT NULL
);
INSERT INTO
books( book_name, price)
VALUES
('Hyperion',21),
('War and Peace',26),
('1984',20),
('The Time Machine',19);
Create a new table to save changes:
CREATE TABLE books_log (
id SERIAL PRIMARY KEY,
book_id INT,
book_name VARCHAR NOT NULL,
price NUMERIC NOT NULL,
change_date TIMESTAMP NOT NULL
);
Create a trigger function. Use a procedural language for this. The code below illustrates the syntax of a PL/pgSQL trigger function. It stores old values from books
and date of changes to the books_log
table:
CREATE OR REPLACE FUNCTION save_changes()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
IF (NEW.book_name <> OLD.book_name) OR (NEW.price <> OLD.price) THEN
INSERT INTO books_log(book_id,book_name,price,change_date)
VALUES(OLD.id,OLD.book_name,OLD.price, now());
END IF;
RETURN NEW;
END;
$$
When a PL/pgSQL function is called as a data change trigger, several special variables are created automatically in the top-level block. They are listed in the table below. The code above uses the NEW
and OLD
variables to access the new and old table row, respectively.
Name | Data type | Description |
---|---|---|
NEW |
record |
A new table row for |
OLD |
record |
An old table row for |
TG_NAME |
name |
A name of the fired trigger |
TG_WHEN |
text |
|
TG_LEVEL |
text |
|
TG_OP |
text |
An operation for which the trigger was fired: |
TG_RELID |
oid (references pg_class.oid) |
An object ID of the table that caused the trigger invocation |
TG_TABLE_NAME |
name |
A name of the table that caused the trigger invocation |
TG_TABLE_SCHEMA |
name |
The name of the schema that contains the table causing the trigger invocation |
TG_NARGS |
integer |
The number of arguments given to the trigger function in the |
TG_ARGV[] |
Data type array of text |
The arguments from the |
For more information, see Trigger functions.
Use the CREATE TRIGGER command to define a new trigger. To create or replace a trigger on a table, the user must have the TRIGGER
privilege on the table. The user must also have EXECUTE
privilege on the trigger function.
CREATE TRIGGER last_changes
BEFORE UPDATE
ON books
FOR EACH ROW
EXECUTE PROCEDURE save_changes();
The last_changes
trigger is created as row-level BEFORE UPDATE
trigger on the books
table that invokes the save_changes
trigger function. For more information on the CREATE TRIGGER
syntax, see the CREATE TRIGGER article.
Update the books
table to fire the trigger:
UPDATE books SET price = 52 WHERE ID = 3;
Check the contents of the books_log
table:
SELECT * from books_log;
The result:
id | book_id | book_name | price | change_date ----+---------+-----------+-------+---------------------------- 1 | 3 | 1984 | 20 | 2024-04-04 16:30:07.001503
Modify a trigger
Use the ALTER TRIGGER command to modify the trigger.
The following example renames the trigger:
ALTER TRIGGER last_changes ON books RENAME TO last_changes_new_name;
Disable a trigger
To temporarily disable or enable a trigger, utilize the ALTER TABLE command.
Disable a trigger:
ALTER TABLE books DISABLE TRIGGER last_changes_new_name;
Enable a trigger:
ALTER TABLE books ENABLE TRIGGER last_changes_new_name;
Delete a trigger
To remove a trigger, use the DROP TRIGGER command:
DROP TRIGGER last_changes_new_name ON books;
To execute this command, the current user must be the owner of the table for which the trigger is defined.
List triggers
To list all triggers in the current database, use the information_schema.triggers
system view:
SELECT event_object_table, trigger_name FROM information_schema.triggers;
The result:
event_object_table | trigger_name --------------------+-------------- books | last_changes
It is also possible to utilize the pg_trigger
system catalog to list triggers:
SELECT oid, tgrelid, tgparentid, tgname FROM pg_trigger;
The result:
oid | tgrelid | tgparentid | tgname --------+---------+------------+-------------- 191005 | 190751 | 0 | last_changes
Trigger behavior
This section describes some trigger behavior that you may find important.
The AFTER trigger
The execution of an AFTER
trigger can be deferred to the end of the transaction, rather than the end of the statement, if it was defined as a CONSTRAINT
trigger. In all cases, a trigger is executed as part of the same transaction as the statement that triggered it, so if the statement or the trigger causes an error, the effects of both will be rolled back.
Partitioned and parent tables
A statement that targets a parent table in an inheritance or partitioning hierarchy does not cause the statement-level triggers of affected child tables to be fired. Only the parent table’s statement-level triggers are fired. However, row-level triggers of any affected child tables will be fired.
If UPDATE
on a partitioned table causes a row to move to another partition, it will be performed as DELETE
from the original partition followed by INSERT
into a new partition. In this case, all row-level BEFORE UPDATE
and BEFORE DELETE
triggers are fired on the original partition. Then, all row-level BEFORE INSERT
triggers are fired on the destination partition. You should be aware of the possibility of unexpected results when all of these triggers process the row being moved. Row-level AFTER DELETE
and AFTER INSERT
triggers are applied, but AFTER UPDATE
triggers are not applied because the UPDATE
has been converted to DELETE
and INSERT
. The statement-level DELETE
or INSERT
triggers are not fired, even if rows are moved. Only UPDATE
triggers defined on the target table of the UPDATE
statement will be fired.
Return values of trigger functions
Functions invoked by statement-level triggers should return NULL
. Trigger functions called by row-level triggers can return a table row (a value of the HeapTuple
type) to the calling executor. A row-level BEFORE
trigger can return the following results:
-
NULL — to skip the operation for the current row. This instructs the executor to not perform an
INSERT
,UPDATE
, orDELETE
operation on the specific table row that invokes the trigger. -
A table row — for
INSERT
andUPDATE
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.
If you do not plan to use the options mentioned above with a row-level BEFORE
trigger, its trigger function must return the same row that is passed in — 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 base tables, or it should return the view row that was passed in (the new row for INSERT
and UPDATE
, or the old row for DELETE
). A non-null return value signals that the trigger performed the necessary data modifications in the view. This will increment the counter for the number of rows affected by the command. For INSERT
and UPDATE
operations only, the trigger function can modify a new row before returning it.
The return value is ignored for row-level AFTER
triggers, and so they can return NULL
.
Generated columns
The generated columns deserve special attention. Stored generated columns are computed after BEFORE
triggers and before AFTER
triggers. The generated value can be inspected in AFTER
triggers. In BEFORE
triggers, the old row (the OLD
variable) contains the old generated value, but the new row does not yet contain the new generated value. Changes of a generated column in a BEFORE
trigger are ignored and will be overwritten.
Trigger firing order
If more than one trigger is defined for the same event on the same relation, the triggers are fired in alphabetical order by trigger name. In the case of BEFORE
and INSTEAD OF
triggers, the row returned by each trigger becomes the input to the next trigger. If any BEFORE
or INSTEAD OF
trigger returns NULL
, the operation for this row stops, and subsequent triggers are not fired for this row.
Trigger WHEN condition
A trigger definition can also contain a boolean WHEN
condition that is checked to see whether the trigger should be fired. In row-level triggers, the WHEN
condition can examine the old and new values of row columns. Statement-level triggers can also include 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 executed, so using WHEN
is not significantly different from testing the same condition at the beginning of the trigger function. However, in an AFTER
trigger, the WHEN
condition is evaluated immediately after the row is updated, and it determines whether an event is queued to fire the trigger at the end of the statement. Therefore, when the WHEN
condition in an AFTER
trigger does not return true
, there is no need to either queue the event or re-fetch this row at the end of the statement. This can significantly speed up statements that modify a large number of rows with a trigger that only needs to fire on a few. INSTEAD OF
triggers do not support the WHEN
condition.
Cascading triggers
If a trigger function executes SQL commands, 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 can execute a command that inserts an additional row into the same table, causing the INSERT
trigger to be fired again. It is the programmer’s responsibility to avoid endless recursion in such cases.
Trigger arguments
When defining a trigger, you can specify arguments. The purpose of including arguments in a trigger definition is to allow different triggers with similar requirements to call the same function. 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 (for example, INSERT
or UPDATE
) and arguments that are listed in the CREATE TRIGGER
command. For row-level triggers, the input data also includes the new row for INSERT
and UPDATE
triggers, and the old row for UPDATE
and DELETE
triggers.
Visibility of data changes
If you execute SQL commands in the trigger function, these SQL commands see the data changes according to the following data visibility rules:
-
None of the changes made by a statement are visible to statement-level
BEFORE
triggers, but all modifications are visible to statement-levelAFTER
triggers. -
The data change (insertion, update, or deletion) causing the trigger to fire is not visible to SQL commands executed in a row-level
BEFORE
trigger, because it has not yet occurred. -
SQL commands executed in a row-level
BEFORE
trigger see the effects of data changes for rows previously processed in the same command. This requires caution, since an SQL command that processes on multiple rows can access the rows in any order. -
A row-level
INSTEAD OF
trigger can see the effects of data changes made by previousINSTEAD OF
trigger calls in the same command. -
When a row-level
AFTER
trigger is fired, all data changes made by the command are already complete, and are visible to the invoked trigger function.
If a trigger function is written in any of the standard procedural languages, the above statements are correct only if the function is declared as VOLATILE
. Functions that are declared as STABLE
or IMMUTABLE
will not see changes made by the calling command.