I think what you propose will work fine, but you could just
do it all yourself with some SQL. Arguably clearer, and no
messing about with complex, possibly non-portable, triggers +
auto-increments.
/* Initialize system */
BEGIN;
CREATE TABLE id_allocator(id INTEGER);
INSERT INTO id_allocator(0);
COMMIT;
/* Retrieve next id in sequence: */
BEGIN;
SELECT id FROM id_allocator; /* This is the id to use */
UPDATE id_allocator SET id = id + 1;
COMMIT; /* Can't use the id until the transaction successfully commits! */
--- Clark Christensen <[EMAIL PROTECTED]> wrote:
> 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
>
>
>
>
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
