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