| How to keep a sequence value unchanged when rollback occurs [message #370677] |
Tue, 18 January 2000 18:39  |
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  |
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/
------------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
