PostgreSQL INSTEAD OF Triggers

Summary: in this tutorial, you will learn about PostgreSQL INSTEAD 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 that intercept insert, update, and delete operations on views.

It means that when you execute an INSERT, UPDATE, or DELETE statement on a view, PostgreSQL does not directly execute the statement. Instead, it executes the statements defined in the INSTEAD OF trigger.

To create an INSTEAD 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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Inside the function, you can customize the behavior for each operation including INSERT, UPDATE, and DELETE.

Second, create an INSTEAD OF trigger and bind the function to it:

CREATE TRIGGER trigger_name
INSTEAD OF INSERT OR UPDATE OR DELETE 
ON table_name
FOR EACH ROW 
EXECUTE FUNCTION fn_trigger;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL INSTEAD OF trigger example

Let’s take an example of creating an INSTEAD OF trigger.

1) Setting up a view with an INSTEAD OF trigger

First, create two tables employees and salaries:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE salaries (
    employee_id INT,
    effective_date DATE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL DEFAULT 0, 
    PRIMARY KEY (employee_id, effective_date),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);Code language: SQL (Structured Query Language) (sql)

Next, insert rows into the employees and salaries tables:

INSERT INTO employees (name) 
VALUES 
   ('Alice'), 
   ('Bob')
RETURNING *;

INSERT INTO salaries 
VALUES 
   (1, '2024-03-01', 60000.00), 
   (2, '2024-03-01', 70000.00)
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Then, create a view based on the employees and salaries tables:

CREATE VIEW employee_salaries 
AS
SELECT e.employee_id, e.name, s.salary, s.effective_date
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

After that, create a function that will execute when the INSTEAD OF trigger associated with the view activates:

CREATE OR REPLACE FUNCTION update_employee_salaries()
RETURNS TRIGGER AS 
$$
DECLARE
    p_employee_id INT;
BEGIN
    IF TG_OP = 'INSERT' THEN
	-- insert a new employee 
        INSERT INTO employees(name) 
        VALUES (NEW.name)
	RETURNING employee_id INTO 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 salaries 
	SET salary = NEW.salary 
	WHERE employee_id = NEW.employee_id;

    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM salaries 
	WHERE employee_id = OLD.employee_id;
    END IF;
    RETURN NULL;
END;
$$ 
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you execute an insert against the employee_salaries view, the INSTEAD 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, the INSTEAD OF trigger will update the data in the salaries table.

If you delete a row from the employee_salaries view, the INSTEAD OF trigger will delete a row from the employees table. The DELETE CASCADE will automatically delete a corresponding row from the salaries table.

Finally, create an INSTEAD OF trigger that will be fired for the INSERT, UPDATE, or DELETE on the employee_salaries view:

CREATE TRIGGER instead_of_employee_salaries
INSTEAD OF INSERT OR UPDATE OR DELETE 
ON employee_salaries
FOR EACH ROW 
EXECUTE FUNCTION update_employee_salaries();Code language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

PostgreSQL does not execute this statement. Instead, it executes the statement defined in the INSTEAD OF trigger. More specifically, it executes two statements:

1) Insert a new row into the employees table and get the employee id:

INSERT INTO employees(name) 
VALUES (NEW.name)
RETURNING employee_id INTO p_employee_id;Code language: SQL (Structured Query Language) (sql)

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);Code language: SQL (Structured Query Language) (sql)

Second, verify the inserts by retrieving data from the employees and salaries tables:

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

 employee_id |  name
-------------+---------
           1 | Alice
           2 | Bob
           3 | Charlie
(3 rows)Code language: SQL (Structured Query Language) (sql)

SELECT * FROM salaries;Code language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

2) Updating data into tables via the view

First, update the salary of the employee id 3 via the employee_salaries view:

UPDATE employee_salaries
SET salary = 95000
WHERE employee_id = 3;Code language: SQL (Structured Query Language) (sql)

Second, retrieve data from the salaries table:

SELECT * FROM salaries;Code language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

3) Deleting data via views

First, delete the employee with id 3 via the employee_salaries view:

DELETE FROM employee_salaries
WHERE employee_id = 3;Code language: SQL (Structured Query Language) (sql)

Second, retrieve data from the employees table:

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

 employee_id | effective_date |  salary
-------------+----------------+----------
           1 | 2024-03-01     | 60000.00
           2 | 2024-03-01     | 70000.00
(2 rows)Code language: SQL (Structured Query Language) (sql)

Because of the DELETE CASCADE, PostgreSQL also deletes the corresponding row in the salaries table:

SELECT * FROM salaries;Code language: SQL (Structured Query Language) (sql)

Output:

 employee_id | effective_date |  salary
-------------+----------------+----------
           1 | 2024-03-01     | 60000.00
           2 | 2024-03-01     | 70000.00
(2 rows)Code language: SQL (Structured Query Language) (sql)

Summary

Use the INSTEAD OF trigger to customize the behavior of INSERT, UPDATE, and DELETE operations on a database view.

Was this tutorial helpful ?