TCL Commands - Commit - Rollback - Save Point in SQL Server .

                              Transaction Control Language (TCL)

Used to control the transactions made against the database.

Commit: 

* Used to make the transaction permanent or changes will be affected permanently in the    table.

 * We can’t rollback the changes after the commit.

Ex:

BEGIN TRANSACTION

DELETE
FROM emp
WHERE empno = 1001

COMMIT

Rollback: 

                     * Used to undo the changes made by any command but only before a commit is done.

                           * We can't Rollback data which has been committed in the database.

                     Ex:

                        BEGIN TRANSACTION

               DELETE
               FROM emp
               WHERE empno = 1002

             ROLLBACK

Save point: 

                      * It is a logical point given by developers between DML statements

                      * Saves the work done up to some point. Used with Rollback.
       
                           BEGIN TRANSACTION

                UPDATE emp
                SET sal = 5000
                WHERE empno = 1001

                SAVE TRANSACTION s1

               UPDATE emp
               SET sal = 5000
               WHERE empno = 1002

               SAVE TRANSACTION s2

               UPDATE emp
               SET sal = 5000
               WHERE empno = 1003 

               ROLLBACK transation s2

                COMMIT