Introduction to the PostgreSQL AFTER INSERT trigger

In PostgreSQL, a trigger is a database object associated with a table, which is automatically fired in response to anINSERT,UPDATE,DELETE, orTRUNCATE event.

AnAFTER INSERT trigger is a trigger that is fired after anINSERT event occurs on a table.

TheAFTER INSERT trigger can access the newly inserted data using theNEW record variable. ThisNEW variable allows you to access the values of columns in the inserted row:

NEW.column_name

Typically, you useAFTER INSERT triggers for logging changes, updating related tables, or sending notifications based on the inserted data.

To create anAFTERINSERT trigger, you follow these steps:

First,define a function that will execute when the trigger is activated:

CREATE OR REPLACE FUNCTION trigger_function()   RETURNS TRIGGER   LANGUAGE PLPGSQLAS$$BEGIN   -- trigger logic   -- ...   RETURN NEW;END;$$

TheRETURN NEW statement indicates that the function returns the modified row, which is theNEW row.

Second, create anAFTERINSERT trigger and bind the function to it:

CREATE TRIGGER trigger_nameAFTER INSERTON table_nameFOR EACH {ROW |STATEMENT}EXECUTE FUNCTION trigger_function();

PostgreSQL AFTER INSERT trigger example

First,create a new table calledmembers to store the member data:

CREATE TABLE members (    idSERIAL PRIMARY KEY,    name VARCHAR(100)NOT NULL,    emailVARCHAR(100)UNIQUE);

Themembers table has three columnsid,name, andemail. Theid column is aserial andprimary key column. Theemail column has a unique constraint to ensure the uniqueness of emails.

Second, create another table calledmemberships to store the memberships of the members:

CREATE TABLE memberships (    idSERIAL PRIMARY KEY,    member_idINT NOT NULL REFERENCES members(id),    membership_typeVARCHAR(50)NOT NULL DEFAULT 'free');

The memberships table has three columns id, member_id, and membership_type:

• Theid is a serial and primary key column.
• Themember_id references the id column of themembers table. It is a foreign key column.
• Themembership_type column has a default value of "free".

Third, define a trigger function that inserts a default free membership for every member:

CREATE OR REPLACE FUNCTION create_membership_after_insert()RETURNS TRIGGERAS $$BEGIN    INSERT INTO memberships (member_id)    VALUES (NEW.id);    RETURN NEW;END;$$LANGUAGE plpgsql;

Fourth, define anAFTERINSERT trigger on themembers table, specifying that it should execute thecreate_membership_after_insert() function for each row inserted:

CREATE TRIGGER after_insert_member_triggerAFTER INSERT ON membersFOR EACHROWEXECUTE FUNCTION create_membership_after_insert();

Fifth,insert a new row into themembers table:

INSERT INTO members(name, email)VALUES('John Doe','john.doe@example.com')RETURNING*;

Output:

id |   name   |       email----+----------+--------------------  1 | John Doe | john.doe@example.com(1 row)

Sixth, retrieve data from thememberships table:

SELECT * FROM memberships;

Output:

id | member_id | membership_type----+-----------+-----------------  1 |         1 | free(1 row)

Summary

• Use anAFTERINSERT trigger to call a function automatically after anINSERT operation successfully on the associated table.

Last updated onMarch 28, 2024

On this page

• Introduction to the PostgreSQL AFTER INSERT trigger
• PostgreSQL AFTER INSERT trigger example
• Summary