You can get your primary key in the insert statement:
insert into <table> values (...) returning <your id col> into <variable>;
--
Have a nice day
Michel
"Mike Krolewski" <mkrolewski_at_rii.com> a écrit dans le message news: 94omee$rb8$1_at_nnrp1.deja.com...
> In article <94o44j$df6$1_at_nnrp1.deja.com>,
> iluzn_at_my-deja.com wrote:
> > i have a trigger that fires and gets the next value from a sequence
and
> > puts it into my primary key field before insert.
> >
> > i want to fire the trigger, updating the sequence and place the value
> > into the field. after that has been done i want to retrieve the
current
> > value of the sequence and using it in my VB application.
> >
> > I should (correct me if im wrong) be able to figure out how to do all
> > of this with very little hassles and need for help.
> >
> > my problem is that in the time between when the table insert occured
> > and the time when the SQL retrieves the current sequence value to be
> > used for proccessing in my VB application it is possible for another
> > client to fire another trigger. if the 2nd trigger fires before the
> > current sequence value of the first trigger fire is retrieved then i
am
> > going to get the value of the second fire for the first and second
> > instance.
> >
> > a little tricky to explain... but is there a SQL call to halt the
> > sequence and stop it from being incremented until its value has been
> > retrieved by a SQL call and fed to my VB app.
> >
> > step of event(to explain what i mean)
> >
> > -- sql call to insert
> > -- trigger fired(sequence incremented)
> > -- SQL call to get current value of sequence
> >
> > potential problem (with user(1) and user(2))
> >
> > -- (1)SQL call to insert
> > -- (1)trigger fired(sequence incremented)(current value: 5)
> > -- (2)SQL call to insert
> > -- (2)trigger fired(sequence incremented)(current value: 6)
> > -- (1)SQL call to get current value of sequence(value returned: 6)
> > -- (2)SQL call to get current value of sequence(value returned: 6)
> >
> > what i would like to achieve
> >
> > -- sql call to insert
> > -- trigger fired(sequence incremented, sequence halted)
> > -- SQL call to get current value of sequence
> >
> > an ideal set of procedures
> >
> > -- (1)SQL call to insert
> > -- (1)trigger fired(sequence incremented,sequence halted)(current
> > value: 5)
> > -- (2)SQL call to insert
> > -- (2)trigger fired(sequence on hold)
> > -- (1)SQL call to get current value of sequence(value returned: 5)
> > -- (1) sequence re-activated
> > -- (2)trigger fired(sequence incremented)(current value 6)
> > -- (2)SQL call to get current value of sequence(value returned: 6)
> >
> > this is given to let you get a better gist of my problem. can anyone
> > think of a way to get the current sequence value so that it doesnt
> > return a false value due to another trigger incrementing it?
> >
> > i would realy appreciate it, thanks :)
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> You are correct. Trying to look at a sequence will eventually be
> incorrect. There is no equivalent to locking or transaction specific
> values for a sequence. Thus everyone is on their own when trying to get
> a sequence.
>
> Obviously, every request for a sequence get a unique sequence.
>
> There are two solutions.
>
> Solution One: Retrieve the sequence from the record that you just
> inserted in to the table.
>
> insert into <table> ...
>
> select <sequence column> from <table> where...
>
>
> Solution Two: Retrieve the sequence from the record using the
> 'returning'?? clause.
>
> Oracle has extended SQL to have a value returned when the statement is
> executed. I want to say the wording is 'returning' but without my
> manuals I am just guessing. It is a new feature and should be easy to
> locate. Check the syntax for insert and/or update statements.
>
> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Thu Jan 25 2001 - 03:22:00 CST
