Lesson 9	Creating transactions
Objective	Create and manage transactions in SQL-Server

Create and manage Transactions Statements in SQL-Server

As briefly discussed in an earlier lesson, there are three major Transact-SQL statements that deal with transactions. They are:

BEGIN TRANSACTION:for starting a transaction
COMMIT TRANSACTION:for ending a transaction and applying all changes
ROLLBACK TRANSACTION:for ending a transaction and canceling all changes

These three statements use the following general syntax:

transaction_name is the name of your transaction. This is only used in the case of nested transactions so that they can be distinguished from each other,
but is only used for the outer-most transaction — 1) transaction_name is the name of your transaction. This is only used in the case of nested transactions so that they can be distinguished from each other, but is only used for the outer-most transaction.

2) @transaction_variable is the name of your transaction as stored in a variable

7) @savepoint_name is the name of your savepoint, as specified in a SAVE TRANSACTION Transact-SQL statement. This allows for you to rollback part of the transaction, without having to rollback the entire transaction.

8) @savepoint_variable is the name of your savepoint, as specified in the SAVE TRANSACTION Transact-SQL statement, as stored in a variable

TRAN or TRANSACTION.

Notice that your statements can either be entered as TRAN or TRANSACTION. These terms are synonymous and you do not need to explicitly enter this part of the statement. However, there is an additional statement that can be used:

SAVE TRANSACTION for marking a savepoint, or bookmark, at a specific place within a transaction

The SAVE TRANSACTION statement uses the following syntax:

Syntax for SQL-Server Transaction Statements

SQL Server 2014, Marks the starting point of an explicit, local transaction. BEGIN TRANSACTION increments @@TRANCOUNT by 1.

BEGIN { TRAN | TRANSACTION } 
    [ { transaction_name | @tran_name_variable }
      [ WITH MARK [ 'description' ] ]
    ]
[ ; ]

SAVE TRANSACTION Syntax Example in SQL-Server<

In Transact-SQL (T-SQL), the SAVE TRANSACTION statement sets a savepoint within a transaction. A savepoint marks a point within a transaction that you can roll back to without affecting the entire transaction. This feature allows the DBA or developer to have a finer degree of control over transactions and is useful for error recovery or to correct logical errors in a transaction. The syntax for SAVE TRANSACTION is as follows:

SAVE TRANSACTION savepoint_name;

Here savepoint_name is an identifier that you choose to refer to the savepoint later. After setting a savepoint, you can use the ROLLBACK TRANSACTION statement with the savepoint name to roll back to that point and undo parts of the transaction. The syntax is:

ROLLBACK TRANSACTION savepoint_name;

SAVE TRANSACTION

BEGIN TRANSACTION;

-- Assume we have a table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderAmount'

-- Insert a new record.
INSERT INTO Orders (OrderID, CustomerID, OrderAmount)
VALUES (1, 101, 100);

-- Set a savepoint.
SAVE TRANSACTION SavePoint1;

-- Insert another record.
INSERT INTO Orders (OrderID, CustomerID, OrderAmount)
VALUES (2, 102, 200);

-- Rollback to the savepoint.
ROLLBACK TRANSACTION SavePoint1;

-- The second INSERT operation will be rolled back, but the first will remain.

-- Commit the transaction.
COMMIT;

In this example, the ROLLBACK TRANSACTION SavePoint1; statement undoes the second INSERT operation. However, it does not affect the first INSERT operation because that happened before the savepoint. The COMMIT; statement then commits the transaction, making the first INSERT operation permanent. Remember that SAVE TRANSACTION can only be used to create a savepoint within an existing transaction. If there is no active transaction, an error will occur. Also, savepoints are not nestable: if you issue a SAVE TRANSACTION command using the name of an existing savepoint, the old savepoint is deleted and a new one is set.

SAVE TRANSACTION syntax (Transact-SQL)

You entered:

Applies To: SQL Server 2014, SQL Server 2016 Preview
Sets a savepoint within a transaction.
Topic link icon Transact-SQL Syntax Conventions

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
[ ; ]

savepoint_name is the name of your savepoint.
This is used to identify the point in the transaction where you might want to rollback to. — 1) savepoint_name is the name of your savepoint. This is used to identify the point in the transaction where you might want to rollback to.

2) @savepoint_variable is the name of your savepoint as stored in a variable.

Notice that your statements can either be entered as TRAN or TRANSACTION. They are synonymous. You do not need to explicitly enter the SACTION part of the statement.

Creating Transactions

Creating transactions is quite simple. You simply specify the appropriate keywords to begin and end the transaction, plus place all Transact-SQL statements that make up a work unit in the middle. This is an example of a transaction:

BEGIN TRAN
CREATE TABLE Timesheets (EmployeeID int, 
 ClientID tinyint, TaskID tinyint, 
 WeekEndingDate Smalldatetime, Hours decimal)
 INSERT INTO Timesheets (EmployeeID, ClientID, 
 TaskID, WeekEndingDate, Hours) 
 VALUES (1001, 101, 11, "02/02/99", 40)
COMMIT TRAN

The statement above starts the transaction, executes two separate Transact-SQL statements, and commits the transaction.

Errors during Creation

You may wonder what happens if there are errors. This is a great question. If the CREATE TABLE statement fails, you do not want the INSERT statement to succeed. They are treated as a single work unit, which is why they are wrapped within a transaction. Although errors are discussed in a later lesson, you should know that in our example, if an error occurs, the transaction needs to be rolled back, like this:

BEGIN TRAN
CREATE TABLE Timesheets (EmployeeID int, 
 ClientID tinyint, TaskID tinyint, 
 WeekEndingDate Smalldatetime, Hours decimal)
IF @@ERROR <> 0
BEGIN
 ROLLBACK
 RETURN
END 
INSERT INTO Timesheets (EmployeeID, ClientID, TaskID,
 WeekEndingDate, Hours)
VALUES (1002, 102, 11, "02/02/99", 40)
IF @@ERROR <> 0
BEGIN
 ROLLBACK
 RETURN
END 
COMMIT TRAN

Notice that if the value of the global variable @@ERROR is not 0, then an error occurred and the transaction is rolled back.
That is really all there is to transactions. The majority of the Transact-SQL programming is in the error handling.
See the Error handling lesson for more information on this. The next lesson takes transactions one step further by showing you how to create transactions across multiple servers.

Creating Transaction - Exercise

Click the Exercise link below to practice creating a transaction.
Creating Transaction - Exercise