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

Поиск
Список
Период
Сортировка
От Adrian Klaver
Тема Re: Trigger before delete does fire before, but delete doesn't not happen
Дата
Msg-id 200812010740.10167.aklaver@comcast.net
обсуждение исходный текст
Ответ на Re: Trigger before delete does fire before, but delete doesn't not happen  (Adrian Klaver <aklaver@comcast.net>)
Список pgsql-general
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@comcast.net



--
Adrian Klaver
aklaver@comcast.net

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Trigger before delete does fire before, but delete doesn't not happen
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Detecting changes to certain fields in 'before update' trigger functions