Transactions Locks «Prev  Next»

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

Create and manage Transactions in SQL-Server

Transaction SQL statements

As briefly discussed in an earlier lesson, there are three major Transact-SQL statements that deal with transactions. They are:
  1. BEGIN TRANSACTION:for starting a transaction
  2. COMMIT TRANSACTION:for ending a transaction and applying all changes
  3. ROLLBACK TRANSACTION:for ending a transaction and canceling all changes

These three statements use the following general syntax:


Transaction Statement Syntax
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.
However, there is an additional statement that can be used:
  1. SAVE TRANSACTION: for marking a savepoint, or bookmark, at a specific place within a transaction

The SAVE TRANSACTION: statement uses the following syntax:
Save Transaction Syntax
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