When I insert something into table A, I want to use a trigger to insert into table B, get the auto increment and use it as the id in table A. That’s pretty doable. However when I call last_insert_id() outside of the trigger, the returned value is NOT the id I’ve just inserted (I think it’s the last id that was automatically generated).
While you CAN call last_insert_id() with an argument to explicitly set the value, its scope is limited to the trigger, so the value no longer applies in my application code.
Is there any way to do this and still have my application code call last_insert_id?
Possibly, but I need to be working with the surrogate key. Unfortunately. If it can’t be done at the database level I can work around it at the application level.
if there was a natural key, you would just use it to SELECT the row you just inserted, and in so doing you’d also get the value that was assigned to the auto_increment column,
so, your application wouldn’t have to use last_insert_id()
a natural key is by definition reliable (and unique)