> This page location: PostgreSQL Triggers > AFTER DELETE Trigger > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL AFTER DELETE Trigger **Info:** AFTER DELETE triggers work the same on any PostgreSQL database, so you can apply these patterns wherever you run Postgres. If you're an enterprise looking for managed Postgres in the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers performance, security, and native integration with the Lakehouse so your triggers, audit logs, and downstream analytics live in one place. If you're a developer or startup who needs to ship fast and scale without ops overhead, [Neon](https://neon.com) gives you the best Postgres platform to build on. **Summary**: in this tutorial, you will learn how to define a PostgreSQL `AFTER DELETE` trigger that is fired after a row is deleted from a table. ## Introduction to the PostgreSQL AFTER DELETE trigger In PostgreSQL, a trigger is a database object that is automatically activated in response to an event including [`INSERT`](../postgresql-tutorial/postgresql-insert), [`UPDATE`](../postgresql-tutorial/postgresql-update), [`DELETE`](../postgresql-tutorial/postgresql-delete), or [`TRUNCATE`](../postgresql-tutorial/postgresql-truncate-table) occurring on a table. An `AFTER DELETE` trigger is activated after one or more rows are deleted from a table. An `AFTER 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 an `AFTER DELETE` trigger, the `OLD` 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 syntax `OLD.column_name`. Notice that you cannot change the column values (`OLD.column_name`) because they are read-only. To create an `AFTER DELETE` trigger, you follow these steps: First, [define a trigger function](../postgresql-plpgsql/postgresql-create-function) that will execute after a `DELETE` operation: ```plsql 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: ```sql CREATE TRIGGER trigger_name AFTER DELETE ON table_name FOR EACH ROW EXECUTE FUNCTION trigger_function_name(); ``` ## PostgreSQL AFTER DELETE trigger example We'll use an `AFTER DELETE` trigger to archive a deleted row in a separate table. First, [create a table](../postgresql-tutorial/postgresql-create-table) called `employees` to store the employee data: ```sql CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, salary NUMERIC(10, 2) NOT NULL ); ``` Second, [insert two rows](../postgresql-tutorial/postgresql-insert-multiple-rows) into the `employees` table: ```sql 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 named `employee_archives` for archiving deleted employees: ```sql 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 that [inserts](../postgresql-tutorial/postgresql-insert) a deleted employee into the `employee_archives` table: ```plsql CREATE OR REPLACE FUNCTION archive_deleted_employee() RETURNS TRIGGER AS $$ BEGIN INSERT INTO employee_archives(id, name, salary) VALUES (OLD.id, OLD.name, OLD.salary); RETURN OLD; END; $$ LANGUAGE plpgsql; ``` Fifth, create an `AFTER DELETE` trigger that executes the `archive_deleted_employee()` function when a row is deleted from the `employees` table: ```sql CREATE TRIGGER after_delete_employee_trigger AFTER DELETE ON employees FOR EACH ROW EXECUTE FUNCTION archive_deleted_employee(); ``` Sixth, [delete a row](../postgresql-tutorial/postgresql-delete) from the `employees` table: ```sql DELETE FROM employees WHERE id = 1 RETURNING *; ``` Output: ``` id | name | salary ----+----------+---------- 1 | John Doe | 90000.00 (1 row) ``` The `AFTER INSERT` trigger will be activated that calls the `archive_deleted_employee()` function to insert the deleted row into the `employee_archives` table. Seventh, retrieve data from the `employee_archives` table: ```sql 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 the `AFTER DELETE` trigger has successfully archived the deleted row into the `employee_archives` table. ## Summary - Use a `BEFORE DELETE` trigger to automatically call a function before a row is deleted from a table. --- ## Related docs (PostgreSQL Triggers) - [Introduction](https://neon.com/postgresql/triggers/introduction-postgresql-trigger) - [CREATE TRIGGER](https://neon.com/postgresql/triggers/creating-first-trigger-postgresql) - [DROP TRIGGER](https://neon.com/postgresql/triggers/drop-trigger) - [ALTER TRIGGER](https://neon.com/postgresql/triggers/alter-trigger) - [AFTER INSERT Trigger](https://neon.com/postgresql/triggers/after-insert-trigger) - [BEFORE INSERT Trigger](https://neon.com/postgresql/triggers/before-insert-trigger) - [BEFORE UPDATE Trigger](https://neon.com/postgresql/triggers/before-update-trigger) - [AFTER UPDATE Trigger](https://neon.com/postgresql/triggers/after-update-trigger) - [BEFORE DELETE Trigger](https://neon.com/postgresql/triggers/before-delete-trigger) - [INSTEAD OF Triggers](https://neon.com/postgresql/triggers/instead-of-triggers) - [BEFORE TRUNCATE Trigger](https://neon.com/postgresql/triggers/before-truncate-trigger) - [Disable Triggers](https://neon.com/postgresql/triggers/managing-postgresql-trigger) - [Enable Triggers](https://neon.com/postgresql/triggers/enable-triggers) - [List All Triggers](https://neon.com/postgresql/triggers/how-to-list-all-triggers-in-postgresql) - [Event Triggers](https://neon.com/postgresql/triggers/event-trigger) - [Conditional Triggers](https://neon.com/postgresql/triggers/trigger-when-condition)