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

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(at)comcast(dot)net

--
Adrian Klaver
aklaver(at)comcast(dot)net