Thread: Trigger questions
1) Can I create a trigger on a view?
2) Do triggers cascade?
Say I have an insert trigger on a table.
And, I have an insert trigger on a view that references this table
If I do an insert on the view, will both triggers fire?
On Thu, May 4, 2023 at 9:49 AM DAVID ROTH <adaptron@comcast.net> wrote:
1) Can I create a trigger on a view?2) Do triggers cascade?Say I have an insert trigger on a table.And, I have an insert trigger on a view that references this tableIf I do an insert on the view, will both triggers fire?
Can not have triggers on Views, Views use RULES which are DO INSTEAD.
https://www.postgresql.org/docs/current/rules.html
Yes if you have an INSERT/UPDATE/DELETE rule on a view that inserts into a table then that table's triggers will be executed.
Please note RULES should be avoided beyond the use case for VIEWS. RULES are executed very early in the query tree; it is not trivial to write rules on Tables.
Thanks
https://www.postgresql.org/docs/current/rules.html
Yes if you have an INSERT/UPDATE/DELETE rule on a view that inserts into a table then that table's triggers will be executed.
Please note RULES should be avoided beyond the use case for VIEWS. RULES are executed very early in the query tree; it is not trivial to write rules on Tables.
Thanks
On 5/4/23 07:03, Justin wrote: > > > On Thu, May 4, 2023 at 9:49 AM DAVID ROTH <adaptron@comcast.net > <mailto:adaptron@comcast.net>> wrote: > > __ > 1) Can I create a trigger on a view? > 2) Do triggers cascade? > > Say I have an insert trigger on a table. > And, I have an insert trigger on a view that references this table > If I do an insert on the view, will both triggers fire? > > > Can not have triggers on Views, Views use RULES which are DO INSTEAD. > https://www.postgresql.org/docs/current/rules.html > <https://www.postgresql.org/docs/current/rules.html> That is wrong. See https://www.postgresql.org/docs/current/sql-createtrigger.html The following table summarizes which types of triggers may be used on tables, views, and foreign tables: > > Yes if you have an INSERT/UPDATE/DELETE rule on a view that inserts > into a table then that table's triggers will be executed. > > Please note RULES should be avoided beyond the use case for VIEWS. > RULES are executed very early in the query tree; it is not > trivial to write rules on Tables. > > Thanks -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, May 4, 2023 at 7:04 AM Justin <zzzzz.graf@gmail.com> wrote:
On Thu, May 4, 2023 at 9:49 AM DAVID ROTH <adaptron@comcast.net> wrote:1) Can I create a trigger on a view?2) Do triggers cascade?Say I have an insert trigger on a table.And, I have an insert trigger on a view that references this tableIf I do an insert on the view, will both triggers fire?Can not have triggers on Views, Views use RULES which are DO INSTEAD.
https://www.postgresql.org/docs/current/rules.html
Our users need not care or even know about this particular implementation detail of views. For them, views are a fundamental concept.
The description of create trigger makes is perfectly clear that views are a valid "table-like" target for a trigger.
> CREATE TRIGGER creates a new trigger. CREATE OR REPLACE TRIGGER will either create a new trigger, or replace an existing trigger. The trigger will be associated with the specified table, view, or foreign table and will execute the specified function function_name when certain operations are performed on that table.
Depending on the view definition, the need for a trigger may be removed since some views are auto-updatable.
David J.