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

["Stéphane A. Schildknecht" <stephane.schildknecht@xxxxxxxxxxxxxxxx>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I'm faced with something like a comprehension problem.
The exemple may be oversimplified, but, it seems same problem happens with updates.

To simplify, I have triggers on 2 tables (commande and commandeligne).

When deleting from table commande, a trigger fires to delete corresponding
entries in table commandeligne.

When deleting from table commandeligne a trigger fires to update sum of command
(column montant) in table commande.

I'm conscious that an "on delete cascade" on table commande would be really
better, conceptually and logically, but I would like to understand why I don't
get deletion of my tuple in table commande when firing triggers.

The test case I use is as follows :

#####
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;

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

####

Command 1 is still there.

Thanks in advance.

Best regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJL9o+A+REPKWGI0ERAv0gAJ0XU41ZkrjTzm8AL5aG+NtO3m6IOACgsY08
JsTE7QefA+yh87P7V/Lel10=
=3WLn
-----END PGP SIGNATURE-----

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general