PostgreSQL Triggers

PostgreSQL triggers are callback functions of the database, which are automatically executed/called when a specified database event occurs.

The following are some important points about PostgreSQL triggers:

PostgreSQL triggers can be triggered in the following situations:
- Before performing the operation (before checking constraints and attempting to insert, update, or delete).
- After performing the operation (after checking constraints and completing the insert, update, or delete).
- INSTEAD OF operations (when inserting, updating, or deleting on a view).
The FOR EACH ROW attribute of the trigger is optional. If selected, it is invoked once for each row modified by the operation; conversely, if FOR EACH STATEMENT is selected, the trigger executes once per statement, regardless of how many rows are modified.
The WHEN clause and trigger operations can access each row element when referencing NEW.column-name and OLD.column-name for insert, delete, or update operations. Here, column-name is the name of a column in the table with which the trigger is associated.
If a WHEN clause exists, the PostgreSQL statement is executed only for the row for which the WHEN clause is true; if there is no WHEN clause, the PostgreSQL statement is executed for every row.
The BEFORE or AFTER keyword determines when the trigger action is executed, deciding whether the trigger action is executed before or after the insertion, modification, or deletion of the associated row.
The table to be modified must exist in the same database as the table or view to which the trigger is attached, and only tablename must be used, not database.tablename.
Constraint options are specified when creating a constraint trigger. This is the same as a regular trigger, except that this constraint can be used to adjust the timing of the trigger firing. When a constraint implemented by a constraint trigger is violated, it will raise an exception.

Syntax

The basic syntax for creating a trigger is as follows:

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- 触发器逻辑....
];

Here, event_name can be a database operation such as INSERT, DELETE, and UPDATE on the mentioned table table_name. You may optionally specify FOR EACH ROW after the table name.

The following is the syntax for creating a trigger on one or more specified columns of a table for UPDATE operations:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- 触发器逻辑....
];

Examples

Let us assume a situation where we want to maintain an audit trail for every record inserted into the newly created COMPANY table (if it already exists, delete and recreate it):

exampledb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

To maintain the audit trail, we will create a new table named AUDIT. Whenever there is a new record entry in the COMPANY table, a log message will be inserted into it:

exampledb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Here, ID is the ID of the AUDIT record, EMP_ID is the ID from the COMPANY table, and DATE will hold the timestamp when the record in COMPANY was created. So, now let us create a trigger on the COMPANY table as follows:

exampledb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() is a PostgreSQL function whose definition is as follows:

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Now, let's start inserting data into the COMPANY table:

exampledb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

At this point, a record has been inserted into the COMPANY table:

At the same time, a record is also inserted into the AUDIT table, because we created a trigger when inserting into the COMPANY table. Similarly, we can also create triggers for updates and deletes as needed:

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

List Triggers

You can list all triggers in the current database from the pg_trigger table:

exampledb=# SELECT * FROM pg_trigger;

If you want to list triggers of a specific table, the syntax is as follows:

exampledb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

The result obtained is as follows:

 tgname
-----------------
 example_trigger
(1 row)

Drop Triggers

The basic syntax for dropping a trigger is as follows:

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

The command to drop the trigger example_trigger on the company table above is:

drop trigger example_trigger on company;

Other Extensions

PostgreSQL Advanced

PostgreSQL Triggers

Syntax

Examples

List Triggers

Drop Triggers