Hi John
You are completely correct, it was just a hasty
example to show that triggers may solve teh problem.
If you want a unique variable you probably have to
use a 1 row table as an index counter.
% cat test.sql
-- example showing how to use an increment unique
value with sqlite
.header on
create table test (
num integer,
num2 integer,
info text,
primary key (num)
);
-- one value index database for unique indexnumber
create table myindex (
id integer,
num integer,
primary key (id)
);
-- trigger which increases num2 with a unique index
create trigger increment_sum_in_test after insert on
test
begin
update myindex set num = num+1 where id=0;
update test set num2 = (
select num from myindex where id=0
) where num=new.num;
end;
-- first set up myindex so the trigger wont complain
insert into myindex (id,num) values (0,0);
insert into test (num,info) values (null,'number
one');
insert into test (num,info) values (null,'number
two');
insert into test (num,info) values (null,'number
three');
delete from test where info='number three';
delete from test where info='number one';
insert into test (num,info) values (null,'number
four');
insert into test (num,info) values (null,'number
five');
select num,info,num2 from test;
$ sqlite3 text.db < test.sql
num|info|num2
2|number two|2
3|number four|4
4|number five|5
--
MortenB
--- John LeSueur <[EMAIL PROTECTED]> skrev:
> what happens If you do:
>
> delete from test where num = 2;
> insert into test (num, info) values(null, 'number
> four');
>
> Wouldn't you have:
>
> num|info|num2
> 1|number one|1
> 3|number three|3
> 3|number four|3
>
> John LeSueur
>
>
