Re: recursive inner trigger call

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 <skydelta98(at)yahoo(dot)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(dot)gauthier(at)intel(dot)com>
> *To:* Red Light <skydelta98(at)yahoo(dot)com>; "pgsql-general(at)postgresql(dot)org" <
> pgsql-general(at)postgresql(dot)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-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Red Light
> *Sent:* Thursday, December 01, 2011 1:58 PM
> *To:* pgsql-general(at)postgresql(dot)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.