Bruno LEVEQUE ([EMAIL PROTECTED]) 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
