Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

postgres
postgresql

What are triggers in PostgreSQL?

Ravi

Overview

A PostgreSQL trigger is a function that is called automatically whenever a table-related event occurs. The INSERT, UPDATE, DELETE, and TRUNCATE keywords are all examples of events. First, the intended function to execute is defined and then it is attached to a trigger.

Types of triggers

There are two different types of triggers. Let’s look at them below:

Row level triggers

The row level type of trigger gets called (or fired) for every row that gets affected in the table/relation. For example, if an operation updates 5 rows of the table, then the row level trigger(s) associated with the update event are 5 times, once for each update operation.

Statement level triggers

The statement level type of trigger gets called only once for any given operation, regardless of how many rows it modifies. If an operation doesn’t update any row of the table, the associated statement level triggers still get called.

Firing point of triggers

A trigger can be configured to be fired in the following situations:

  • Before the operation is attempted on rows.
  • After the operation is completed.
  • Instead of the event/operation.

If the trigger is called before an event, it can skip the current row’s action or even modify the row that is being updated or inserted. If the trigger is called after the event, it has access to all of the modifications.

How to create a trigger

The syntax to create a 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 )
  • BEFORE | AFTER | INSTEAD OF: These options refer to the trigger firing point.
  • [ FOR [ EACH ] { ROW | STATEMENT }: These options refer to the type of trigger.

Example

CREATE TABLE Person (
    Name 	varchar(100) NOT NULL,
    Age 	int,
    Address varchar(100)
);

CREATE TABLE Person_Audit (
    Name 	varchar(100) NOT NULL,
    Age 	int,
    Address varchar(100),
    Postgres_User  varchar(100) NOT NULL,
    PersonInsertTime  varchar(20) NOT NULL
);

CREATE OR REPLACE FUNCTION person_insert_trigger_function()
RETURNS TRIGGER AS
$$
BEGIN

    INSERT INTO Person_Audit(Name, Age, Address, Postgres_User ,PersonInsertTime)
         VALUES(NEW.Name,NEW.Age,NEW.Address,current_user,current_date);

RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER person_insert_trigger
AFTER INSERT ON Person
FOR EACH ROW
EXECUTE PROCEDURE person_insert_trigger_function();


INSERT INTO Person 
    (Name, Age, Address) 
  VALUES
    ('gaby', 20, 'US'),
    ('fahim', 22, 'UK'),
    ('rachel',15, 'China');

select * from Person_Audit;

Explanation

  • Lines 1–5: We create the Person table with Name, Age and Address attributes.
  • Lines 7–14: We create the Person_Audit table with Name, Age, Address, Postgres_User, and PersonInsertTime attributes.
  • Lines 15–26: A user defined function called person_insert_trigger_function that is attached to the person_insert_trigger is defined. As and when a new record is inserted to Person table, an entry is made into Person_Audit table with the data inserted to Person, the current PostgreSQL user who inserted it and the date at which the record was inserted.
  • Lines 28–31: We define person_insert_trigger on the INSERT event on the Person table. This trigger executes the person_insert_trigger_function function for every record inserted into the table.
  • Lines 34–39: We insert a few records to the Person table.
  • Line 41: We print the Person_Audit table to check whether the trigger was fired or not.

RELATED TAGS

postgres
postgresql
RELATED COURSES

View all Courses

Keep Exploring