Jay wrote:
Oh. Nifty. I would therefore be safe doing this:
begin immediate;
insert into master_table() values();
insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff';
commit;
But it would fail if I had multiple related tables?
begin immediate;
insert into master_table() values();
insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff';
insert into related_table2( master_id, data ) select last_insert_rowid(), 'wrong id inserted here';
commit;
The last_insert_rowid() would be the id for the last related table not the master table.
Yes, that's how it would work.
This function is normally intended to be used from C code using the API function, or in SQL triggers.
If you need to use the value to link multiple tables on an insert, you need to save it somewhere safe. You could use a dedicated table with a single row like this.
create table last_inserted_master (master_id integer); insert into last_inserted_master values(NULL);
insert into master_table values(...);
update last_inserted_master set master_id = select last_insert_rowid();
insert into related_table1(master_id, data) values((select master_id from last_inserted_master), ...);
insert into related_table2(master_id, other_data) values((select master_id from last_inserted_master), ...);
The last_insert_rowid value is not updated by inserts that occur inside a trigger. Well, actually it is, and those values are available inside the trigger, but the initial value (before the trigger executed) is restored when the trigger ends. When a trigger starts it can read the value of the last_insert_rowid from its calling context (usually the mainline code, but triggers can be nested as well). So, this update code could all be moved into a trigger that fires immediately after the insert. This can be useful for audit trail tables etc., where the information to be stored in the related table can be determined from existing tables and the new row.
HTH Dennis Cote
