Triggers for a MPTT based table - Mailing list pgsql-general

Hi all,

I wish to use a MPTT based table to store some hierachical datas. Here
is my table definition:

CREATE TABLE region (
        id SERIAL NOT NULL,
        full_path VARCHAR(255) NOT NULL,
        lhs INTEGER NOT NULL,
        rhs INTEGER NOT NULL,
        level INTEGER NOT NULL,
        parent_id INTEGER,
        PRIMARY KEY (id),
         FOREIGN KEY(parent_id) REFERENCES region (id) ON DELETE cascade
)

I'm trying to manage lhs, rhs and level with triggers.

Here are my triggers:


CREATE OR REPLACE FUNCTION  update_tree_oninsert() RETURNS TRIGGER AS $BODY$
BEGIN
    IF NEW.parent_id != 0 THEN
        UPDATE region SET level = (select level from region where id =
NEW.parent_id)+1 where id = NEW.id;
        UPDATE region SET lhs = (select rhs from region where id =
NEW.parent_id) where id = NEW.id;
        UPDATE region SET rhs = lhs + 1 where id = NEW.id;
        UPDATE region SET rhs = rhs + 2 WHERE rhs >= (select rhs from
region where id = NEW.parent_id) and id != NEW.id;
        UPDATE region SET lhs = lhs + 2 WHERE lhs >= (select rhs from
region where id = NEW.id) and id != NEW.id;
    END IF;
    RETURN NEW ;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

CREATE TRIGGER  add_upd
     AFTER INSERT ON  region
     FOR EACH ROW EXECUTE PROCEDURE  update_tree_oninsert();



CREATE OR REPLACE FUNCTION  update_tree_ondelete() RETURNS TRIGGER AS $BODY$
BEGIN
    UPDATE region SET lhs = lhs - (OLD.rhs-OLD.lhs+1) WHERE lhs > OLD.rhs;
    UPDATE region SET rhs = rhs - (OLD.rhs-OLD.lhs+1) WHERE rhs > OLD.rhs;
    RETURN OLD ;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

CREATE TRIGGER  del_upd
     AFTER DELETE ON  region
     FOR EACH ROW EXECUTE PROCEDURE  update_tree_ondelete();




So here is my problem:

When I insert data in the table region, the trigger on insert is OK but
as soon as I try to remove an entry from the database

For example:

 id |                full_path                | lhs | rhs | level |
parent_id
----+-----------------------------------------+-----+-----+-------+-----------
  1 | world                                   |   1 |  70 |     0 |
  2 | world/continent0                        |   2 |  67 |     1
|         1
  3 | world/continent0/country0               |   3 |  34 |     2
|         2
  4 | world/continent0/country0/region0       |   4 |  13 |     3
|         3
  5 | world/continent0/country0/region0/city0 |   5 |   6 |     4
|         4
  6 | world/continent0/country0/region0/city1 |   7 |   8 |     4
|         4
  7 | world/continent0/country0/region0/city2 |   9 |  10 |     4
|         4
  8 | world/continent0/country0/region0/city3 |  11 |  12 |     4
|         4
  9 | world/continent0/country0/region1       |  14 |  23 |     3
|         3
 10 | world/continent0/country0/region1/city0 |  15 |  16 |     4
|         9
 11 | world/continent0/country0/region1/city1 |  17 |  18 |     4
|         9
 12 | world/continent0/country0/region1/city2 |  19 |  20 |     4
|         9
 13 | world/continent0/country0/region1/city3 |  21 |  22 |     4
|         9
 14 | world/continent0/country0/region2       |  24 |  33 |     3
|         3
 15 | world/continent0/country0/region2/city0 |  25 |  26 |     4
|        14
 16 | world/continent0/country0/region2/city1 |  27 |  28 |     4
|        14
 17 | world/continent0/country0/region2/city2 |  29 |  30 |     4
|        14
 18 | world/continent0/country0/region2/city3 |  31 |  32 |     4
|        14
 19 | world/continent0/country1               |  35 |  66 |     2
|         2
 20 | world/continent0/country1/region0       |  36 |  45 |     3
|        19
 21 | world/continent0/country1/region0/city0 |  37 |  38 |     4
|        20
 22 | world/continent0/country1/region0/city1 |  39 |  40 |     4
|        20
 23 | world/continent0/country1/region0/city2 |  41 |  42 |     4
|        20
 24 | world/continent0/country1/region0/city3 |  43 |  44 |     4
|        20
 25 | world/continent0/country1/region1       |  46 |  55 |     3
|        19
 26 | world/continent0/country1/region1/city0 |  47 |  48 |     4
|        25
 27 | world/continent0/country1/region1/city1 |  49 |  50 |     4
|        25
 28 | world/continent0/country1/region1/city2 |  51 |  52 |     4
|        25
 29 | world/continent0/country1/region1/city3 |  53 |  54 |     4
|        25
 30 | world/continent0/country1/region2       |  56 |  65 |     3
|        19
 31 | world/continent0/country1/region2/city0 |  57 |  58 |     4
|        30
 32 | world/continent0/country1/region2/city1 |  59 |  60 |     4
|        30
 33 | world/continent0/country1/region2/city2 |  61 |  62 |     4
|        30
 34 | world/continent0/country1/region2/city3 |  63 |  64 |     4
|        30
 35 | world/continent_otot                    |  68 |  69 |     1
|         1

But as soon I'm trying to delete an entry I get this error about
constraint being violated:

Does anybody have any clue ?


--
Laurent RAHUEL,
Chef de Projet
______________________________________________________________________
net-ng
14 rue Patis Tatelin, Bât G        Web : http://www.net-ng.com
35700 RENNES                       e-mail: laurent.rahuel@net-ng.com
FRANCE                             Tel : 02.23.21.21.50
_______________________________________________________________________
Ce message et tout document joint sont confidentiels. Toute diffusion
ou publication en est interdite. Si vous recevez ce message par erreur,
merci d'en avertir immédiatement l'expéditeur par e-mail et de
supprimer ce message et tout document joint.
_______________________________________________________________________
This message and any attachment are confidential. Any use is prohibited
except formal approval. If you receive this message in error, please
notify the sender by return e-mail and delete this message and any
attachment from your system.
_______________________________________________________________________