Re: [sqlite] Autoincrement sequence not updated by UPDATE

[David Raymond]

sqlite> create table tbl1
   ...> (
   ...>   id integer primary key autoincrement,
   ...>   someOtherfield,
   ...>   yetAnotherField
   ...> );

sqlite> create trigger trg_imInChargeAndSayNoAutoincrementUpdates
   ...> before update of id on tbl1
   ...> begin
   ...>   select raise(abort, 'Bad dog, no biscuit.');
   ...> end;

sqlite> insert into tbl1 (someOtherField, yetAnotherField) values (1, 2);

sqlite> select * from tbl1;
id|someField|someOtherField
1|1|2

sqlite> update tbl1 set id = 2 where id = 1;
Error: Bad dog, no biscuit.






sqlite> create table tbl2
   ...> (
   ...>   id integer primary key autoincrement,
   ...>   someOtherField,
   ...>   yetAnotherField
   ...> );

sqlite> create trigger trg_imInChargeAndWantToUpdateMyAutoincrementFields
   ...> after update of id on tbl2
   ...> when new.id > (select seq from sqlite_sequence where name = 'tbl2')
   ...> begin
   ...>   update sqlite_sequence
   ...>   set seq = new.id where name = 'tbl2';
   ...> end;

sqlite> insert into tbl2 (someOtherField, yetAnotherField) values (1, 2);

sqlite> select * from tbl2;
id|someOtherField|yetAnotherField
1|1|2

sqlite> select * from sqlite_sequence;
name|seq
tbl1|1
tbl2|1

sqlite> update tbl2 set id = 5 where id = 1;

sqlite> select * from tbl2;
id|someOtherField|yetAnotherField
5|1|2

sqlite> select * from sqlite_sequence;
name|seq
tbl1|1
tbl2|5
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users