6

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?

4
  • An instead of trigger fires, when you insert into the view, not if you insert into one of the base tables Commented Apr 3, 2019 at 8:20
  • ...if I insert directly into a view doesn't that break the relationship between the view and base tables, and sort of remove the idea of the view? Commented Apr 3, 2019 at 8:28
  • It's not clear to me in the question - are you actually doing an INSERT on the view when you say the view trigger isn't firing? Commented Apr 3, 2019 at 8:35
  • No, I'm doing an insert in one of the base tables. Commented Apr 3, 2019 at 8:42

2 Answers 2

2

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.

Sign up to request clarification or add additional context in comments.

5 Comments

I agree, and it make sense. My goal was to be able to ´listen` (sort of) on the view, for any changes made in its base tables. Instead of on each individual base table, as this makes it more difficult to automatic find the changes it makes in the view, which is what I need.
The view never changes - it has no content.
Make sense, yes. Thank you.
I think it should be supported: On views, triggers can be defined to execute instead of INSERT, UPDATE, or DELETE operations. Such INSTEAD OF triggers are fired once for each row that needs to be modified in the view. See Overview of Trigger Behavior
@SergiuIndrie Sure, but I don't get your point. This doesn't say that a trigger on a view fires when the underlying tables are changed.
0

The trigger with a view is fired when INSERT, UPDATE or DELETE operation is prevented on a view but not on the base table of the view. *My answer explains the trigger with a view in detail.

*Memos:

  • INSTEAD OF can prevent operations.

  • You can use INSTEAD OF only with a view and FOR EACH ROW otherwise there is error.

  • You cannot specify one or more columns with UPDATE OF when using INSTEAD OF otherwise there is error.

  • You cannot use TRUNCATE with INSTEAD OF otherwise there is error.

  • The trigger with BEFORE or AFTER and INSERT, UPDATE or DELETE on a view doesn't work. *My question and the answers explains it in detail.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.