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
