Re: Why are triggers semi-deferred?

Added to TODO:

* Have AFTER triggers execute after the appropriate SQL statement in a
function, not at the end of the function

---------------------------------------------------------------------------

Philip Warner wrote:
> At 11:51 PM 1/06/2003 -0400, Bruce Momjian wrote:
> >Does anyone have answers for these? I read the thread and don't 100%
> >understand it all.
>
> My belief is that at least ROW triggers need fixing (7.3 doesn't have
> statement, not sure about 7.4).
>
> Currently, if you write a plpgsql procedure which calls more than one
> insert/update/delete statements, the AFTER triggers for all of these
> statements will not fire until after the procedure exits. They should fire
> either just after each row is updated, or just after the most immediately
> enclosing statement executes. I think the thread wanted the latter.
>
> So, if we have a table with two rows, and a BEFORE and AFTER trigger, and a
> plpgsql procedure that updates all rows twice, then we should have:
>
> procedure called
> procedure executes first update
> before trigger fires(row 1)
> before trigger fires(row 2)
> row 1 updated
> row 2 updated
> after trigger fires(row 1)
> after trigger fires(row 2)
> procedure executes second update
> before trigger fires(row 1)
> before trigger fires(row 2)
> row 1 updated
> row 2 updated
> after trigger fires(row 1)
> after trigger fires(row 2)
> procedure exits
>
> What we have in 7.3 is:
>
> procedure called
> procedure executes first update
> before trigger fires(row 1)
> before trigger fires(row 2)
> row 1 updated
> row 2 updated
> procedure executes second update
> before trigger fires(row 1)
> before trigger fires(row 2)
> row 1 updated
> row 2 updated
> procedure exits
> after trigger fires(row 1)
> after trigger fires(row 2)
> after trigger fires(row 1)
> after trigger fires(row 2)
>
> IIRC, the thread did not really discuss whether do intersperse the BEFORE
> executions with the updates, but doing them all before seems consistent.
>
> Apologies is this has been covered elsewhere...
>
>
>
>
>
>
>
>
> ----------------------------------------------------------------
> Philip Warner | __---_____
> Albatross Consulting Pty. Ltd. |----/ - \
> (A.B.N. 75 008 659 498) | /(@) ______---_
> Tel: (+61) 0500 83 82 81 | _________ \
> Fax: (+61) 03 5330 3172 | ___________ |
> Http://www.rhyme.com.au | / \|
> | --________--
> PGP key available upon request, | /
> and from pgp5.ai.mit.edu:11371 |/
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073