Introduction to the PostgreSQL BEFORE UPDATE trigger

In PostgreSQL, a trigger is a database object that is automatically activated when an event such asINSERT,UPDATE,DELETE, orTRUNCATE occurs on the associated table.

ABEFORE UPDATE trigger is a type of trigger that activates before anUPDATE operation is applied to a table.

TheseBEFORE UPDATE triggers can be particularly useful when you want to modify data before an update occurs or enforce certain conditions.

In aBEFORE UPDATE trigger, you can access the following variables:

• OLD: This record variable allows you to access the row before the update.
• NEW: This record variable represents the row after the update.

Also, you can access the following variables:

• TG_NAME: Represent the name of the trigger.
• TG_OP: Represent the operation that activates the trigger, which isUPDATE for theBEFORE UPDATE triggers.
• TG_WHEN: Represent the trigger timing, which isBEFORE for theBEFORE UPDATE triggers.

To create aBEFORE UPDATE trigger, you follow these steps:

First,define a trigger function that will execute when theBEFORE UPDATE trigger fires:

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

Second, create aBEFORE UPDATE trigger that executes the defined function:

CREATE TRIGGER trigger_nameBEFORE UPDATEON table_nameFOR EACH {ROW |STATEMENT}EXECUTE FUNCTION trigger_function();

PostgreSQL BEFORE UPDATE trigger example

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

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

Next, define a trigger function thatraises an exception if the new salary is lower than the current salary. The trigger will prevent the update when the exception occurs.

CREATE OR REPLACE FUNCTION fn_before_update_salary()RETURNS TRIGGERAS $$BEGIN    IF NEW.salary< OLD.salaryTHEN        RAISE EXCEPTION'New salary cannot be less than current salary';    END IF;    RETURN NEW;END;$$LANGUAGE plpgsql;

Then, create aBEFORE UPDATE trigger that executes thefn_before_update_salary() before the update:

CREATE TRIGGER before_update_salary_triggerBEFORE UPDATE OF salaryON employeesFOR EACHROWEXECUTE FUNCTION fn_before_update_salary();

ThisBEFORE UPDATE trigger ensures that the salary of the employee cannot be decreased. If you attempt to reduce the salary, the trigger will raise an exception and abort the update.

After that,insert some rows into theemployees table:

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

Output:

id |   name   | salary----+----------+--------  1 | John Doe |  70000  2 | Jane Doe |  80000(2 rows)

Finally, attempt to decrease the salary ofJohn Doe:

UPDATE employeesSET salary= salary* 0.9WHERE id= 1;

TheBEFORE UPDATE trigger raises the following exception:

ERROR:  New salary cannot be less than current salaryCONTEXT:  PL/pgSQLfunction fn_before_update_salary()line 4 at RAISE

Summary

• Use aBEFORE UPDATE trigger to automatically execute a function before an update.

Last updated onMarch 28, 2024

On this page

• Introduction to the PostgreSQL BEFORE UPDATE trigger
• PostgreSQL BEFORE UPDATE trigger example
• Summary