[GENERAL] Triggers for a MPTT based table

Tue, 31 Mar 2009 11:44:24 -0700 

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.rah...@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.
_______________________________________________________________________



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














    











					Reply via email to