Trigger firing order odd? - Mailing list pgsql-hackers

I just noticed that if two updates are done in the same PLPGSQL procedure, 
then all the BEFORE triggers fire just before the updates, but that all the 
AFTER triggers fire when the procedure exits. This is contrary to what 
happens in Dec RDB, not sure about others. It is also a little 
counter-intuitive.

I would expect:

Begin   update...;   update...;
End;

to fire the BEFORE, the AFTER, then the BEFORE & AFTER again.

Sample code below.
---------------------

CREATE TABLE zzz (    f1 integer
);


--
-- TOC entry 632 (OID 3098251)
-- Name: zzz_tg_proc (); Type: FUNCTION; Schema: public; Owner: pgsql
--

CREATE or REPLACE FUNCTION zzz_tg_proc () RETURNS "trigger"    AS 'Begin Raise NOTICE ''In trigger %'',TG_NAME; return
NEW;end'   LANGUAGE plpgsql;
 

CREATE FUNCTION zzz_proc () RETURNS void    AS 'Begin Raise NOTICE ''In proc'';        update zzz set f1 = 2 where
f1=1;       Raise NOTICE ''Updating again'';        update zzz set f1 = 1 where f1=2;        Raise NOTICE ''Leaving
Proc'';       return;        end;' Language 'plpgsql';
 



--
-- TOC entry 633 (OID 3098523)
-- Name: zzz_proc (); Type: FUNCTION; Schema: public; Owner: pgsql
--

CREATE FUNCTION zzz_proc () RETURNS void    AS 'Begin Raise NOTICE ''In proc''; insert into zzz values(1); Raise 
NOTICE ''Leaving Proc''; return;end'    LANGUAGE plpgsql;

CREATE FUNCTION zzz_upd () RETURNS void    AS 'Begin Raise NOTICE ''In proc''; update zzz set f1 = 1 where f1=1; 
Raise NOTICE ''Leaving Proc''; return;end'    LANGUAGE plpgsql;

CREATE TRIGGER zzz_upd_bef_tg    BEFORE UPDATE ON zzz    FOR EACH ROW    EXECUTE PROCEDURE zzz_tg_proc ();

CREATE TRIGGER zzz_upd_aft_tg    AFTER UPDATE ON zzz    FOR EACH ROW    EXECUTE PROCEDURE zzz_tg_proc ();

CREATE TRIGGER zzz_add_bef_tg    BEFORE INSERT ON zzz    FOR EACH ROW    EXECUTE PROCEDURE zzz_tg_proc ();

CREATE TRIGGER zzz_add_aft_tg    AFTER INSERT ON zzz    FOR EACH ROW    EXECUTE PROCEDURE zzz_tg_proc ();


insert into zzz values(1);
NOTICE:  In trigger zzz_add_bef_tg
NOTICE:  In trigger zzz_add_aft_tg

select zzz_proc();
NOTICE:  In proc
NOTICE:  In trigger zzz_upd_bef_tg
NOTICE:  Updating again
NOTICE:  In trigger zzz_upd_bef_tg
NOTICE:  Leaving Proc
NOTICE:  In trigger zzz_upd_aft_tg
NOTICE:  In trigger zzz_upd_aft_tg



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