Introduction to the PostgreSQL AFTER DELETE trigger

In PostgreSQL, a trigger is a database object that is automatically activated in response to an event includingINSERT,UPDATE,DELETE, orTRUNCATE occurring on a table.

AnAFTER DELETE trigger is activated after one or more rows are deleted from a table.

AnAFTER DELETE trigger can be particularly useful in some scenarios such as logging deleted data, updating data in related tables, or enforcing complex business rules.

In anAFTER DELETE trigger, theOLD variable, which holds the value of the row being deleted, is available. To access a column value of the deleted row, you can use the syntaxOLD.column_name.

Notice that you cannot change the column values (OLD.column_name) because they are read-only.

To create anAFTER DELETE trigger, you follow these steps:

First,define a trigger function that will execute after aDELETE operation:

CREATE OR REPLACE FUNCTION trigger_function_name()RETURNS TRIGGER AS$$BEGIN    -- This logic will be executed after the DELETE operation    -- To access the values of a column of the deleted row:    -- OLD.column_name    RETURN OLD;END;$$LANGUAGE plpgsql;

Second, create a trigger and associate the trigger function with it:

CREATE TRIGGER trigger_nameAFTER DELETE ON table_nameFOR EACHROWEXECUTE FUNCTION trigger_function_name();

PostgreSQL AFTER DELETE trigger example

We’ll use anAFTER DELETE trigger to archive a deleted row in a separate table.

First,create a table calledemployees to store the employee data:

CREATE TABLE employees (    idSERIAL PRIMARY KEY,    name VARCHAR(100)NOT NULL,    salaryNUMERIC(10,2)NOT NULL);

Second,insert two rows into theemployees table:

INSERT INTO employees(name, salary)VALUES   ('John Doe',90000),   ('Jane Doe',80000)RETURNING*;

Output:

id |   name   |  salary----+----------+----------  1 | John Doe | 90000.00  2 | Jane Doe | 80000.00(2 rows)

Third, create another table namedemployee_archives for archiving deleted employees:

CREATE TABLE employee_archives(    id SERIAL PRIMARY KEY,    name VARCHAR(100)NOT NULL,    salary NUMERIC(10, 2)NOT NULL,    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Fourth, define a function thatinserts a deleted employee into theemployee_archives table:

CREATE OR REPLACE FUNCTION archive_deleted_employee()RETURNS TRIGGERAS$$BEGIN    INSERT INTO employee_archives(id,name, salary)    VALUES (OLD.id, OLD.name, OLD.salary);    RETURN OLD;END;$$LANGUAGE plpgsql;

Fifth, create anAFTER DELETE trigger that executes thearchive_deleted_employee() function when a row is deleted from theemployees table:

CREATE TRIGGER after_delete_employee_triggerAFTER DELETE ON employeesFOR EACHROWEXECUTE FUNCTION archive_deleted_employee();

Sixth,delete a row from theemployees table:

DELETE FROM employeesWHERE id= 1RETURNING*;

Output:

id |   name   |  salary----+----------+----------  1 | John Doe | 90000.00(1 row)

TheAFTER INSERT trigger will be activated that calls thearchive_deleted_employee() function to insert the deleted row into theemployee_archives table.

Seventh, retrieve data from theemployee_archives table:

SELECT * FROM employee_archives;

Output:

id |   name   |  salary  |        deleted_at----+----------+----------+---------------------------  1 | John Doe | 90000.00 | 2024-03-28 16:30:37.89788(1 row)

The output indicates that theAFTER DELETE trigger has successfully archived the deleted row into theemployee_archives table.

Summary

• Use aBEFORE DELETE trigger to automatically call a function before a row is deleted from a table.

Last updated onMarch 28, 2024

On this page

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