PostgreSQL Triggers

Whenever a specified database event is occurs, there are database callback functions, which are automatically invoked known as Triggers.

Triggers can be specified to fire when:
1. Before the operation is attempted on a row
2. After the operation has completed
3. Instead of the operation

A trigger that is marked for EACH ROW is called once for each row that the operation modifies.For example, a DELETE that affects 5 rows will cause any DELETE trigger on a target relations called 5 different times, once for each different row.If multiple triggers are defined for the same event then triggers are invoked in alphabetical order by name.

The three operations BEFORE, AFTER or INSTEAD determine when the trigger actions will be invoked and executed relative to the insertion, modification, or removal of the corresponding row.Triggers are automatically removed when the table associated with the trigger is dropped.

A CONSTRAINT option when specified creates a constraint trigger.The trigger is similar to a regular trigger except we can adjust the trigger firing time using SET CONSTRAINTS, an exception will be raised when CONSTRAINTS associated with the trigger is violated.

Syntax :

The basic syntax of creating a trigger is as follows

* event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. 
CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- Trigger logic goes here....
];
------------------------------------------------------------------------
*creating a trigger on an UPDATE operation on one or more specified columns of a table
CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
-- Trigger logic goes here....
];

Example :

The Example of creating a trigger is as follows:-
Suppose we want to keep an audit trial for every record being put in the company's table.

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

To keep the audit trail, we will create a new table called AUDIT where the log messages will be inserted

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

In this, ID is the AUDIT record ID and EMPID is the ID, which will come from the company table and keep the DATE timestamp when the record is created in the company table.

lfcdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
-----------------------------------------------------------------
\\Where auditlogfunc() is a PostgreSQL procedure and has the following definition −
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;

So let us create one record in COMPANY table as follows −

lfcdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Prateek', 24, 'Mysore', 17528.00 );
-----------------------------------------------------------------
\\Output −
id | name    | age | address      | salary
----+------+-----+--------------+--------
1 | prateek |  24 | Mysore       |  17528
------------------------------------------------------------------
\\Same time, one record will be created in AUDIT table.
emp_id |          entry_date
--------+-------------------------------
1 | 2019-05-05 15:49:59.968+05:30

---

Visit :

---

Discussion