I am trying to figure out how to determine the primary key, an Auto Increment field, of a row, after it is inserted into the database. I know the standard answer to this is to use LAST_INSERT_ID(), but I believe my database design is causing this to not work for me. Let me explain a little bit.
Let's say I have two tables, calendar and event, that I would like to have as sub-types of a more generic table calendar_base. The main reason for doing this is to simplify the relations with other tables in the DB. So the calendar_base table generates the auto incremented primary key, which is used as a foreign key in the calendar and event tables. In order to ease inserts into these tables, I have a before insert trigger on the calendar and event tables that inserts a row in the calendar_base table and set the foreign key in the calendar and event tables to LAST_INSERT_ID() .
So when I insert the rows into calendar and event tables, LAST_INSERT_ID() returns 0, presumably since if is within the trigger, which runs in a different "context" than my insert query, that the auto increment is generated and therefore my connection does not have a valid LAST_INSERT_ID().
Is there anything shy of a store procedure that will allow me to grab the ID that I need? Am I missing a simple change that will let this work better?