Jump to content

Archived

This topic is now archived and is closed to further replies.

clarkdej

Using last_insert_id() in Trigger

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.