Home » RDBMS Server » Server Administration » How to keep a sequence value unchanged when rollback occurs
How to keep a sequence value unchanged when rollback occurs [message #370677] Tue, 18 January 2000 18:39 Go to next message
J Zhang
Messages: 1
Registered: January 2000
Junior Member
People,

I have a question here. If you have a trigger like
following,
create trigger abc after insert or update or delete
on table1
begin
insert into table2 values (seq.nextval);
end;

Now after you do insert into table1, you want to rollback this transaction. Obviously, there will
be no new row inserted into table2. However, the
current value of sequence 'seq' has got increased.
Is there any work-around to avoid this situation?
Whoever asks this is looking for eliminating the gap
in the sequence value when there are 'rollback' on
the original transactions.

Thanks,

JZ
Re: How to keep a sequence value unchanged when rollback occurs [message #370685 is a reply to message #370677] Wed, 19 January 2000 05:17 Go to previous messageGo to next message
M. Armaghan Saqib
Messages: 7
Registered: January 2000
Junior Member
The only way to do this (according to my knowledge) is to use a table to implement sequences.

Get sequence from the table, update and lock the row (using FOR UPDATE) which will be released upon commit.

But this locking will prevent other users to get next sequence number for the duration of this transaction.

Oracle sequences were created to avoid this locking problem.

regards,
M. Armaghan Saqib
------------------------------------------------------------------------
* SQL PlusPlus: Add power to SQL Plus.
* SQL Link for XL: Integrate Oracle with XL.
* Oracle CBT with sample GL (Triggers and Forms Source Code included.
Download free: http://www.geocities.com/armaghan/
------------------------------------------------------------------------
Re: How to keep a sequence value unchanged when rollback occurs [message #370698 is a reply to message #370677] Thu, 20 January 2000 11:42 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
What about selecting the max value from the table and not using a sequence?

Also be aware don't use the cache parameter on th e sequence as the cache values are flushed on database stop and restart and this leaves big gaps
Previous Topic: Delete file created bt UTL_FILE function.
Next Topic: REF CURSOR related
Goto Forum:
  


Current Time: Tue Oct 20 10:19:14 CDT 2020