PostgreSQL INSTEAD OF Triggers
Summary: in this tutorial, you will learn about PostgreSQLINSTEAD OF a trigger to insert, update, or delete data of base tables through a view.
Introduction to PostgreSQL INSTEAD OF triggers
In PostgreSQL,INSTEAD OF triggers are a special type of triggers thatintercept insert, update, and delete operations on views.
It means that when you execute anINSERT,UPDATE, orDELETE statement on a view, PostgreSQL does not directly execute the statement. Instead, it executes the statements defined in theINSTEAD OF trigger.
To create anINSTEAD OF trigger, you follow these steps:
First,define a function that will execute when a trigger is fired:
CREATE OR REPLACE FUNCTION fn_trigger()RETURNS TRIGGER AS$$ -- function body$$LANGUAGE plpgsql;Inside the function, you can customize the behavior for each operation includingINSERT,UPDATE, andDELETE.
Second, create anINSTEAD OF trigger and bind the function to it:
CREATE TRIGGER trigger_nameINSTEAD OFINSERT OR UPDATE OR DELETEON table_nameFOR EACHROWEXECUTE FUNCTION fn_trigger;PostgreSQL INSTEAD OF trigger example
Let’s take an example of creating anINSTEAD OF trigger.
1) Setting up a view with an INSTEAD OF trigger
First,create two tablesemployees andsalaries:
CREATE TABLE employees ( employee_idSERIAL PRIMARY KEY, name VARCHAR(100)NOT NULL);CREATE TABLE salaries ( employee_idINT, effective_dateDATE NOT NULL, salaryDECIMAL(10,2)NOT NULL DEFAULT 0, PRIMARY KEY (employee_id, effective_date), FOREIGN KEY (employee_id)REFERENCES employees(employee_id));Next,insert rows into theemployees andsalaries tables:
INSERT INTO employees (name)VALUES ('Alice'), ('Bob')RETURNING*;INSERT INTO salariesVALUES (1,'2024-03-01',60000.00), (2,'2024-03-01',70000.00)RETURNING*;Then,create a view based on theemployees andsalaries tables:
CREATE VIEW employee_salariesASSELECT e.employee_id, e.name, s.salary, s.effective_dateFROM employees eJOIN salaries sON e.employee_id= s.employee_id;After that, create a function that will execute when theINSTEADOF trigger associated with the view activates:
CREATE OR REPLACE FUNCTION update_employee_salaries()RETURNS TRIGGERAS$$DECLARE p_employee_idINT;BEGIN IF TG_OP= 'INSERT' THEN-- insert a new employee INSERT INTO employees(name) VALUES (NEW.name)RETURNING employee_idINTO p_employee_id;-- insert salary for the employee INSERT INTO salaries(employee_id, effective_date, salary)VALUES (p_employee_id, NEW.effective_date, NEW.salary); ELSIF TG_OP= 'UPDATE' THEN UPDATE salariesSET salary= NEW.salaryWHERE employee_id= NEW.employee_id; ELSIF TG_OP= 'DELETE' THEN DELETE FROM salariesWHERE employee_id= OLD.employee_id; END IF; RETURN NULL;END;$$LANGUAGE plpgsql;If you execute an insert against theemployee_salaries view, theINSTEAD OF trigger will insert a new row into the employees table first, then insert a new row into the salaries table.
When you update an employee’s salary by id, theINSTEAD OF trigger will update the data in thesalaries table.
If you delete a row from theemployee_salaries view, theINSTEAD OF trigger will delete a row from theemployees table. TheDELETE CASCADE will automatically delete a corresponding row from the salaries table.
Finally, create anINSTEAD OF trigger that will be fired for theINSERT,UPDATE, orDELETE on theemployee_salaries view:
CREATE TRIGGER instead_of_employee_salariesINSTEAD OF INSERT OR UPDATE OR DELETEON employee_salariesFOR EACH ROWEXECUTE FUNCTION update_employee_salaries();1) Inserting data into tables via the view
First, insert a new employee with a salary via the view:
INSERT INTO employee_salaries (name, salary, effective_date)VALUES ('Charlie',75000.00,'2024-03-01');PostgreSQL does not execute this statement. Instead, it executes the statement defined in theINSTEADOF trigger. More specifically, it executes two statements:
1) Insert a new row into theemployees table and get the employee id:
INSERT INTO employees(name)VALUES (NEW.name)RETURNING employee_idINTO p_employee_id;2) Insert a new row into the salaries table using the employee id, salary, and effective date:
INSERT INTO salaries(employee_id, effective_date, salary)VALUES (p_employee_id, NEW.effective_date, NEW.salary);Second, verify the inserts by retrieving data from theemployees andsalaries tables:
SELECT * FROM employees;Output:
employee_id | name-------------+--------- 1 | Alice 2 | Bob 3 | Charlie(3 rows)SELECT * FROM salaries;Output:
employee_id | effective_date | salary-------------+----------------+---------- 1 | 2024-03-01 | 60000.00 2 | 2024-03-01 | 70000.00 3 | 2024-03-01 | 75000.00(3 rows)2) Updating data into tables via the view
First, update the salary of the employee id 3 via theemployee_salaries view:
UPDATE employee_salariesSET salary= 95000WHERE employee_id= 3;Second, retrieve data from thesalaries table:
SELECT * FROM salaries;Output:
employee_id | effective_date | salary-------------+----------------+---------- 1 | 2024-03-01 | 60000.00 2 | 2024-03-01 | 70000.00 3 | 2024-03-01 | 95000.00(3 rows)3) Deleting data via views
First, delete the employee with id 3 via theemployee_salaries view:
DELETE FROM employee_salariesWHERE employee_id= 3;Second, retrieve data from theemployees table:
SELECT * FROM employees;Output:
employee_id | effective_date | salary-------------+----------------+---------- 1 | 2024-03-01 | 60000.00 2 | 2024-03-01 | 70000.00(2 rows)Because of theDELETECASCADE, PostgreSQL also deletes the corresponding row in thesalaries table:
SELECT * FROM salaries;Output:
employee_id | effective_date | salary-------------+----------------+---------- 1 | 2024-03-01 | 60000.00 2 | 2024-03-01 | 70000.00(2 rows)Summary
- Use the
INSTEAD OFtrigger to customize the behavior ofINSERT,UPDATE, andDELETEoperations on a database view.
Last updated on