On 31/08/2007, Babu, Lokesh <[EMAIL PROTECTED]> wrote: > I was trying the following piece of code (see below), > > The requirement here is very simple, I want the t_id field or ROWID > field to be regenerated sequentially even after delete has been > performed. > <snip>
Hi Lokesh, You can achieve this with a trigger as follows: SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> create table testTbl( t_id integer, t_name text, t_desc text ); sqlite> sqlite> insert into testTbl values( 1, '111', 'd1111' ); sqlite> insert into testTbl values( 2, '222', 'd2' ); sqlite> insert into testTbl values( 3, '3', 'd3' ); sqlite> insert into testTbl values( 4, '4', 'd4' ); sqlite> insert into testTbl values( 5, '5', 'd5' ); sqlite> create trigger testTblTrigger after DELETE on testTbl begin ...> update testTbl set t_id=t_id-1 where t_id>old.t_id; ...> end; sqlite> sqlite> sqlite> select * from testTbl; 1|111|d1111 2|222|d2 3|3|d3 4|4|d4 5|5|d5 sqlite> delete from testTbl where t_id=2; sqlite> select * from testTbl; 1|111|d1111 2|3|d3 3|4|d4 4|5|d5 sqlite> delete from testTbl where t_id>1 and t_id<4; sqlite> select * from testTbl; 1|111|d1111 2|5|d5 sqlite> Rgds, Simon ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
