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

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