Just curious... if the trigger is not using a sequence, what is it using? - Patrick.
________________________________ From: David Hodge [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 9:04 AM To: [email protected] Subject: Re: Obtaining Generated Primary Keys 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 <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
