Getting back a generated unique id after insert - SOLVED
Posted: Mon Jun 25, 2007 1:00 pm
I have a table that for data integrity reasons has an artificial primary key that's auto-incrementing. To insert into the table, I give it all the other values, and it generates a unique id for the row. For the sake of the situation, this is my job table
I also have a lookup table with some repeating values (a category table), and an intersection table that allows me to link the two in a many:many relationship, using the generated unique id from the job table as the foreign key.
However, to do this link at insert, I need the job_id number, and I'm wondering if there's a quick/dirty/easy way to have this fire back to me after the insert. Trying to do a select using the same data that I inserted isn't working (???), and I don't trust it anyway, as I could conceivably be pulling the wrong row, being that I'm not selecting on the primary key.
The only thought I had was to have a trigger fire on insert, that would put the most recently created id into a (really small) table, but that seems superfluous and prone to potential error.
Any thoughts?
-Dex
I also have a lookup table with some repeating values (a category table), and an intersection table that allows me to link the two in a many:many relationship, using the generated unique id from the job table as the foreign key.
However, to do this link at insert, I need the job_id number, and I'm wondering if there's a quick/dirty/easy way to have this fire back to me after the insert. Trying to do a select using the same data that I inserted isn't working (???), and I don't trust it anyway, as I could conceivably be pulling the wrong row, being that I'm not selecting on the primary key.
The only thought I had was to have a trigger fire on insert, that would put the most recently created id into a (really small) table, but that seems superfluous and prone to potential error.
Any thoughts?
-Dex