Get Auto Increment Primary Key from row inserted into MySQL database using ADO

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?
LVL 7
SterlingMcClungAsked:
Who is Participating?
 
Amar BardoliwalaConnect With a Mentor Commented:
Hello SterlingMcClung,

I am not master of triggers and stored procedures but check following.

did you check that your trigger works fine and you are getting row inserted in your calendar_base table with new incremented primary key for your row?

are you calling trigger before running a stored procedure?

one solution can be that you put your insert statement in your stored procedure that your are running, so all will be in same context and you can get last_insert_id

see if this helps you.

Thank you.
Amar Bardoliwala
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.