SoFunction
Updated on 2025-04-04

Detailed explanation of the creation, use and deletion example of PostgreSql trigger

1. Overview

A trigger is a special stored procedure that is automatically triggered by events. The triggering event can be to perform INSERT, UPDATE, DELETE and other operations on a table. Triggers are often used to strengthen data integrity constraints and business rules constraints, etc.

2. Create a trigger

2.1 Syntax

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

HereeventIt can be one of the following：

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

Parameter description:

name: The name of the trigger. This must be different from any other triggers on the same table. The name cannot be schema-qualified, and the trigger inherits the schema of the table it is located in.
BEFORE|AFTER|INSTEAD OF: Determines whether the function will be called before and after the event or will replace the event.
event: One of INSERT, UPDATE, DELETE, or TRUNCATE, which specifies the event to be raised to the trigger. Multiple events can be specified with OR, except when a relationship is required to be passed.

For UPDATE events, you can specify a list of columns using the following syntax:

 UPDATE OF column_name1 [, column_name2 ... ]

table_name: The name of the table, view, or external table to which the trigger is to be used (probably schema-qualified).
referenced_table_name: The name of another table referenced by the constraint (probably schema-qualified). This option is used for foreign key constraints and is not recommended for general purposes. This can only be specified for constraint triggers.
NOT DEFERRABLE|[ DEFERRABLE ][ INITIALLY IMMEDIATE|INITIALLY DEFERRED]: The default timing of this trigger. This can only be specified for constraint triggers.
REFERENCING: This keyword immediately before the declaration of one or two relationship names, which provide access to the passing relationship of the trigger statement.
OLD TABLE|NEW TABLE: This clause indicates whether the next relationship name is used for the pre-image transfer relationship or the post-image transfer relationship.
transition_relation_name: The (unqualified) name to use in this trigger.
FOR EACH ROW|FOR EACH STATEMENT: This specifies whether the trigger function should be raised once for each row affected by the trigger event, or only for each SQL statement. If none are specified, FOR EACH STATEMENT will be the default value. Constraint triggers can only be specified as FOR EACH ROW.
condition: A Boolean expression that determines whether the trigger function will be actually executed. If WHEN is specified, the function will be called only if condition returns true. In the FOR EACH ROW trigger, the WHEN condition can be written to OLD.column_name or NEW.column_name respectively to reference the old and new row values ​​of the column. Of course, the INSERT trigger cannot reference OLD and the DELETE trigger cannot reference NEW. The INSTEAD OF trigger does not support the WHEN condition. Currently, the WHEN expression cannot contain subqueries.
FUNCTION_NAME: A user-provided function that is declared as having no arguments and returns the type trigger, which is executed when the trigger is raised. In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must be a function rather than a procedure in any case. Here, the use of the keyword PROCEDURE is for historical reasons and has been abandoned.
arguments: An optional comma-separated parameter list that is provided to the function when the trigger is executed. Parameters are string constants. Simple names and numeric constants can also be written here, but they will all be converted into strings. Please check the description of the implementation language of the trigger function to find out how these parameters are accessed inside the function, which may be different from normal function parameters.

2.2 Example

You need to create an execution function for the trigger first, and the type returned by this function is the trigger type. Then create a trigger. If there is a student table (student table) and a student's test score table (score table), you can use a trigger to delete the student's score while deleting the student's records.

--Create a student table
CREATE TABLE student(
student_no int primary key,
student_name varchar(40),
age int
);

--Create a grade sheet
CREATE TABLE score (
student_no int,
chinese_score int,
math_score int,
test_date date
);

--Create a trigger execution function
CREATE OR REPLACE FUNCTION student_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM score WHERE student_no = OLD.student_no;
RETURN OLD;
END;
$$
LANGUAGE plpgsql;

--Create a trigger
CREATE TRIGGER delete_student_trigger
AFTER DELETE ON student
FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger
();

--Insert test data
INSERT INTO student VALUES(1, 'Zhang San', 14);
INSERT INTO student VALUES(2, 'Li Si', 13);
INSERT INTO student VALUES(3, 'Wang Er', 15);
INSERT INTO score VALUES(1, 85, 75, date '2013-05-23');
INSERT INTO score VALUES(1, 80, 73, date '2013-09-18');
INSERT INTO score VALUES(2, 68, 83, date '2013-05-23');
INSERT INTO score VALUES(2, 73, 85, date '2013-09-18');
INSERT INTO score VALUES(3, 72, 79, date '2013-05-23');
INSERT INTO score VALUES(3, 78, 82, date '2013-05-23');

--Delete data and test to verify whether the trigger is effective
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | Zhang San         |  14
          2 | Li Si         |  13
          3 | Wang Er         |  15
(3 rows)

postgres=# select * from score;
 student_no | chinese_score | math_score | test_date
------------+---------------+------------+------------
          1 |            85 |         75 | 2013-05-23
          1 |            80 |         73 | 2013-09-18
          2 |            68 |         83 | 2013-05-23
          2 |            73 |         85 | 2013-09-18
          3 |            72 |         79 | 2013-05-23
          3 |            78 |         82 | 2013-05-23
(6 rows)

postgres=# DELETE FROM student where student_no = 3;
DELETE 1
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | Zhang San         |  14
          2 | Li Si         |  13
(2 rows)

postgres=# select * from score;
 student_no | chinese_score | math_score | test_date
------------+---------------+------------+------------
          1 |            85 |         75 | 2013-05-23
          1 |            80 |         73 | 2013-09-18
          2 |            68 |         83 | 2013-05-23
          2 |            73 |         85 | 2013-09-18
(4 rows)

3. Statement-level triggers and row-level triggers

A trigger marked FOR EACH ROW is called a row-level trigger and is called once for each row modified by the operation. For example, a DELETE affecting 10 rows will cause any ON DELETE trigger on the target relationship to be called 10 times independently. In contrast, a trigger marked FOR EACH STATEMENT is called a statement-level trigger and will only be performed once for any given operation, regardless of how many rows the operation modifies (in particular, an operation that modifies zero rows will still cause any available FOR EACH STATEMENT trigger to be executed).

3.1 Statement-level trigger example

Record the update status of the student table.

--Create a logging table
CREATE TABLE log_student(
update_time timestamp, --Operation time
db_user varchar(40), --The database username of the operation
opr_type varchar(6) --Operation Type：insert、delete、update
);

--Create a trigger function（TG_OP It is a special variable in the trigger function，represent DML Operation Type）
CREATE FUNCTION log_student_trigger ()
RETURNS trigger AS
$$
BEGIN
INSERT INTO log_student values(now(), user, TG_OP);
RETURN NULL;
END;
$$
LANGUAGE "plpgsql";

--Create statement-level triggers
CREATE TRIGGER log_student_trigger
AFTER INSERT OR DELETE OR UPDATE ON student
FOR STATEMENT EXECUTE PROCEDURE log_student_trigger ();

--Insert data，Verify that the trigger is effective
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
(0 rows)

postgres=# select * from log_student;
 update_time | db_user | opr_type
-------------+---------+----------
(0 rows)

postgres=# INSERT INTO student VALUES(1, 'Zhang San', 14), (2, 'Li Si', 14);INSERT 0 2
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | Zhang San         |  14
          2 | Li Si         |  14
(2 rows)

postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 09:55:06.990726 | postgres | INSERT
(1 row)

postgres=# UPDATE student SET age = 15;
UPDATE 2
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | Zhang San         |  15
          2 | Li Si         |  15
(2 rows)

postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 09:55:06.990726 | postgres | INSERT
 2023-07-11 09:55:22.535921 | postgres | UPDATE
(2 rows)

postgres=# UPDATE student SET age = 16 WHERE student_no = 3;
UPDATE 0
postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 09:55:06.990726 | postgres | INSERT
 2023-07-11 09:55:22.535921 | postgres | UPDATE
 2023-07-11 09:56:39.129029 | postgres | UPDATE
(3 rows)

In the above example, you can see that the student table inserts and updates two rows of data. Log_student records one data of the corresponding operation. An update statement is executed, but the data is not updated, and a log log operation will also be triggered. It means that the statement trigger is triggered according to the statement, regardless of how many lines of data the statement actually operates.

3.2 Row-level trigger example

Record the update status of the student table.

--Create a logging table
CREATE TABLE log_student(
update_time timestamp, --Operation time
db_user varchar(40), --The database username of the operation
opr_type varchar(6) --Operation Type：insert、delete、update
);

--Create a trigger function（TG_OP It is a special variable in the trigger function，represent DML Operation Type）
CREATE FUNCTION log_student_trigger ()
RETURNS trigger AS
$$
BEGIN
INSERT INTO log_student values(now(), user, TG_OP);
RETURN NULL;
END;
$$
LANGUAGE "plpgsql";

--Create row-level triggers
CREATE TRIGGER log_student_trigger2
AFTER INSERT OR DELETE OR UPDATE ON student
FOR ROW EXECUTE PROCEDURE log_student_trigger ();

--Insert data，Verify that the trigger is effective
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
(0 rows)

postgres=# select * from log_student;
 update_time | db_user | opr_type
-------------+---------+----------
(0 rows)

postgres=# INSERT INTO student VALUES(1, 'Zhang San', 14), (2, 'Li Si', 14);INSERT 0 2
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | Zhang San         |  14
          2 | Li Si         |  14
(2 rows)

postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:08.608556 | postgres | INSERT
(2 rows)

postgres=# UPDATE student SET age = 15;
UPDATE 2
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          1 | Zhang San         |  15
          2 | Li Si         |  15
(2 rows)

postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:22.382645 | postgres | UPDATE
 2023-07-11 10:00:22.382645 | postgres | UPDATE
(4 rows)

postgres=# UPDATE student SET age = 16 WHERE student_no = 3;
UPDATE 0
postgres=# select * from log_student;
        update_time         | db_user  | opr_type
----------------------------+----------+----------
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:08.608556 | postgres | INSERT
 2023-07-11 10:00:22.382645 | postgres | UPDATE
 2023-07-11 10:00:22.382645 | postgres | UPDATE
(4 rows)

In the above example, we can see that the student table inserts and updates two rows of data, and both log_student records the corresponding number of data. When the update operation does not update the actual row, the log log will not be triggered. This indicates that the row-level trigger is triggered according to the number of rows affected.

4. BEFORE trigger and AFTER trigger

Generally speaking, statement-level BEFORE triggers are triggered before the statement starts doing anything, while statement-level AFTER triggers are triggered only at the end of the statement. A row-level BEFORE trigger is fired before an operation is performed on a specific row, while a row-level AFTER trigger is fired only at the end of a statement, but it will be fired before any statement-level AFTER trigger.

4.1 BEFORE trigger example

The BEFORE trigger can directly modify the NEW value to change the actual updated value. The specific example is as follows:

--Create a trigger function
CREATE FUNCTION student_use_new_name_tirgger ()
RETURNS trigger AS '
BEGIN
NEW.student_name = NEW.student_name||NEW.student_no;
RETURN NEW;
END;'
LANGUAGE "plpgsql";

--Create a trigger
CREATE TRIGGER user_new_name_student_trigger
BEFORE INSERT OR UPDATE ON student
FOR EACH ROW EXECUTE PROCEDURE
student_use_new_name_tirgger ();

--Insert data verification triggers take effect
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
(0 rows)

postgres=# INSERT INTO student values(3,'Wang Er', 15);INSERT 0 1
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          3 | Wang Er3        |  15
(1 row)

4.2 AFTER Trigger Example

If you use the AFTER trigger, it makes no sense to modify the NEW, as shown below:

postgres=# DROP TRIGGER user_new_name_student_trigger ON student;
DROP TRIGGER
postgres=# delete from student;
DELETE 1
postgres=# CREATE TRIGGER user_new_name_student_trigger
postgres-# AFTER INSERT OR UPDATE ON student
postgres-# FOR EACH ROW EXECUTE PROCEDURE
postgres-# student_use_new_name_tirgger ();
CREATE TRIGGER
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
(0 rows)

postgres=# INSERT INTO student values(3,'Wang Er', 15);INSERT 0 1
postgres=# select * from student;
 student_no | student_name | age
------------+--------------+-----
          3 | Wang Er         |  15
(1 row)

5. Delete the trigger

5.1 Syntax

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

Parameter description：

  • IF EXISTS: If the trigger does not exist, do not throw an error, but send a prompt.
  • name: The name of the trigger to be removed.
  • table_name: The name of the table that defines the trigger (can be schema-qualified).
  • CASCADE: Automatically delete objects that depend on this trigger, and then delete all objects that depend on those objects (see Section 5.14).
  • RESTRICT: If any object depends on the trigger, it is refused to be deleted. This is the default value.

5.2 Example

DROP TRIGGER user_new_name_student_trigger ON student;

When a trigger is deleted, the trigger's function will not be deleted. However, when a table is deleted, the triggers on the table are also deleted.

6. Trigger behavior

6.1 Trigger usage scenarios

when event Row level Statement level
BEFORE INSERT/UPDATE/DELETE Tables and external tables Tables, views, and external tables
BEFORE TRUNCATE NULL surface
AFTER INSERT/UPDATE/DELETE Tables and external tables Tables, views, and external tables
AFTER TRUNCATE NULL surface
INSTEAD OF INSERT/UPDATE/DELETE view NULL
INSTEAD OF TRUNCATE NULL NULL
  • INSTEAD OF trigger, only for row-level triggers to insert, update or delete views.
  • When BEFORE and AFTER triggers act on views, they are only used when statement-level triggers insert, update or delete views.
  • BEFORE and AFTER triggers, when TRUNCATE is triggered, it is only used in the case where statement-level triggers are emptying the table.

6.2 Trigger function returns value

  • Statement-level triggers should always return NULL, that is, "ETURN NULL" must be explicitly written in the trigger function, which will result in an error if not written.
  • BEFORE and INSTEAD OF row-level triggers, returning NULL, means that operations on the current row are ignored. Returns a non-NULL row. For INSERT and UPDATE operations, the returned row will become the inserted row or the row to be updated.
  • AFTER row-level trigger whose return value is ignored.
  • If there are multiple triggers on the same event, they will be triggered in the order of trigger names. BEFORE and INSTEAD OF row-level triggers, the row returned by each trigger (which may have been modified) will become the input to the next trigger. If the content returned by the BEFORE and INSTEAD OF row-level triggers is empty, then other row-level triggers on that row will not be triggered either.

7. Special variables in trigger function

When a PL/pgSQL function is called as a trigger function, the system will automatically create several special variables in the top-level declaration segment, such as NEW, OLD, TG_OP variables, etc. in the previous example. The variables that can be used are as follows:

  • NEW: This variable stores new data rows in the row-level trigger trigger triggered by the INSERT/UPDATE operation, and the data type is "RECORD". This variable is not allocated in a statement-level trigger, and it is not also allocated in a row-level trigger trigger triggered by a DELETE operation.
  • OLD: This variable stores the original data rows in the row-level trigger trigger triggered by the UPDATE/DELETE operation, and the data type is "RECORD". This variable is not allocated in a statement-level trigger, and it is also not allocated in a row-level trigger trigger triggered by the INSERT operation.
  • TG_NAME: The data type is type name, and this variable contains the trigger name that actually triggers.
  • TG_WHEN: The content is "BEFORE" or "AFTER" string to specify whether it is a BEFORE trigger or an AFTER trigger.
  • TG_LEVEL: The content is "ROW" or "STATEMENT" string is used to specify whether it is a statement-level trigger or a row-level trigger.TG_OP: A string with the content of "INSERT", "UPDATE", "DELETE", "TRUNCATE", used to specify the type of the DML statement.
  • TG_RELID: The OID of the table where the trigger is located.
  • TG_RELNAME: The name of the table where the trigger is located. This variable is about to be abandoned. It is recommended to use the TG_TABLE_NAME variable to replace this variable.
  • TG_TABLE_NAME: The name of the table where the trigger is located.
  • TG_TABLE_SCHEMA: The pattern of the table where the trigger is located.
  • TG_NARGS: The number of parameters assigned to the trigger process in the CREATE TRIGGER statement.
  • TG_ARGV[]: is an array of type text; is a parameter in the CREATE TRIGGER statement.

8. Event trigger

8.1 Event Trigger Overview

PostgreSQL has supported a trigger called "Event Trigger" since version 9.3. This trigger is mainly used to make up for the shortcomings of PostgreSQL's previous versions that did not support DDL triggers. Since event triggers involve greater permissions, such as prohibiting DDL operations, only superusers can create and modify event triggers. Currently, event triggers support the following 3 types of DDL events.

  • ddl_command_start: DDL is triggered before it starts execution.
  • ddl_command_end: A DDL execution is triggered after completion.
  • sql_drop: Triggered before deleting the database object.

8.2 Create event triggers

1) Syntax

CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

Parameter description：

name: The name of the trigger. This name must be unique in this database.
event: The event name that triggers the given function call.
filter_variable: The name of the variable used to filter events. This can be used to restrict the trigger to raise only for the part of the situation it supports. The only filter_variable currently supported is TAG.
filter_value: A list of values ​​associated with the filter_variable for which the trigger is to be raised. For TAG, this represents a list of command tags (e.g. ‘DROP FUNCTION’).
function_name: A user-provided function that is declared as having no arguments and returns type event_trigger. In the syntax of CREATE EVENT TRIGGER, the keywords CREATE EVENT TRIGGER and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.

2) Example

Before creating an event trigger, you must first create a trigger function. The return type of the event trigger function is event_trigger. Note that it is different from the return type trigger of the ordinary trigger function. An example of prohibiting execution of any DDL is as follows:

CREATE OR REPLACE FUNCTION abort_any_command()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;

CREATE EVENT TRIGGER abort_ddl ON ddl_command_start
   EXECUTE FUNCTION abort_any_command();

8.3 Modify event triggers

1) Syntax

ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER EVENT TRIGGER name RENAME TO new_name

Parameter description：

name: The name of the existing trigger to be modified.
new_owner: The username of the new owner of the event trigger.
new_name: The new name of the event trigger.
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER: These forms configure the trigger of event triggers. A disabled trigger is still known to the system, but it will not be executed when the period trigger event occurs.

2) Example

--Disable event triggers
ALTER EVENT TRIGGER abort_ddl DISABLE;

8.4 Delete event trigger

1) Syntax

DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]

Parameter description：

IF EXISTS: If the event trigger does not exist, do not throw an error, but send a prompt.
name: The name of the event trigger to be removed.
CASCADE: Automatically delete objects that depend on this trigger, and then delete all objects that depend on those objects.
RESTRICT: If any object depends on the trigger, it is refused to be deleted. This is the default value.

2) Example

--Delete event trigger
DROP EVENT TRIGGER abort_ddl;

Summarize

This is the article about the creation, use and deletion of PostgreSql triggers. For more related PostgreSql trigger content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!