On 3/13/2012 11:41 AM, Marco Bambini wrote:
I have a table defined like:
foo (id INTEGER, pid INTEGER, oid INTEGER)
id is an unique identification number
pid is parent_id
oid is order_id
and with some data like:
id pid oid
74 1 1
42 1 2
28 0 1
1 1 3
17 0 2
16 2 1
9 2 3
3 2 4
10 2 2
within the same pid (parent_id) I need a way to automatically keep oid
(order_id) sorted and oid is very volatile, it will change frequently, users
will add or remove items and reorder the items.
So in this example if an user add a new item with pid 1 at position 2 then the
old positions 2 and 3 must be incremented by 1.
Trigger should take care of automatically reordering oid within the same pid in
case of INSERT, UPDATE and DELETE.
Please note that if the trigger is too complicated then I could just execute a
smart sql statement every time table foo changes.
Are you looking for something like this?
create trigger foo_insert after insert on foo
begin
update foo set oid=oid+1 where pid = new.pid and id != new.id and oid
>= new.oid;
end;
create trigger foo_delete after delete on foo
begin
update foo set oid=oid-1 where pid = old.pid and oid > old.oid;
end;
I'd probably not use a trigger on update, otherwise it will be triggered
by insert and delete triggers. Instead, you could run something like
this after oid is updated to a greater value:
update foo set oid=oid-1 where pid = :updatePid and id != :updateId and
oid between :oldOid and :newOid;
The matching request for downward adjustment is left as an exercise for
the reader.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
