Thanks,
David Y. Hodge
On 9/15/06, Crocker, Patrick <[EMAIL PROTECTED]> wrote:
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
