On Sunday 30 November 2008 11:18:12 pm Stéphane A. Schildknecht wrote: <Snip> > Adrian Klaver a écrit : > > > > > > 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, It works if you change this to an AFTER DELETE trigger: 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(); Use this version DROP TRIGGER IF EXISTS p_commande_bd ON commande; CREATE TRIGGER p_commande_bd after DELETE ON commande FOR Each row EXECUTE PROCEDURE p_commande_bd(); The problem as far as I can tell is tuple visibility. By using a BEFORE trigger for the first function the OLD.* values are still available when the second trigger fires so UPDATE commande SET montant=montant-OLD.montant WHERE id = OLD.id_commande; has values to update in the commande table. For further clarifciation see: http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html In particular: The data change (insertion, update, or deletion) causing the trigger to fire is naturally not visible to SQL commands executed in a row-level before trigger, because it hasn't happened yet. -- 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
