[SOLVED] TRIGGER ON VIEW in PostgreSQL doesn't trigger

TRIGGER ON VIEW in PostgreSQL doesn't trigger

I'm trying to add a trigger on a VIEW in PostgreSQL 9.6.

This is my view:

CREATE VIEW names AS
SELECT one.name AS name_one, two.name AS name_two, three.name AS name_three
FROM table_one one
LEFT JOIN table_two two ON one.id = two.id
LEFT JOIN table_three three ON two.id = three.id;

This is my trigger function:

CREATE OR REPLACE FUNCTION notify_name_changed() RETURNS trigger AS $BODY$
BEGIN
  PERFORM pg_notify('name_changed', row_to_json(NEW)::text);
  RETURN NULL;
END; 
$BODY$ LANGUAGE plpgsql;

And my CREATE TRIGGER:

CREATE TRIGGER notify_name_changed INSTEAD OF INSERT OR UPDATE OR DELETE ON "names"
  FOR EACH ROW EXECUTE PROCEDURE notify_name_changed();

This doesn't fire any changes whenever something happens in one of the base tables. However, creating 3 individual triggers does, but is somewhat unrelated to the view:

CREATE TRIGGER notify_name_changed AFTER INSERT OR UPDATE OR DELETE ON "one"
  FOR EACH ROW EXECUTE PROCEDURE notify_name_changed();
    
CREATE TRIGGER notify_name_changed AFTER INSERT OR UPDATE OR DELETE ON "two"
  FOR EACH ROW EXECUTE PROCEDURE notify_name_changed();
    
CREATE TRIGGER notify_name_changed AFTER INSERT OR UPDATE OR DELETE ON "three"
  FOR EACH ROW EXECUTE PROCEDURE notify_name_changed();

Isn't it possible to add a trigger directly on a view, which fires in the event of any changes in base tables used in that view?

Solution

I think you misunderstand the concept of a view.

A view does not hold any data, you can see it as a “crystallized SQL statement” that has a name. Whenever a view is used in a query, it is replaced by its definition in the “query rewrite” step.

An INSTEAD OF trigger for UPDATE on a view is triggered only if you update the view itself, not the underlying tables. For that, you'd have to define triggers on those tables.

The point that you are probably missing is that if something changes in the underlying tables, it is immediately changed in the view, since the view is just a query on the base table.