info

INSTEAD OF triggers work the same way on any PostgreSQL database, so you can apply these patterns wherever your Postgres runs. If you're an enterprise looking for managed Postgres in the AI era, Lakebase delivers fast, secure Postgres fully integrated with the Lakehouse. If you're a developer or startup who needs to ship and scale quickly, Neon is the Postgres platform built for that pace.

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;

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;

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) ON DELETE CASCADE
);

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 *;

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;

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 employees
	WHERE employee_id = OLD.employee_id;
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

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();

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 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;

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 the employees and salaries 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 the employee_salaries view:

UPDATE employee_salaries
SET salary = 95000
WHERE employee_id = 3;

Second, retrieve data from the salaries 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 the employee_salaries view:

DELETE FROM employee_salaries
WHERE employee_id = 3;

Second, retrieve data from the employees 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 the DELETE CASCADE, PostgreSQL also deletes the corresponding row in the salaries 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 OF trigger to customize the behavior of INSERT, UPDATE, and DELETE operations on a database view.