In a current project, I need to insert records into a history table every time a user creates, modifies, or deletes records in a number of other tables. These “other” tables are set up with an auto-incrementing primary key, and I am currently inserting records into them using prepared statements.
Creating the history records for edit and delete operations is not a problem, since I know the modified/deleted record’s id. But, how do I safely get the auto-incremented id of a record I’ve just inserted in order to then create the appropriate history record with the newly-created record’s id as a foreign key? I’ve seen mention of several techniques for getting the last-used id, but am thinking that might fail in multi-user setting if another user happens to insert a record into the same table in between my insert and my trying to get the last-used id.
We are using SQLite for the initial design and implementation, but will eventually be using cubeSQL server.