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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
