PostgreSQL Triggers

PostgreSQL triggers are callback functions of the database, which will be automatically executed when the specified database event occurs/Call.

The following are some important points about PostgreSQL triggers:

• PostgreSQL triggers can be triggered in the following situations:

• Before the operation is executed (before checking the constraint and trying to insert, update, or delete).

• After the operation is executed (after checking the constraint and after insertion, update, or deletion is completed).

• Update operation (when inserting, updating, or deleting a view).

• The FOR EACH ROW attribute of the trigger is optional. If selected, it is called once per row when the operation is modified; otherwise, selecting FOR EACH STATEMENT, regardless of how many rows are modified, the trigger marked by each statement is executed once.

• The WHEN clause and trigger operation refer to NEW.column-name and OLD.column-name can access each element of each row when form insertion, deletion, or update is performed. Among column-name is the name of the column in the table associated with the trigger.

• If there is a WHEN clause, the PostgreSQL statement will only execute the row where the WHEN clause is true. If there is no WHEN clause, the PostgreSQL statement will execute on each row.

• The BEFORE or AFTER keyword determines when to execute the trigger action, deciding whether to execute the trigger action before or after the insertion, modification, or deletion of the related rows.

• The table to be modified must exist in the same database, as a table or view to which the trigger is attached, and must only use tablename, not database.tablename.

• When creating a constraint trigger, constraint options are specified. This is the same as a regular trigger, but this type of constraint can be used to adjust the trigger's timing of activation. When the constraint implemented by the constraint trigger is violated, it will throw 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
[
　--　Trigger logic...
];

Here, event_name can be an INSERT, DELETE, or UPDATE database operation on the mentioned table table_name. You can choose to specify FOR EACH ROW after the table name.

The syntax for creating a trigger on one or more specified columns of a table during an UPDATE operation is as follows:

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
　--　Trigger logic...
];

Online Example

Let's assume a scenario, we want to keep an audit trial for each record inserted into the newly created COMPANY table (if it already exists, it will be deleted and recreated):

w3codeboxdb=# 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 trial, we will create a new table named AUDIT. Whenever there is a new record item in the COMPANY table, a log message will be inserted into it:

w3codeboxdb=# 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 keep the timestamp when the record in COMPANY is created. So, let's create a trigger on the COMPANY table as follows:

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

auditlogfunc() is a PostgreSQL program, defined 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, we start inserting data into the COMPANY table:

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

At this time, a record was inserted into the COMPANY table:

At the same time, a record was also inserted into the AUDIT table because we created a trigger when inserting into the COMPANY table. Similarly, we can also create triggers for update and delete operations 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:

w3codeboxdb=# SELECT　*　FROM pg_trigger;

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

w3codeboxdb=# 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)

Delete trigger

The basic syntax for deleting a trigger is as follows:

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

The command to delete the trigger example_trigger on the table company in this article is:

drop trigger example_trigger on company;