pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger questions
4+ messages / 4 participants
[nested] [flat]
* Trigger questions
@ 2023-05-04 13:49 DAVID ROTH <adaptron@comcast.net>
0 siblings, 1 reply; 4+ messages in thread
From: DAVID ROTH @ 2023-05-04 13:49 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trigger questions
@ 2023-05-04 14:03 Justin <zzzzz.graf@gmail.com>
parent: DAVID ROTH <adaptron@comcast.net>
0 siblings, 2 replies; 4+ messages in thread
From: Justin @ 2023-05-04 14:03 UTC (permalink / raw)
To: DAVID ROTH <adaptron@comcast.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 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
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trigger questions
@ 2023-05-04 14:30 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Justin <zzzzz.graf@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2023-05-04 14:30 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trigger questions
@ 2023-05-04 14:42 David G. Johnston <david.g.johnston@gmail.com>
parent: Justin <zzzzz.graf@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2023-05-04 14:42 UTC (permalink / raw)
To: Justin <zzzzz.graf@gmail.com>; +Cc: DAVID ROTH <adaptron@comcast.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 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
>
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.
https://www.postgresql.org/docs/current/sql-createtrigger.html
Depending on the view definition, the need for a trigger may be removed
since some views are auto-updatable.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-05-04 14:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-04 13:49 Trigger questions DAVID ROTH <adaptron@comcast.net>
2023-05-04 14:03 ` Justin <zzzzz.graf@gmail.com>
2023-05-04 14:30 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-04 14:42 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox