Re: Getting trigger value after insert

One possibility to get the generatedID is using the RETURNING clause:

INSERT INTO t
(attr01, attr02)
VALUES
(:b1,:b2)
RETURNING ID INTO :b3

Another possibility is to get the new ID in advance before making the INSERT. Then your trigger has to be sensible to the fact, that the ID is missing or not.

On Mon, 20 May 2002 00:33:28 GMT, "Marc" <Marc-Weinstock_at_si.rr.com> wrote:

>U must be in the same session that called the insert trigger for this to
>work. If U are using shared pool connections with autocommit on this might
>not work. In the same session that called "seq_name. nextval." call
>"seq_name.currval" This will return the last sequence number used in that
>session. This will work it way back from a trigger since the trigger was
>called in the same session.
>
>select seq_name.currval into ??? from dual;
>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
>news:1021838689.10972.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>>
>> See the FAQ below for ideas.
>> Search for autonumber in the index
>> --
>> Jonathan Lewis
>> http://www.jlcomp.demon.co.uk
>>
>> Author of:
>> Practical Oracle 8i: Building Efficient Databases
>>
>> Next Seminar - Australia - July/August
>> http://www.jlcomp.demon.co.uk/seminar.html
>>
>> Host to The Co-Operative Oracle Users' FAQ
>> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>>
>>
>>
>> Bryon Lape wrote in message <3CE060D0.714A7521_at_noway.com>...
>> >I have an ATG Dynamo 4.5 app running on top of Oracle 8i. I have a
>> >trigger that gets the next number of a sequence and puts the value in an
>> >id column upon row insert (no autogenerated ids in 8i as far as I can
>> >find). I would really like to get the id that was used in the sequence
>> >after the insert. The only column guaranteed to be unique is the id.
>> >Can either Oracle or Dynamo return this value?
>> >
>>
>>
>
>

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon May 20 2002 - 11:22:20 CEST