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. > > select oid,* from commande where id=1; > select oid,* from commandeligne where id_commande=1;; -- Adrian Klaver aklaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
