Thread

delete in a trigger

PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-01-16T21:32:43Z

Bruno LEVEQUE (bruno.leveque@libertysurf.fr) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
delete in a trigger

Long Description
I have 2 tables and one trigger. When I delete a line in the first table, the trigger must delete a line in the second. The problem is :
	The trigger wants to delete the line like I ask it but at the end it repeats the "delete" so it aborts the query. It sames to loop.

Is-it a Bug or an error ?

Sample Code
The trigger is :

CREATE FUNCTION "deletecheque" ( ) RETURNS opaque AS '
DECLARE
   debitOld   FLOAT8;
   creditOld  FLOAT8;
   sommeOld   FLOAT8;
   intitule   TEXT;
   taux       FLOAT8;
   ordre      FLOAT8;

   debO1      FLOAT8;
   debO2      FLOAT8;
   credO1      FLOAT8;
   credO2      FLOAT8;

BEGIN
   RAISE DEBUG ''1 - '';
   -- initialisation des variables
   taux     := extraitTaux(old.num_cpte,old.prixaction * old.nbaction);
   ordre    := extraitOrdre(old.num_cpte,old.prixaction * old.nbaction);

   intitule := creeIntitule(old.code);

   IF count(*) = 0 FROM enEuro
     WHERE
              num_cpte = old.num_cpte THEN
      -- le compte est un compte en franc
      sommeOld    := creeSomme(old.prixaction,old.nbaction);
   ELSE
      -- le compte est un compte en euro
      sommeOld    := old.prixaction * old.nbaction;
   END IF;

   -- vrai si debit
   -- faux si credit
   IF old.deb_cred THEN
      debitOld  := sommeOld * (1 + taux) + ordre;
      creditOld := 0;
   ELSE
      debitOld  := 0;
      creditOld := sommeOld * (1 - taux) - ordre;
   END IF;

   -- verification de l existence de la ligne dans la table CHEQUE

   debO1  := debitOld  - 0.01;
   debO2  := debitOld  + 0.01;
   credO1 := creditOld - 0.01;
   credO2 := creditOld + 0.01;

   IF count(*) = 0 FROM cheque
     WHERE
             num_cpte = old.num_cpte
         AND date     = old.date
         AND lib      = intitule
         AND deb      >= debO1
         AND deb      < debO2
         AND cred     >= credO1
         AND cred     < credO2 THEN

      RAISE EXCEPTION ''PAS de LIGNE REPONDANT AUX CRITERES'';
   END IF;

   -- suppression de la ligne de la table CHEQUE
   DELETE FROM cheque
     WHERE
             num_cpte = old.num_cpte
         AND date     = old.date
         AND lib      = intitule
         AND deb      >= debO1
         AND deb      < debO2
         AND cred     >= credO1
         AND cred     < credO2;

   RETURN old;
END;
' LANGUAGE 'plpgsql';


CREATE TRIGGER "deletebourse" AFTER DELETE ON "action"  FOR EACH ROW EXECUTE PROCEDURE "deletecheque" ();


With the d2 flag I see :

query: delete from action where num_cpte=9 and date='13/12/2000'::date and code=
12546 and nbaction=50 and prixaction>=4.3 and prixaction<4.32 and deb_cred='t';
ProcessQuery

DEBUG:  1 -
query: SELECT  extraitTaux( $1 , $2  *  $3 )
query: SELECT  frais.taux FROM frais WHERE num_cpte =  $1  AND limitesup >  $2
AND limiteinf <  $3
query: SELECT   $1
query: SELECT  extraitOrdre( $1 , $2  *  $3 )
query: SELECT  frais.ordre FROM frais WHERE num_cpte =  $1  AND limitesup >  $2
 AND limiteinf <  $3
query: SELECT   $1
query: SELECT  creeIntitule( $1 )
query: SELECT  action_nom.intitule FROM action_nom WHERE action_nom.code =  $1
query: SELECT  NOT  $1
query: SELECT  textcat('action : ', $1 )
DEBUG:  intitule : "action : canal +"
query: SELECT   $1
query: SELECT  count(*) = 0 FROM enEuro WHERE num_cpte =  $1
query: SELECT   $1  *  $2
query: SELECT   $1
query: SELECT   $1  * (1 +  $2 ) +  $3

query: SELECT  0
query: SELECT   $1  - 0.01
query: SELECT   $1  + 0.01
query: SELECT   $1  - 0.01
query: SELECT   $1  + 0.01
query: SELECT  count(*) = 0 FROM cheque WHERE num_cpte =  $1  AND date =  $2  AN
D lib =  $3  AND deb >=  $4  AND deb <  $5  AND cred >=  $6  AND cred <  $7
query: DELETE FROM cheque WHERE num_cpte =  $1  AND date =  $2  AND lib =  $3  A
ND deb >=  $4  AND deb <  $5  AND cred >=  $6  AND cred <  $7

DEBUG:  1 -
DEBUG:  intitule : "action : canal +"

ERROR:  PAS de LIGNE REPONDANT AUX CRITERES
AbortCurrentTransaction


No file was uploaded with this report