Scripts and Transactions in Transact-SQL - Explicit and Implicit Transactions
(Page 3 of 5 )
Explicit Transactions
The most popular and obvious way to use transactions is to give explicit commands to start or finish the transaction. Transactions started in this way are called explicit transactions. You can group Transact-SQL statements into a single transaction using the following statements:
- Begin Transaction
- Rollback Transaction
- Commit Transaction
If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, these changes are committed to the database.
I will demonstrate the use of these processes on the ap_LeaseSchedule_Clear stored procedure. Its main purpose is to set monthly lease amounts to zero for each asset associated with an expired lease schedule. It also sets the total of the lease amounts to zero. These two operations must be performed simultaneously to preserve the integrity of the database.
Create Procedure ap_LeaseShedule_Clear
-- Set value of Lease of all equipment
-- associated with expired Lease Schedule to 0.
-- Set total amount of Lease Schedule to 0.
@intLeaseScheduleId int
As
Begin Transaction
-- Set value of Lease of all equipment
-- associated with expired Lease Schedule to 0
Update dbo.Inventory
Set Lease = 0
Where LeaseScheduleId = @intLeaseScheduleId If @@Error <> 0 goto PROBLEM
-- Set total amount of Lease Schedule to 0 Update dbo.LeaseSchedule
Set PeriodicTotalAmount = 0
Where ScheduleId = @intLeaseScheduleId
If @@Error <> 0 goto PROBLEM
Commit Transaction
Return 0
PROBLEM:
Print ' Unable to eliminate lease amounts from the database!'
Rollback Transaction
Return 1
Before the real processing starts, the Begin Transaction statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two Update statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the Commit Transaction statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by If statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing.
We will review more complex transactions (including nested transactions) and ways to process errors in the next chapter.
Implicit Transactions
The third transaction mode is called the implicit transaction. To use this mode, you must set the Set Implicit_Transactions On statement for the connection. Any of the following statements will serve as an implicit start to a transaction:
Alter Table Create Delete Drop |
Fetch Grant Insert Open |
Revoke Select Truncate Table Update |
To finish the transaction, you must use the Commit Transaction or Rollback Transaction statement. After that, any of the preceding commands will start a new implicit transaction.
Next: Transaction Processing Architecture >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Buy this book now.
|
|
/ 4 
