Re: A simple question, I hope

Jim,

Thanks for the reply. My goal was to continue using a trigger to insert sequence.nextVal, as I didn't want to have ID's inserted through DML statements.

But thinking further about my problem, I may be better off dropping the trigger, and instead instead retrieving sequence values through Java to get the next ID, and including ID in the INSERT statements. Since most (or all) inserts will be done through Java code anyway, this may be the best solution.

Thanks,
Darryl

"Jim Lyons" <jlyons_at_weblyons.com> wrote in message news:e67857c9.0106100937.64f40a95_at_posting.google.com...
> "Darryl" <dfs_at_nospam.com> wrote in message
 news:<x9xU6.5632$c37.668921_at_newsread1.prod.itd.earthlink.net>...
> > Thanks, Reidel, but I guess I didn't specify my question well enough. I
> > already have a sequence, and I already have a trigger that puts the next
> > sequence value into the row.
> >
> > The problem is, at the point I insert the row, I need to know the new
 ID. I
> > need that value returned to the Java program that performed the insert.
 And
> > it's a high-transaction, high-activity system, so I can't do this:
> >
> > 1) INSERT new record
> > 2) SELECT MAX(ID) FROM TABLE
> >
> > because a new record may have been inserted by another transaction
 between 1
> > and 2.
> >
> You can select <seq>.currval from dual. Also, if you have need to use
> the value in several places, you can select <seq>.nextval from dual into
> a variable. Then you can use it as often as you want as well as in your
> original insert statement.
>
> Jim Lyons
>
Received on Sun Jun 10 2001 - 19:47:04 CDT