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
