Re: Why are triggers semi-deferred? - Mailing list pgsql-hackers

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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073