pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Trigger 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