Friday 23 November 2012

Transactions in Database

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.

Error Handling

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 clients 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, or DELETE statement.

Example:- 
 

Note:-

It’s used to safe your data during error occurs.
 


0 comments:

Post a Comment