Search Postgresql Archives

Triggers for a MPTT based table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux