MySQL Forums
Forum List  »  Stored Procedures

last_insert_id() returns a value on INSERT IGNORE in my stored procedure
Posted by: Roman Agapkin
Date: January 31, 2014 07:35AM

Hello,
first here is what i'm trying to do:
1. i insert entry into a table mediaobject
2. after insert trigger on mediaobject is called
3. trigger calls stored procedure insert Artist
4. in SP two things should be done: insert into artist table if not already there
if row is inserted also make entry to a n:m relationship helping table.

here is my procedure:

CREATE PROCEDURE isooctane_db.insertArtist(IN a_name VARCHAR(255), IN uid INT, OUT artist_id INT)
BEGIN
DECLARE aid INT DEFAULT 0;
INSERT IGNORE INTO artist (artist_name) VALUES (a_name);
set aid = last_insert_id();
IF aid > 0 THEN
INSERT IGNORE INTO user_has_artist(user_id, artist_id) VALUES (uid, aid);
END IF;
SET artist_id = aid;
END

even when a row is beeing ignored, last_insert_id is returning a number>0 and the second insert is trying to insert an entry with this number, which leads to a foreign key constraint error. where does the number comes from?

in documentation it is said, if a row is ignored, 0 is returned by last_insert_id()

best regards,
rome

Options: ReplyQuote


Subject
Views
Written By
Posted
last_insert_id() returns a value on INSERT IGNORE in my stored procedure
5012
January 31, 2014 07:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.