Jump to content


Photo

Using last_insert_id() in Trigger


  • Please log in to reply
1 reply to this topic

#1 clarkdej

clarkdej
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 01 August 2006 - 02:26 AM

I need to take the autoincrement value and add it to the first two letters of the last name, frist two letters of the last name, to create an 8 character barcode value.  Here is my Trigger;

BEFORE INSERT ON patron
FOR EACH ROW
begin
set @namestring = left(new.namel,2);
set @namestring2 = left(new.namef,2);
set @namestring3= last_insert_id();
set new.barcode = concat(@namestring,@namestring2,@namestring3);
end

Problem:  @namestring3 always returns 0.

What is the proper way to do this?

Using mySQL 5.0 with third party app generating insert.  Connector is 3.51.12

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 August 2006 - 02:33 PM

I've never used triggers, but I've also never seen auto-increment used this way.  Don't you need to run "SELECT LAST_INSERT_ID()"?  Also, if you're doing this _before_ the INSERT, doesn't this mean that you can never get the new UID since you haven't actually run an insert yet?  Maybe "AFTER INSERT" is more appropriate?  Again, I'm not a trigger expert, just a hunch.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users