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
set @namestring = left(new.namel,2);
set @namestring2 = left(new.namef,2);
set @namestring3= last_insert_id();
set new.barcode = concat(@namestring,@namestring2,@namestring3);
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
Using last_insert_id() in Trigger
1 reply to this topic
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