BEGIN TRANSACTION (TSQL Statement)

Starts an explicit transaction.

Syntax

BEGIN { TRAN | TRANSACTION } 

Remarks

This statement starts an explicit transaction.

All modifications that subsequent statements will make to tables (insertions, deletions and updates of records) will be recorded in the journal of this transaction.

If a statement raises an error, the batch aborts immediately. If a transaction is still active, a rollback is automatically performed and the pending changes are discarded.

The function @@TRANCOUNT returns the current nesting level of transaction.
If no BEGIN TRANSACTION has been encountered, @@TRANCOUNT is 0, which indicates that the statements run in autocommit mode.

Transactions can be nested. Each BEGIN TRANSACTION just increments @@TRANCOUNT.

When COMMIT TRANSACTION is encountered:

When ROLLBACK TRANSACTION is encountered:

Avoid Nested Transactions

Don’t nest transactions. It is too easy to get lost with the transaction nesting level.

This will make your script a real mess. So, don’t do that !

Keep it Simple !

Just put a BEGIN TRANSACTION at the top of your batch and a COMMIT TRANSACTION at the end.

If a statement raises an error, or if you raise an error with THROW, the batch will abort immediately and an automatic rollback will be performed, discarding all the changes.

BEGIN TRANSACTION

... Put your statements here (INSERT, SELECT, etc).
... The statements IF, WHILE, GOTO, SET @variable, etc are also allowed.

COMMIT TRANSACTION

Besides, you never need to use ROLLBACK TRANSACTION. It is better to raise an error with THROW, which will terminate the batch and automatically perform a rollback.

This way, all the changes produced by your batch will be applied, or none of them if an error occurred.

Examples

CREATE TABLE parent (id         INT NOT NULL IDENTITY( 100, 1) PRIMARY KEY,
                     first_name VARCHAR(20),
                     name       VARCHAR(20)
                    );

CREATE TABLE child  (id         INT NOT NULL IDENTITY(5000, 1) PRIMARY KEY,
                     parent_id  INT NOT NULL,
                     first_name VARCHAR(20)
                    );
GO

DECLARE @id BIGINT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Paul', 'SMITH');
SET @id = SCOPE_IDENTITY();
INSERT INTO child (parent_id, first_name) VALUES (@id, 'John');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Jack');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Jill');
COMMIT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Vladimir', 'POPOV');
SET @id = SCOPE_IDENTITY();
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Anastasia');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Taysia');
COMMIT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Jean', 'DUBOIS');
COMMIT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Mario', 'ROSSI');
SET @id = SCOPE_IDENTITY();
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Isabella');
INSERT INTO child (parent_id, first_name) VALUES (NULL, 'Anna');  -- error, batch aborts
COMMIT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Franz', 'MULLER');
SET @id = SCOPE_IDENTITY();
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Hans');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Greta');
COMMIT;

GO

SELECT * FROM parent;
SELECT * FROM child;

The result is:

2016/03/26 03:18:07 --- executing batch example.sql:20 ---
(1 row(s) affected)  <-- Paul SMITH

(1 row(s) affected)  <--     John

(1 row(s) affected)  <--     Jack

(1 row(s) affected)  <--     Jill

(1 row(s) affected)  <-- Vladimir POPOV

(1 row(s) affected)  <--     Anastasia

(1 row(s) affected)  <--     Taysia

(1 row(s) affected)  <-- Jean DUBOIS

(1 row(s) affected)  <-- Mario ROSSI

(1 row(s) affected)  <--     Isabella

2016/03/26 03:18:07 27:12 [ERROR_GENERAL/ERROR_TABLE_COLUMN_DOESNT_ALLOW_NULL/ERROR_BATCH_ABORT/1]
<Column "parent_id" doesn't allow NULL.>   <-- Anna
2016/03/26 03:18:07 return code: -1

2016/03/26 03:18:07 --- executing batch example.sql:57 ---
id         |first_name     |name           |
-----------+---------------+---------------+
        100|Paul           |SMITH          |
        101|Vladimir       |POPOV          |
        102|Jean           |DUBOIS         |

(3 row(s) affected)

id         |parent_id  |first_name     |
-----------+-----------+---------------+
       5000|        100|John           |
       5001|        100|Jack           |
       5002|        100|Jill           |
       5003|        101|Anastasia      |
       5004|        101|Taysia         |

(5 row(s) affected)

2016/03/26 03:18:07 return code: 0

The transaction for Mario ROSSI and his daughters has failed. The batch has aborted and this pending transaction has been automatically rolled back, cancelling all changes it has recorded.

RSQL, a simple alternative to Microsoft SQL Server