Re: Trigger before delete does fire before, but delete doesn't not happen

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Adrian Klaver a écrit :
> On Friday 28 November 2008 3:47:10 am Stéphane A. Schildknecht wrote:
>> drop table commande cascade;
>> drop table commandeligne;
>>
>> CREATE TABLE commande
>> (
>>   id integer NOT NULL,
>>   montant real,
>>   CONSTRAINT id PRIMARY KEY (id)
>> )with oids;
>>
>> CREATE TABLE commandeligne
>> (
>>   id_commande integer NOT NULL references commande (id)
>> --  on delete cascade on update cascade
>>   ,
>>   montant real,
>>   id_produit integer NOT NULL,
>>   CONSTRAINT clef PRIMARY KEY (id_commande, id_produit)
>> )with oids;
>>
>> CREATE OR REPLACE FUNCTION p_commande_bd() RETURNS "trigger" AS
>> $BODY$
>> BEGIN
>>     -- RAISE NOTICE 'Table commandeligne : suppression de la ligne %',
>> OLD.id; DELETE FROM commandeligne WHERE id_commande = OLD.id;
>>     -- RAISE NOTICE 'Table commandeligne : ligne % supprimée', OLD.id;
>>     RETURN OLD;
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql' VOLATILE;
>>
>> DROP TRIGGER  IF EXISTS    p_commande_bd ON commande;
>> CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE
>> PROCEDURE p_commande_bd();
>>
>> CREATE OR REPLACE FUNCTION p_commandeligne_ad() RETURNS "trigger" AS
>> $BODY$
>> BEGIN
>>     -- RAISE NOTICE 'Table commande : maj de la ligne %', OLD.id_commande;
>>     UPDATE commande SET montant=montant-OLD.montant WHERE id =
>> OLD.id_commande; -- RAISE NOTICE 'Table commande : ligne % maj (%)',
>> OLD.id_commande, OLD.montant;
>>     RETURN NEW;
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql' VOLATILE;
>> DROP TRIGGER  IF EXISTS    p_commandeligne_ad ON commandeligne;
>> CREATE TRIGGER p_commandeligne_ad  AFTER DELETE ON commandeligne FOR EACH
>> ROW EXECUTE PROCEDURE p_commandeligne_ad();
>>
>>
>> -------- First step : Creating first command
>> insert into commande(id, montant) values(1,150);
>> insert into commandeligne(id_commande,id_produit, montant) values(1,1,100);
>> insert into commandeligne(id_commande,id_produit, montant) values(1,2,20);
>> insert into commandeligne(id_commande,id_produit, montant) values(1,3,30);
>>
>> select oid,* from commande where id=1;
>> select oid,* from commandeligne where id_commande=1;
>>
>> -------- 2nd step : Deletion of command 1
>> delete from commande where id=1;
>
> When I run this test case I get:
>
> test=# -------- 2nd step : Deletion of command 1
> test=# delete from commande where id=1;
> ERROR:  update or delete on table "commande" violates foreign key
> constraint "commandeligne_id_commande_fkey" on table "commandeligne"
> DETAIL:  Key (id)=(1) is still referenced from table "commandeligne".
>
> The FK in  commandeligne (id_commande integer NOT NULL references commande
> (id))  is preventing the trigger from completing.
>

Here, I don't get that error.

Maybe you could try creating the commandeligne table like that :

CREATE TABLE commandeligne
(
  id_commande integer NOT NULL
  -- references commande (id)
  --  on delete cascade on update cascade
   ,
   montant real,
   id_produit integer NOT NULL,
   CONSTRAINT clef PRIMARY KEY (id_commande, id_produit)
)with oids;

I'm running PG 8.3.5 or 8.2.11, result is the same.

Regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJM4+zA+REPKWGI0ERAmeCAKCV5upN9r7174fzIQRLE6pajSc1tACg4pw0
SRmXwnN3huC4A6vteOo9CkE=
=mlSt
-----END PGP SIGNATURE-----