Re: [GENERAL] recursive inner trigger call

[Red Light]

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