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


Fri, 02 May 2003 15:09:06 +1000
Philip Warner <pjw@rhyme.com.au>
Trigger firing order odd?

Sample code should have been:

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';

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


Fri, 02 May 2003 15:18:38 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Trigger firing order odd?