Solved

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

Posted on 2013-11-25
1
766 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
1 Comment
 
LVL 11

Accepted Solution

by:
Amar Bardoliwala earned 500 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to update the value of duplicated records (except latest one) 2 24
Mysql Left Join Case 10 70
Bot attack question 11 43
FTP server backups 5 8
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Running classic asp applications under Windows Server 2008 R2 (x64) and IIS 7 is not as easy as one may think. It took me a while to figure it out while getting error 8002801d a few times. After you install the OS you will need to install the fol…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

860 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