Transactions
Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.
Users can group two or more 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, we say that these changes are committed to the database.
Here is an example of a transaction :
USE pubs DECLARE @intErrorCode INT BEGIN TRAN UPDATE Authors SET Phone = '415 354-9866' WHERE au_id = '724-80-9391' SELECT @intErrorCode = @@ERROR IF (@intErrorCode <> 0) GOTO PROBLEM UPDATE Publishers SET city = 'Calcutta', country = 'India' WHERE pub_id = '9999' SELECT @intErrorCode = @@ERROR IF (@intErrorCode <> 0) GOTO PROBLEM COMMIT TRAN PROBLEM: IF (@intErrorCode <> 0) BEGIN PRINT 'Unexpected error occurred!' ROLLBACK TRAN END
Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATEstatements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN 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. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.
Error Handling
The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. The @@ERROR automatic variable is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of@@ERROR immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset to 0 when the next statement executes successfully. If a trappable error occurs, @@ERROR will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you'll want to test for changes in @@ERROR right after any INSERT, UPDATE, orDELETE statement.
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), @title VARCHAR(20), @title_type CHAR(12)) AS BEGIN TRAN INSERT titles(title_id, title, type) VALUES (@title_id, @title, @title_type) IF (@@ERROR <> 0) BEGIN PRINT 'Unexpected error occurred!' ROLLBACK TRAN RETURN 1 END INSERT titleauthor(au_id, title_id) VALUES (@au_id, @title_id) IF (@@ERROR <> 0) BEGIN PRINT 'Unexpected error occurred!' ROLLBACK TRAN RETURN 1 END COMMIT TRAN RETURN 0
This kind of solution contains substantial repetition especially if your business logic requires more than two Transact-SQL statements to be implemented. A more elegant solution is to group codes into a generic error handling procedure:
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), @title VARCHAR(20), @title_type CHAR(12)) AS BEGIN TRAN INSERT titles(title_id, title, type) VALUES (@title_id, @title, @title_type) IF (@@ERROR <> 0) GOTO ERR_HANDLER INSERT titleauthor(au_id, title_id) VALUES (@au_id, @title_id) IF (@@ERROR <> 0) GOTO ERR_HANDLER COMMIT TRAN RETURN 0 ERR_HANDLER: PRINT 'Unexpected error occurred!' ROLLBACK TRAN RETURN 1
