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)
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 |/