ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Crocker, Patrick" <patrick.croc...@verizon.com>
Subject RE: Obtaining Generated Primary Keys
Date Fri, 15 Sep 2006 13:55:08 GMT
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:dyhodge@gmail.com] 
Sent: Friday, September 15, 2006 8:37 AM
To: user-java@ibatis.apache.org
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 <patrick.crocker@verizon.com> 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:dyhodge@gmail.com]
	Sent: Friday, September 15, 2006 6:58 AM
	To: user-java@ibatis.apache.org
	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
	
	



Mime
View raw message