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?
instead oftrigger fires, when you insert into the view, not if you insert into one of the base tablesinsertdirectly into aviewdoesn't that break the relationship between theviewandbase tables, and sort of remove the idea of theview?insertin one of the base tables.