Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


SQL Transaction - SQL COMMIT ROLLBACK SAVEPOINT


SQL Transaction Control (TC) commands are used to manage database transaction. SQL transaction command use with DML statement for INSERT, UPDATE and DELETE.

DML statement are store into SQL buffer until you execute Transaction commands. Once you execute transaction commands its store permanent to a database.


SQL COMMIT

SQL COMMIT command save new changes store into database.

Syntax :

COMMIT;

Example Statement :

SQL> COMMIT;

Commit complete.

SQL SAVEPOINT

SQL SAVEPOINT command create new save point. SAVEPOINT command save the current point with the unique name in the processing of a transaction.

Syntax :

SAVEPOINT savepoint_name;

Example Statement :

SQL> CREATE TABLE emp_data (
	no NUMBER(3), 
	name VARCHAR(50), 
	code VARCHAR(12)
	);

Table created.

SQL> SAVEPOINT table_create;

Savepoint created.

SQL> insert into emp_data VALUES(1,'Opal', 'e1401');

1 row created.

SQL> SAVEPOINT insert_1;

Savepoint created.

SQL> insert into emp_data VALUES(2,'Becca', 'e1402');

1 row created.

SQL> SAVEPOINT insert_2;

Savepoint created.

SQL> SELECT * FROM emp_data;

        NO NAME                                       CODE
---------- ------------------------------------------ ------------
         1 Opal                                       e1401
         2 Becca                                      e1402

SQL ROLLBACK

SQL ROLLBACK command execute at the end of current transaction and undo/undone any changes made since the begin transaction.

Syntax :

ROLLBACK [To SAVEPOINT_NAME];

Example Statement :

Above example we are create 3 SAVEPOINT table_create, insert_1 and insert_2. Now we are rollback to insert_1 SAVEPOINT.

SQL> ROLLBACK TO insert_1;

Rollback complete.

SQL> SELECT * FROM emp_data;

        NO NAME                                       CODE
---------- ------------------------------------------ ------------
         1 Opal                                       e1401