INSTEAD OF trigger for views in PostgreSQL

Introduction

INSTEAD OF triggers let you change data through views that normally can't be updated. They run custom code instead of the usual action.

You want to update data through a view that combines multiple tables.

You want to allow inserts or deletes on a view that normally doesn't support them.

You want to control exactly what happens when someone changes data through a view.

You want to hide complex table structures but still allow data changes via a simple view.

Syntax

PostgreSQL

CREATE TRIGGER trigger_name
INSTEAD OF {INSERT | UPDATE | DELETE} ON view_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

The trigger must call a function that handles the data change.

INSTEAD OF triggers only work on views, not on tables.

Examples

This example creates a trigger that updates the underlying table when the view is updated.

PostgreSQL

CREATE OR REPLACE FUNCTION update_view() RETURNS trigger AS $$
BEGIN
  UPDATE real_table SET column1 = NEW.column1 WHERE id = OLD.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_view_trigger
INSTEAD OF UPDATE ON my_view
FOR EACH ROW
EXECUTE FUNCTION update_view();

This example allows inserting new rows through the view.

PostgreSQL

CREATE OR REPLACE FUNCTION insert_view() RETURNS trigger AS $$
BEGIN
  INSERT INTO real_table(column1) VALUES (NEW.column1);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_view_trigger
INSTEAD OF INSERT ON my_view
FOR EACH ROW
EXECUTE FUNCTION insert_view();

Sample Program

This example shows a view for sales employees. The INSTEAD OF trigger lets you update the employee's name through the view. The final SELECT shows the updated row in the real table.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT
);

INSERT INTO employees (name, department) VALUES
('Alice', 'Sales'),
('Bob', 'HR');

CREATE VIEW sales_employees AS
SELECT id, name FROM employees WHERE department = 'Sales';

CREATE OR REPLACE FUNCTION update_sales_employee() RETURNS trigger AS $$
BEGIN
  UPDATE employees SET name = NEW.name WHERE id = OLD.id AND department = 'Sales';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sales_employee_update
INSTEAD OF UPDATE ON sales_employees
FOR EACH ROW
EXECUTE FUNCTION update_sales_employee();

-- Update name through the view
UPDATE sales_employees SET name = 'Alicia' WHERE id = 1;

-- Check updated data
SELECT * FROM employees WHERE id = 1;

Important Notes

INSTEAD OF triggers let you customize how changes to views affect underlying tables.

Always test your trigger functions carefully to avoid unexpected data changes.

INSTEAD OF triggers are useful for complex views that combine or filter data.

Summary

INSTEAD OF triggers run custom code instead of the usual insert, update, or delete on views.

They let you make views updatable even if they normally are not.

You write a trigger function to define what happens when data changes through the view.