Hi Red, I have the slight suspicion that you have not understood what NEW and OLD mean, in the context of a trigger, am I correct?
See http://www.postgresql.org/docs/9.0/static/plpgsql-trigger.html Bèrto On 1 December 2011 22:17, Red Light <skydelt...@yahoo.com> wrote: > > Hi Dave, > > when i use before trigger , nothing happen; i mean no cumputation is done > (and when i start tu duplicate the same data just by mistake ...) > i got the same error. > ------------------------------ > * > From:* "Gauthier, Dave" <dave.gauth...@intel.com> > *To:* Red Light <skydelt...@yahoo.com>; "pgsql-general@postgresql.org" < > pgsql-general@postgresql.org> > *Sent:* Thursday, December 1, 2011 8:09 PM > *Subject:* RE: [GENERAL] recursive inner trigger call > > You set the trigger to fire off whenever ed_expore.bv is inserted or > updated. Then the trigger updates ed_explore.bv, which fires the update > trigger again, etc... . Infinite loop. No? > > Maybe you just want to use a before trigger to set that value before the > insert, then you wouldn't need the recursive after trigger? > > > > > *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *Red Light > *Sent:* Thursday, December 01, 2011 1:58 PM > *To:* pgsql-general@postgresql.org > *Subject:* [GENERAL] recursive inner trigger call > > Hi guys, > > i got the following problematic : i got a table called bv that have some > 'entry data' and i have another column that need to be calculated and put > back in the table: > > > here is my table: > > > CREATE TABLE public.bv > ( > id_bv integer NOT NULL, > c_vmax_actuel real, > d_capacite_barrages_new real, > CONSTRAINT "BV_pkey" PRIMARY KEY (id_bv) > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE public.bv OWNER TO postgres; > > > i created a trigger that do the necessary computation: > > CREATE OR REPLACE FUNCTION public.store_bv() RETURNS TRIGGER AS $store_bv$ > DECLARE > v_vmax_actuel numeric(15,2); > BEGIN > IF (TG_OP = 'UPDATE') OR (TG_OP = 'INSERT') THEN > > update ed_explore."bv" set > c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) ; > > END IF; > RETURN NEW; > > > END; > $store_bv$ LANGUAGE plpgsql; > > > the declaration of my trigger : > > CREATE TRIGGER store_bv_trigger > after INSERT OR UPDATE ON ed_explore.bv > FOR EACH ROW EXECUTE PROCEDURE public.store_bv(); > > > and now i start to insert my data: > > insert into public.bv (id_bv,d_capacite_barrages_new) values > (1,7324591);commit; > > > > then the trigger got executed and goes in an infinite loop,here is the > error that i got : > > *ERREUR: dépassement de limite (en profondeur) de la pile > HINT: Augmenter le paramètre « max_stack_depth » après vous être assuré > que la > limite de profondeur de la pile de la plateforme est adéquate. > CONTEXT: instruction SQL « update ed_explore."bv" set c_vmax_actuel = > ((d_capacite_barrages_new) / (30*86400)) »* > > > > And thanks for you help > > > > > -- ============================== If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
