> > Contrary to what occurs in other engines, SQLite seems to
> > assert constraints at insert/delete time, ignoring the fact that
> > (insert or delete) trigger will increment or decrement the upper part
> > of the tree interval on HI and LO keys (in the case of a nested tree).
>
>This /should/ be handled properly if you make appropriate use of the
>BEFORE or AFTER words in your TRIGGERs. Can you verify whether you
>are doing so in this case ?
Yes, in the case of nested trees, we need to update LO first on delete,
but HI first on insert. The following raise a constraint error, which
disappears when Unique constraints are removed. More precisely, the
constraint is raise on HI being "not unique" on delete, while LO is
deemed "non unique" on insert. Removing both constraints works and
shows that if the constraints were checked after the relevant trigger
action, nothing would be in violation.
CREATE TABLE "Tree" (
"lo" INTEGER NOT NULL,
"hi" INTEGER NOT NULL,
"value" TEXT DEFAULT '');
CREATE TRIGGER "tgDelNode"
AFTER DELETE
ON "Tree"
FOR EACH ROW
BEGIN
delete from tree where lo > old.lo and hi < old.hi;
update tree set lo = lo - (old.hi - old.lo + 1) where lo >=
old.lo;
update tree set hi = hi - (old.hi - old.lo + 1) where hi >= old.lo;
END;
CREATE TRIGGER "tgInsLeaf"
BEFORE INSERT
ON "Tree"
FOR EACH ROW
BEGIN
update tree set hi = hi + 2 where hi >= new.lo;
update tree set lo = lo + 2 where lo >= new.lo;
END;
CREATE UNIQUE INDEX [ixHi] ON [Tree] ([hi]);
CREATE UNIQUE INDEX [ixLo] ON [Tree] ([lo]);
To make this ready to work, insert a root element as
insert into Tree values (1, 2, 'root');
then insert some nodes:
insert into tree values (2, 3, 'node A');
insert into tree values (4, 5, 'node B');
insert into tree values (6, 7, 'node C');
then some leaves:
insert into tree values (3, 4, 'leaf 1');
insert into tree values (5, 6, 'leaf 2');
insert into tree values (9,10, 'leaf a');
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
