?
Solved

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

Posted on 2013-11-25
1
Medium Priority
?
780 Views
Last Modified: 2013-12-09
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?
0
Comment
Question by:SterlingMcClung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 11

Accepted Solution

by:
Amar Bardoliwala earned 1500 total points
ID: 39676704
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question