> How do you renumber an index anyway?
Hmm ... given the tables
work (idx, sortcrit)
storeroom(rowcount integer)
where 'idx' may be the index to renumber and 'sortcrit' a sorting criteria.
I would try to do something like the code below, but I just remembered that
sqlite doesn't support recursive triggers, therefore this won't work :(
-- 8< --
CREATE TRIGGER renumber_work_table_index AFTER UPDATE OF storeroom
WHEN new.rowcount >= 0
BEGIN
UPDATE work
SET idx = (SELECT rowcount FROM storeroom) + 1,
color = idx % 2
WHERE sortcrit == (SELECT (min(sortcrit) WHERE idx == 0) AND idx == 0;
/* stop if storeroom.rowcount equals the number of rows in work */
UPDATE storeroom
SET rowcount = CASE
WHEN (SELECT rowcount FROM storeroom) == SELECT count(*) THEN -1
ELSE (SELECT rowcount FROM storeroom) + 1
END;
END;
/* fires the renumber_work_table_index-trigger */
CREATE TRIGGER catch_inserts AFTER INSERT ON work
BEGIN
UPDATE work SET idx = 0;
UPDATE storeroom SET rowcount = 0;
END;
/* fires the renumber_work_table_index-trigger */
CREATE TRIGGER catch_deletes AFTER DELETE ON work
BEGIN
UPDATE work SET idx = 0;
UPDATE storeroom SET rowcount = 0;
END;
-- 8< --
Cheers
Daniel
--
Dipl.-Math. (FH) Daniel Franke
Institut fuer Medizinische Biometrie und Statistik
Medizinische Universität zu Luebeck
Ratzeburger Allee 160, Haus 4
23538 Luebeck
Telefon: 0451-500-2786
Telefax: 0451-500-2999
[EMAIL PROTECTED]
