Save point

WORK  :   work is optional is provided by ANSI compatibility
SAVEPOINT:   is optional and is used to rollback a partial transaction, as far as the specified save point
SAVEPOINT
            Savepoint marks and save the current point in the processing of a transaction . When the savepoint is used with a ROLLBACK statement, parts of a transaction can be undone an active savepoint is one that is specified since the last COMMIT or ROLLBACK.
Syn:- SAVEPOINT savepointname
ROLLBACK can be fired from the sql prompt with or without the savepoint clause. The implementation of each is described below.
 A ROLLBACK operation  with out the savepoint clause amounts to the following.

• Ends the transaction
• Undoes all the changes in the current transaction.
• Erases all savepoints in that transaction
• Releases the transactional locks.

A ROLLBACK operation with the to SAVEPOINT clause amounts to the following

• A predetermined portion of the transaction is rolled back.
• Retains the save point rolled back to, but loses those created after the named savepoint.
• Releases all transactional locks that were acquired since the savepoint was taken.

Ex:-     1) insert into vision values(111,’tanuja’,210);
2) select * from vision;
3) rollback;
4) select * from vision;
5) insert into vision values(111,’tanuja’,210);
6) select * from vision;
7) commit;
8) rollback;
9) select * from vision;
10) update vision set sal=1000 where na=’ratna’;
11)  savepoint sav;
12)     select * from vision;
13) update vision set sal =sal +1000 where na=’sekhar’;
14) select * from vision;
15) rollback to savepoint sav;
16)     select * from vision;
17)       rollback;

18)       select * from vision;

For
Online Classes

Contact Us: +919885348743