On Monday 01 December 2008 7:27:48 am Adrian Klaver wrote: > 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. Sorry, this applies to a trigger calling the function on the same table. > -- > Adrian Klaver > aklaver@xxxxxxxxxxx -- 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
