sequences and triggers

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/ Received on Wed Jan 24 2001 - 20:47:49 CST