COMMI,ROLLBACK and SAVEPOINT are TCL Commands.
TCL Means Transnational Control Language.
These commands play important role only when action takes place on the table using DML Commands.
Commit : This is used only after any DML Operations performed on a table in order to insert or delete or update the data permanently
Rollback: This is used only after any DML Operations performed on a table in order to remove the previous transactions taken place on any table.
Note:After Commit there is no point of using rollback.
DDL Commands are Auto commit...(very important to remember this point)
COMMIT makes permanent the changes resulting from all SQL statements in the transaction.
The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
Syntax:
ROLLBACK [WORK] TO SAVEPOINT < marker_name >;
Savepoint : Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
SAVEPOINTS are used to subdivide a transaction into smaller parts
It enables rolling back part of a transaction. Maximum of five save points are allowed.
Syntax:
SAVEPOINT < marker_name >;
Ex:
BEGIN
INSERT INTO temp VALUES (1,1 ‘ROW 1’); SAVEPOINT A;
INSERT INTO temp VALUES (2,2 ‘ROW 2’); SAVEPOINT B ;
ROLLBACK TO SAVEPOINT B;
COMMIT ;
END;
TCL Means Transnational Control Language.
These commands play important role only when action takes place on the table using DML Commands.
Commit : This is used only after any DML Operations performed on a table in order to insert or delete or update the data permanently
Rollback: This is used only after any DML Operations performed on a table in order to remove the previous transactions taken place on any table.
Note:After Commit there is no point of using rollback.
DDL Commands are Auto commit...(very important to remember this point)
COMMIT makes permanent the changes resulting from all SQL statements in the transaction.
The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
Syntax:
ROLLBACK [WORK] TO SAVEPOINT < marker_name >;
Savepoint : Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
SAVEPOINTS are used to subdivide a transaction into smaller parts
It enables rolling back part of a transaction. Maximum of five save points are allowed.
Syntax:
SAVEPOINT < marker_name >;
Ex:
BEGIN
INSERT INTO temp VALUES (1,1 ‘ROW 1’); SAVEPOINT A;
INSERT INTO temp VALUES (2,2 ‘ROW 2’); SAVEPOINT B ;
ROLLBACK TO SAVEPOINT B;
COMMIT ;
END;
No comments:
Post a Comment