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

On Friday 28 November 2008 3:47:10 am Stéphane A. Schildknecht wrote:
> drop table commande cascade;
> drop table commandeligne;
>
> CREATE TABLE commande
> (
>   id integer NOT NULL,
>   montant real,
>   CONSTRAINT id PRIMARY KEY (id)
> )with oids;
>
> 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;
>
> CREATE OR REPLACE FUNCTION p_commande_bd() RETURNS "trigger" AS
> $BODY$
> BEGIN
>     -- RAISE NOTICE 'Table commandeligne : suppression de la ligne %',
> OLD.id; DELETE FROM commandeligne WHERE id_commande = OLD.id;
>     -- RAISE NOTICE 'Table commandeligne : ligne % supprimée', OLD.id;
>     RETURN OLD;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> 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();
>
> CREATE OR REPLACE FUNCTION p_commandeligne_ad() RETURNS "trigger" AS
> $BODY$
> BEGIN
>     -- RAISE NOTICE 'Table commande : maj de la ligne %', OLD.id_commande;
>     UPDATE commande SET montant=montant-OLD.montant WHERE id =
> OLD.id_commande; -- RAISE NOTICE 'Table commande : ligne % maj (%)',
> OLD.id_commande, OLD.montant;
>     RETURN NEW;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> DROP TRIGGER  IF EXISTS    p_commandeligne_ad ON commandeligne;
> CREATE TRIGGER p_commandeligne_ad  AFTER DELETE ON commandeligne FOR EACH
> ROW EXECUTE PROCEDURE p_commandeligne_ad();
>
>
> -------- First step : Creating first command
> insert into commande(id, montant) values(1,150);
> insert into commandeligne(id_commande,id_produit, montant) values(1,1,100);
> insert into commandeligne(id_commande,id_produit, montant) values(1,2,20);
> insert into commandeligne(id_commande,id_produit, montant) values(1,3,30);
>
> select oid,* from commande where id=1;
> select oid,* from commandeligne where id_commande=1;
>
> -------- 2nd step : Deletion of command 1
> delete from commande where id=1;

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.

>
> select oid,* from commande where id=1;
> select oid,* from commandeligne where id_commande=1;;

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