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@xxxxxxxxxx
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
