In my app (a perl/web-based on-line training system), I have a table of users
with an integer primary key column, tech_id. The tech_ids are created by a
foreign system, and either imported with other data, or inserted as-received by
my app.
In enhancing the app, I'm finding it desirable to insert self-registered
technician candidates in this table with a tech_id that's outside the sequence
of the current tech_id, a temporary tech ID.
When the tech passes the on-line exam, the tech_id would be updated with the
permanent tech_id from the foreign DB. I want to have SQLite generate these
temporary IDs.
I'm looking for suggestions on how to do this with SQLite3.
As a test case, I came up with this scanario:
create table t1 (a integer primary key autoincrement, b text);
create trigger deleteme after insert on t1
begin
delete from t1 where a = new.a;
end;
insert into t1 values (null, 'a');
then get the last_insert_rowid
which seems to work. The table itself never holds any data, but, by virtue of
'autoincrement', SQLite keeps track of the next value for column, "a".
So, finally, my questions: What's wrong with this scheme? How bulletproof is
it? What would work better?
Thanks!
-Clark
