On 1 Nov 2013, at 2:15am, Igor Korot <ikoro...@gmail.com> wrote: > When the record is inserted with the field1 as NULL, I want to have > the field1 to have value max( field1 ) + 1. > When the record inserted have some value in field1, i.e. field1 == 5, > I want all records that have field1 > 5 to have field1 to be > incremented. So if new record have field1 as 5, so the old record with > field1 == 5 will have field1 = 6, field1 will become 7 and so forth.
You are trying to fake an explicit order for your rows by doing some very complicated things when you insert a new row. This is not a plausible way to handle a database and consequently it's very difficult in SQL. It can also require lots of processing and disk activity: what happens when you insert a new row with field1 = 1 in a table which has 10000 entries ? Pretend triggers didn't exist and try to figure out how you would usefully have SQL order your rows for you. Go back to first principles: what are you really trying to do ? If you're just allowing your users to rearrange roworder, use REAL values instead of INTEGERs for the roworder field, and when you insert a new row between two existing ones, just give it a roworder of the mean of the two rows surrounding it. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
