Triggers

Eugenia Kuzina

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:

  1. Create a trigger function.

  2. 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.

PL/pgSQL data change trigger function variables
Name Data type Description

NEW

record

A new table row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations

OLD

record

An old table row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations

TG_NAME

name

A name of the fired trigger

TG_WHEN

text

BEFORE, AFTER, or INSTEAD OF, depending on the trigger’s definition

TG_LEVEL

text

ROW or STATEMENT, depending on the trigger’s definition

TG_OP

text

An operation for which the trigger was fired: INSERT, UPDATE, DELETE, or TRUNCATE

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 CREATE TRIGGER statement

TG_ARGV[]

Data type array of text

The arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to TG_NARGS) return NULL

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, or DELETE operation on the specific table row that invokes the trigger.

  • A table row — for 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.

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-level AFTER 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 previous INSTEAD 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.

Found a mistake? Seleсt text and press Ctrl+Enter to report it