Thread: SEQUENCE in a ROLLBACK
10-16-14, 18:52 #1Registered User
Provided Answers: 1
- Join Date
- Jan 2013
Unanswered: SEQUENCE in a ROLLBACK
I am not sure about the ANSI/ISO rules on this; anyone know? We have a SEQUENCE and insert it into a table.
CREATE SEQUENCE Cheque_Seq
START WITH 1
INCREMENT BY 1;
CREATE TABLE Cheque_Book
(cheque_seq INTEGER*NOT NULL PRIMARY KEY,
cheque_amt DECIMAL (15,2) DEFAULT 0.00 NOT NULL);
If I do an insertion with the SEQUENCE in a transaction and then ROLLBACK the transaction, what happens to the sequence value? My thought is that the value should reset to the state it was in before the transaction (basic definition of a ROLLBACK). But it does not in the products I have looked at.
10-16-14, 22:10 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Following your logic of the behavior of a ROLLBACK on a SEQUENCE object, should the other transactions which have used later values in the SEQUENCE be renumbered by the ROLLBACK? I'm pretty sure that would be a disaster!
As far as I'm concerned, a SEQUENCE is a programming artifact. It is a pure SK (Surrogate Key) and its value should not be affected by a ROLLBACK operation any more than an NK (Natural Key) would be altered by the ROLLBACK.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.