I believe this trigger below should work for you. If you insert with the sequence number null, then it'll make it one more than the current max, or 1 if the table's empty. If you explicitly give it a sequence number then the trigger doesn't fire and your specific sequence number either goes in or fails like normal.
I put in the "select raise(ignore);" bit so that the original insert won't fail with the not null constraint failure. That might cause issues if there get to be more triggers on the table though. https://www.sqlite.org/lang_createtrigger.html "When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would have been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step." create table foo ( date text not null, sequence int not null, something1, something2, something3, primary key (date, sequence) ); create trigger foo_insert_trg before insert on foo for each row when new.date is not null and new.sequence is null begin insert into foo values ( new.date, coalesce((select max(sequence) from foo where date = new.date), 0) + 1, new.something1, new.something2, new.something3 ); select raise(ignore); end; sqlite> delete from foo; sqlite> insert into foo values (date(), null, 'A', 'A', 'A');--empty case sqlite> insert into foo values (date(), null, 'B', 'B', 'B');--with something in there sqlite> select * from foo; QUERY PLAN `--SCAN TABLE foo date|sequence|something1|something2|something3 2018-07-05|1|A|A|A 2018-07-05|2|B|B|B sqlite> insert into foo values (date(), 1, 'C', 'C', 'C');--giving it an existing sequence number Error: UNIQUE constraint failed: foo.date, foo.sequence sqlite> insert into foo values (date(), 7, 'D', 'D', 'D');--giving it a specific new sequence number sqlite> insert into foo values (date(), null, 'E', 'E', 'E');--should continue past the new high sequence number sqlite> select * from foo; QUERY PLAN `--SCAN TABLE foo date|sequence|something1|something2|something3 2018-07-05|1|A|A|A 2018-07-05|2|B|B|B 2018-07-05|7|D|D|D 2018-07-05|8|E|E|E -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof Sent: Thursday, July 05, 2018 12:00 AM To: SQLite mailing list Subject: Re: [sqlite] Automatic numbering 2018-07-05 5:37 GMT+02:00 Simon Slavin <slav...@bigfraud.org>: > On 5 Jul 2018, at 3:22am, Cecil Westerhof <cldwester...@gmail.com> wrote: > > > I only want to store a date with a record. But it is possible that more > as > > one record will be inserted, so I want to use another field to use as an > > index. So that the first gets an one, the second a two, etc. > > Is this possible, or do I just have to check if there is already a date > and > > fetch the highest index and increase this with one? > > There's no magical shortcut. > > I would create an index on (theDate, dateEventNumber). Then do > > BEGIN > SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1 > [ in your code see whether you got NULL back, substitute 0 ] > INERT INTO MyTable ... ?1 + 1 > COMMIT > > You can combine the two commands into one more complicated thing, but I'd > do that only if I was sure nobody would ever have to figure out why my code > wasn't working. > OK, thank you. I am going to play with it. -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
