Re: sequences and triggers

Or...at the beginning of your trigger you could select the next value of the sequence and put it into a package variable. You could use the package variable as your key value and still have it available in the package for use by other blocks in the same session.

Van

"Michel Cadot" <micadot_at_netcourrier.com> wrote in message news:94or8l$fi5$1_at_s1.read.news.oleane.net...
> 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 - 19:10:55 CST