Introduction to the PostgreSQL AFTER UPDATE trigger

In PostgreSQL, a trigger is a database object that is fired automatically when an event such asINSERT,UPDATE,DELETE, orTRUNCATE occurs.

AnAFTER UPDATE trigger is a type of trigger that fires after anUPDATE operation is completed successfully on a table.

Because theAFTER UPDATE triggers can access the row after the update, you can perform tasks such as logging changes, updating data in related tables, or sending notifications based on the modified data.

In anAFTER 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.

Additionally, you can access the following variables:

• TG_NAME: Store the name of the trigger.
• TG_OP: Represent the operation that activates the trigger, which isUPDATE for theAFTERUPDATE trigger.
• TG_WHEN: Represent the trigger timing, which isAFTER for theAFTER UPDATE trigger.

To create aAFTER UPDATE trigger, you use the following steps:

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

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

Second, create aAFTER UPDATE trigger that executes the trigger function:

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

PostgreSQL AFTER UPDATE trigger example

First,create a new table calledsalaries to store the employee’s salaries:

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

Second, create a table calledsalary_changes that stores the updates to thesalary column of thesalaries table:

CREATE TABLE salary_changes (    idSERIAL PRIMARY KEY,    employee_idINT NOT NULL,    old_salaryNUMERIC NOT NULL,    new_salaryNUMERIC NOT NULL,    changed_atTIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Third, define the functionlog_salary_changes() that logs the changes of values in thesalary column to thesalary_changes table:

CREATE OR REPLACE FUNCTION log_salary_change()RETURNS TRIGGERAS$$BEGIN    INSERT INTO salary_changes (employee_id, old_salary, new_salary)    VALUES (NEW.id, OLD.salary, NEW.salary);    RETURN NEW;END;$$LANGUAGE plpgsql;

Fourth, define anAFTER UPDATE trigger that calls thelog_salary_change() function after an update occurs to thesalary column of thesalaries table:

CREATE TRIGGER after_update_salary_triggerAFTER UPDATE OF salary ON salariesFOR EACH ROWEXECUTE FUNCTION log_salary_change();

Fifth,insert some rows into thesalaries table:

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

Output:

id |   name   | salary----+----------+--------  1 | John Doe |  90000  2 | Jane Doe |  95000(2 rows)

Sixth, increase the salary ofJohn Doe by 5%:

UPDATE salariesSET salary= salary* 1.05WHERE id= 1;

Seventh, retrieve the data fromsalary_changes table:

SELECT * FROM salary_changes;

Output:

id | employee_id | old_salary | new_salary |         changed_at----+-------------+------------+------------+----------------------------  1 |           1 |      90000 |   94500.00 | 2024-03-28 13:42:37.400673(1 row)

The output shows that the salary before and after changes have been logged to thesalary_changes table.

Summary

• Use aBEFOREUPDATE trigger to execute a function before an update operation occurs.

Last updated onMarch 28, 2024

On this page

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