How to Create Instead Of Trigger in PostgreSQL

In PostgreSQL, you create an INSTEAD OF trigger on a view to override the default action for insert, update, or delete operations. Use CREATE TRIGGER ... INSTEAD OF syntax with a trigger function that defines the custom behavior.

Syntax

An INSTEAD OF trigger is defined on a view to replace the standard action when an insert, update, or delete is attempted. It requires a trigger function that performs the desired operation.

• CREATE TRIGGER: starts the trigger creation.
• trigger_name: name you choose for the trigger.
• INSTEAD OF: specifies the trigger type.
• INSERT | UPDATE | DELETE: event that fires the trigger.
• ON view_name: the view the trigger is attached to.
• FOR EACH ROW: trigger runs for each affected row.
• EXECUTE FUNCTION function_name(): calls the trigger function.

sql

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

Example

This example shows how to create a view and an INSTEAD OF INSERT trigger that inserts data into the underlying table when you insert into the view.

sql

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

CREATE VIEW employee_view AS
SELECT id, name, department FROM employees;

CREATE OR REPLACE FUNCTION employee_view_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO employees (name, department) VALUES (NEW.name, NEW.department);
  RETURN NULL; -- INSTEAD OF triggers return NULL
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_employee_instead
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
EXECUTE FUNCTION employee_view_insert();

-- Insert into the view
INSERT INTO employee_view (name, department) VALUES ('Alice', 'HR');

-- Check the table
SELECT * FROM employees;

Output

 id | name  | department 
----+-------+------------
  1 | Alice | HR
(1 row)

Common Pitfalls

• Trying to create an INSTEAD OF trigger on a table instead of a view will cause an error because INSTEAD OF triggers only work on views.
• Not returning NULL from the trigger function causes errors; INSTEAD OF triggers must return NULL.
• Forgetting to handle all necessary operations (insert, update, delete) if your application needs them.

sql

/* Wrong: INSTEAD OF trigger on a table (will error) */
CREATE TRIGGER wrong_trigger
INSTEAD OF INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION some_function();

/* Right: INSTEAD OF trigger on a view */
CREATE TRIGGER correct_trigger
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
EXECUTE FUNCTION some_function();

Quick Reference

Clause	Description
CREATE TRIGGER trigger_name	Defines the trigger with a chosen name
INSTEAD OF INSERT | UPDATE | DELETE	Specifies the event and that the trigger replaces default action
ON view_name	The view the trigger is attached to
FOR EACH ROW	Trigger fires once per affected row
EXECUTE FUNCTION function_name()	Calls the trigger function that implements custom logic

Key Takeaways

INSTEAD OF triggers in PostgreSQL are only for views, not tables.

You must write a trigger function that performs the desired operation and returns NULL.

Use INSTEAD OF triggers to customize insert, update, or delete behavior on views.

Always test your trigger function to ensure it correctly handles the data changes.

Remember to attach the trigger to the view, not the underlying table.