PostgreSQL Triggers

PostgreSQL Triggers are a set of actions or database callback functions that run automatically when a specified database event (i.e. INSERT, UPDATE, DELETE or TRUNCATE statement) is performed on a specified table. Triggers are used to validate input data, enforce business rules, keeping audit trail etc.

Important points about Triggers

PostgreSQL are performed/ invoked in the following cases:

Before the operation is attempted (before constraints are checked and the INSERT, UPDATE or DELETE is attempted).

Or, after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed).

Or, Instead of the operation (in the case of inserts, updates or deletes on a view)

A trigger marked FOR EACH ROWS is called once for every row that the operation modifies. On the other hand, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies.
You can define multiple triggers of the same kind for the same event but the condition is they will be fired in alphabetical order by name.
Triggers are automatically dropped when the table that they are associated with is dropped.

PostgreSQL Create Trigger

The CREATE TRIGGER statement is used to create a new trigger in a PostgreSQL table. It is activated when a particular event (i.e. INSERT, UPDATE, and DELETE) occurs for the table.

Syntax:

 CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

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

Let's see the syntax of creating a trigger on an INSERT operation.

CREATE  TRIGGER trigger_name AFTER INSERT ON column_name
ON table_name
[
 -- Trigger logic goes here....
];

Let's take an example to demonstrate PostgreSQL create trigger AFTER INSERT statement.

See this example:

In the following example, we keep audit trial for every record being inserted in COMPANY table.

Create a table named COMPANY, by using the following query:

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

To keep audit trial, we will create a new table called AUDIT where log messages will be inserted whenever there is an entry in COMPANY table for a new record.

Create another table Audit, by using the following query:

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

Before creating a trigger on company table, first create a function/ procedure named auditlogfunc().

Execute the following query:

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 create a trigger on COMPANY table by using the following query:

Execute the following query:

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

Insert some values in the COMPANY table.

At the same time, two records will be created in AUDIT table. These records are the result of trigger, which we have created on AFTER INSERT on COMPANY table.

Usage of PostgreSQL Triggers

PostgreSQL triggers can be used for following purposes:

Validate input data.
Enforce business rules.
Generate a unique value for a newly inserted row on a different file.
Write to other files for audit trail purposes.
Query from other files for cross-referencing purposes.
Access system functions.
Replicate data to different files to achieve data consistency.

Advantages of using triggers

It accelerates the development speed of applications. Because the database stores triggers, you do not have to code the trigger actions into each database application.
Global enforcement of business rules. Define a trigger once and then reuse it for any application that uses the database.
Easier maintenance. If a business policy changes, you need to change only the corresponding trigger program instead of each application program.
Improve performance in client/server environment. All rules run in the server before the result returns.

Next TopicPostgreSQL Alias

← prev next →