Re: sequences and triggers

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/