-----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----- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
