Re: Why are triggers semi-deferred?

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)
row1 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
triggerfires(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)
row1 updated       row 2 updated  procedure executes second update    before trigger fires(row 1)    before trigger
fires(row2)       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   |/