Everything you need to know about PostgreSQL triggers

Linux x86-64 (RHEL 8)

Ranjeet Dhumal Technical Support Manager

SUMMARY: This article discussed triggers in PostgreSQL. It describes the different types of triggers and provides examples for each:

1. Types of Triggers

2. Creating a Trigger

                 1. INSERT event trigger

                2. UPDATE event trigger

                3. DELETE event trigger

4. Dropping a Trigger

5. Uses of Triggers

6. Important Points to Remember

 

A “trigger” is defined as any event that sets a course of action in a motion.

In PostgreSQL, if you want to take action on specific database events, such as INSERT, UPDATE, DELETE, or TRUNCATE, then trigger functionality can be useful as it will invoke the required function on defined events.

The trigger will be associated with the specified table, view, or foreign table and will execute the specified function when certain operations are performed on that table. Depending on the requirement we can create trigger BEFORE, AFTER or INSTEAD of the events/operation.

 

Types of Triggers

1.    Row Level Trigger: If the trigger is marked FOR EACH ROW then the trigger function will be called for each row that is getting modified by the event.

For example: If we UPDATE 100 rows in the table, the UPDATE trigger function will be called 100 times, once for each updated row.

2.    Statement Level Trigger: The FOR EACH STATEMENT option will call the trigger function only once for each statement, regardless of the number of the rows getting modified.

 

Creating a Trigger 

The SQL command CREATE TRIGGER creates a trigger on the specified object.

The syntax of the CREATE TRIGGER  is as follows:

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 )

 

where event can be one of:

 

INSERT

UPDATE [ OF column_name [, ... ] ]

DELETE

TRUNCATE

 

For a more detailed description and available options, check the PostgreSQL documentation at https://www.postgresql.org/docs/12/sql-createtrigger.html.

 

1.    INSERT event Trigger

The INSERT event trigger gets called when a new record is added to a table using the INSERT statement.

Example 

Let’s take a look at an example of creating a new trigger. In this example, we will create a new table named “Employee” as follows:

CREATE TABLE "Employee"

(

"EmployeeId" INT NOT NULL,

"LastName" VARCHAR(20) NOT NULL,

"FirstName" VARCHAR(20) NOT NULL,

"Title" VARCHAR(30),

"ReportsTo" INT,

"BirthDate" TIMESTAMP,

"HireDate" TIMESTAMP,

"Address" VARCHAR(70),

"City" VARCHAR(40),

"State" VARCHAR(40),

"Country" VARCHAR(40),

"PostalCode" VARCHAR(10),

"Phone" VARCHAR(24),

"Fax" VARCHAR(24),

"Email" VARCHAR(60),

CONSTRAINT "PK_Employee" PRIMARY KEY  ("EmployeeId")

);



CREATE TABLE "Employee_Audit"

(

"EmployeeId" INT NOT NULL,

"LastName" VARCHAR(20) NOT NULL,

"FirstName" VARCHAR(20) NOT NULL,

"UserName" VARCHAR(20) NOT NULL,

"EmpAdditionTime" VARCHAR(20) NOT NULL,

);

 

We are going to create a trigger that can add an entry in the “Employee_Audit” table if a new employee record gets inserted into the “Employee” table. We are going to log the username details, which is going to add the record in the “Employee” table.

Add a trigger function and CREATE TRIGGER command:

CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc()

  RETURNS trigger AS

$$

BEGIN



    INSERT INTO "Employee_Audit" ( "EmployeeId", "LastName", "FirstName","UserName" ,"EmpAdditionTime")

         VALUES(NEW."EmployeeId",NEW."LastName",NEW."FirstName",current_user,current_date);



RETURN NEW;

END;

$$

LANGUAGE 'plpgsql';



CREATE TRIGGER employee_insert_trigger

  AFTER INSERT

  ON "Employee"

  FOR EACH ROW

  EXECUTE PROCEDURE employee_insert_trigger_fnc();

 

Once we create the above INSERT trigger on the table, it will add one new entry to the “Employee_Audit”  table with these details:

trigger_demo=# INSERT INTO "Employee" VALUES(10,' Adams','Andrew','Manager',1,'1962-02-18 00:00:00','2010-08-14 00:00:00','11120 Jasper Ave NW','Edmonton','AB','Canada','T5K 2N1','+1 780 428-9482','+1 780 428-3457','abc@gmail.com');

INSERT 0 1



trigger_demo=# select * from "Employee" where "EmployeeId" =10;

-[ RECORD 1 ]-------------------

EmployeeId | 10

LastName   | Adams

FirstName  | Andrew

Title  | Manager

ReportsTo  | 1

BirthDate  | 1962-02-18 00:00:00

HireDate   | 2010-08-14 00:00:00

Address | 11120 Jasper Ave NW

City   | Edmonton

State  | AB

Country | Canada

PostalCode | T5K 2N1

Phone  | +1 780 428-9482

Fax    | +1 780 428-3457

Email  | abc@gmail.com

trigger_demo=#



trigger_demo=# select * from "Employee_Audit" ;

-[ RECORD 1 ]---+-----------

EmployeeId   | 10

LastName    | Adams

FirstName   | Andrew

UserName    | postgres

EmpAdditionTime | 2019-11-10

 

2.    UPDATE event Trigger 

The UPDATE event trigger gets called at the time of UPDATE statement execution.

Example

CREATE TRIGGER verify_user_for_update

 BEFORE UPDATE

 ON "Employee"

 FOR EACH ROW

EXECUTE PROCEDURE employee_verify_user_priv();

 

3.    DELETE event Trigger 

This the DELETE event trigger, which can be added on transactions that DELETE the records.

Example

CREATE TRIGGER employee_delete_trigger

  AFTER DELETE

  ON "Employee"

  FOR EACH ROW

 EXECUTE PROCEDURE aft_delete();

 

Dropping a Trigger 

DROP TRIGGER is used to remove a trigger. The syntax is very simple.

drop trigger employee_insert_trigger on "Employee" ;

 

Uses Of Triggers

1.    Auditing: You can use triggers to track the table transactions by logging the event details.

2.    Forcing Check Constraint: You can create a trigger by which you can check the constraints before applying the transaction to the table.

3.   Automatic Population: By using triggers you can also auto populate tables fields by new transactions records manipulation.

Important Points To Remember

1.    To create a trigger on a table, the user must have the TRIGGER privilege on the table and EXECUTE privilege on the trigger function.

2.    You can check system catalogue “pg_trigger” for the existing trigger information in the database.

3.    If you create multiple triggers on the same object for the same event, those triggers will be fired in alphabetical order by name.

 

Reference Links

https://www.postgresql.org/docs/12/sql-createtrigger.html

http://www.postgresqltutorial.com/introduction-postgresql-trigger/

 

 

Ranjeet DhumalTechnical Support Manager