In that case, you may be able to do it as anonymous pl/sql. But I think I'd try the selectKey way first...but that anon pl/sql might be faster - just one call to the database instead of two.
Bah, try them both, and use what works best for you. ;-) Larry On 9/15/06, David Hodge <[EMAIL PROTECTED]> wrote:
The trigger creates the Primary Key before the INSERT. There is no sequence and if I did specify the key it would be overridden by the trigger. I thought I could maybe use the RETURNING INTO clause where I specify a variable to return the primary key into. The SQL works but I am not sure how to get it back so that iBatis can use it or map it. Thanks, David Y. Hodge On 9/15/06, Crocker, Patrick <[EMAIL PROTECTED]> wrote: > An immediate call to "SELECT your_sequence_name.currval FROM dual" after > the insert? > > Or... > > How is the trigger setup? Does it override the value if you specify it > yourself? > > This trigger (from > http://www.oracle-base.com/articles/8i/AutoNumber.php ) will get the next > sequence if NOT specified in the insert. This would allow you to get > the nextval yourself and pass it in to the insert statement: > > CREATE OR REPLACE TRIGGER dept_bir > BEFORE INSERT ON departments > FOR EACH ROW > WHEN (new.id IS NULL) > BEGIN > SELECT dept_seq.NEXTVAL > INTO :new.id > FROM dual; > END; > / > > - Patrick. > > ________________________________ > > From: David Hodge [mailto:[EMAIL PROTECTED] > Sent: Friday, September 15, 2006 8:37 AM > To: [email protected] > Subject: Re: Obtaining Generated Primary Keys > > > The Problem is that I can not use a sequence, as the key is generated > via a Trigger. Unfortunately, I do not have control over this trigger. > Otherwise, what you said would work perfectly. > > Thanks, > > David Y. Hodge > > > On 9/15/06, Crocker, Patrick <[EMAIL PROTECTED]> wrote: > > Using Oracle Sequences, you need to obtain the key first, then > use it in > the INSERT statement: > > <select id="getNextId" resultClass="decimal"> > SELECT your_sequence_name.nextval AS nextid FROM dual > </select > > <insert id="insert" parameterClass="myTable"> > INSERT INTO my_table > (MY_ID, MY_COLUMN) > VALUES > (#myId#, #myColumn#) > </insert> > > - Patrick. > > ________________________________ > > From: David Hodge [mailto:[EMAIL PROTECTED] > Sent: Friday, September 15, 2006 6:58 AM > To: [email protected] > Subject: Obtaining Generated Primary Keys > > > I am using Oracle and I am wondering if there is a way to obtain > a > trigger generated Primary key after doing an INSERT with iBatis. > > I know there is a selectKey function but that seems to work only > if you > have a select statement, which makes sense. Other databases > have ways > for you to select the last_generated_id() but I do not think > Oracle has > this capability. > > Has anyone else had this problem or know of a work around? > > > Thanks, > > David Y. Hodge > > > > >
