Hi everyone,
I passed last week the SQL Fundamentals and now I'm studying for the SQL Expert exam (1Z0-047). I knew about SAVEPOINT and today I was testing something on transactions. I never needed SAVEPOINT in a real situation. So I don't understand them very well. So I was wondering if anyone can explain to me how SAVEPOINT works in a transaction.
Here is a small test case...
create table table#1
(
id number(10, 0) primary key,
var varchar2(10) not null
);
insert into table#1 values(1, 'var 1');
insert into table#1 values(2, 'var 2');
commit;
In my table, there will be 2 records and my transaction is over (it is commited). So far, nothing new.
Now, I add 2 new records into that same table and create a Savepoint S1.
insert into table#1 values(3, 'var 3');
insert into table#1 values(4, 'var 4');
SAVEPOINT S1;
I add 2 new records on my table
insert into table#1 values(5, 'var 5');
insert into table#1 values(6, 'var 6');
And now if I try to rollback to my SAVEPOINT S1 using the following rollback command:
rollback to s1;
It rollbacks the transaction completly and not only the part after the savepoint. Can anyone tell me why ?
Thanks in advance,