Introduction to the PostgreSQL BEFORE 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.

ABEFORE DELETE trigger is activated before one or more rows are deleted from a table.

In practice, you’ll useBEFORE DELETE triggers for tasks such as logging deleted data, updating data in related tables, or enforcing complex business rules.

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

Please note that you cannot modify the column values (OLD.column_name) because they are read-only.

To create aBEFORE DELETE trigger, follow these steps:

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

CREATE OR REPLACE FUNCTION trigger_function_name()RETURNS TRIGGERAS$$BEGIN    -- This logic will be executed before the DELETE operation    -- To access the values of rows being deleted:    -- OLD.column_name    RETURN OLD;END;$$LANGUAGE plpgsql;

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

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

PostgreSQL BEFORE DELETE trigger example

We’ll use aBEFORE DELETE trigger to prevent applications from deleting a row in a table.

First,create a table calledproducts that stores the product data:

CREATE TABLE products (    idSERIAL PRIMARY KEY,    name VARCHAR(100)NOT NULL,    priceNUMERIC(10,2)NOT NULL,    status BOOLEAN NOT NULL DEFAULT true);

Next,insert some rows into theproducts table:

INSERT INTO products (name, price,status)VALUES  ('A',10.99, true),  ('B',20.49, false),  ('C',15.79, true)RETURNING*;

Output:

id | name | price | status----+------+-------+--------  1 | A    | 10.99 | t  2 | B    | 20.49 | f  3 | C    | 15.79 | t(3 rows)

Then, create aBEFORE DELETE trigger function thatraises an exception:

CREATE OR REPLACE FUNCTION fn_before_delete_product()RETURNS TRIGGERAS$$BEGIN    RAISE EXCEPTION'Deletion from the products table is not allowed.';END;$$LANGUAGE plpgsql;

After that, create aBEFORE DELETE trigger on theproducts table:

CREATE TRIGGER before_delete_product_triggerBEFORE DELETE ON productsFOR EACHROWEXECUTE FUNCTION fn_before_delete_product();

Finally, delete a row from theproducts table:

DELETE FROM productsWHERE id= 1;

Error:

ERROR:  Deletionfrom the productstable is not allowed.CONTEXT:  PL/pgSQLfunction fn_before_delete_product()line 3 at RAISE

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 BEFORE DELETE trigger
• PostgreSQL BEFORE DELETE trigger example
• Summary