http://msdn.microsoft.com/en-us/library/ms189797.aspx
XACT_STATE returns the following values.
| Return value | Meaning |
| 1 | The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction. |
| 0 | There is no active user transaction for the current request. |
| -1 | The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.When a batch finishes running, the Database Engine will automatically roll back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This message indicates that an uncommittable transaction was detected and rolled back. |
Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction.
@@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction.
XACT_STATE cannot be used to determine whether there are nested transactions.
http://msdn.microsoft.com/en-us/library/ms181299.aspx
ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.
In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
ROLLBACK TRANSACTION cannot reference a savepoint_name in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.
A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed, except when the COMMIT TRANSACTION is associated with a nested transaction that is contained within the transaction being rolled back. In this instance, the nested transaction will also be rolled back, even if you have issued a COMMIT TRANSACTION for it.
Within a transaction, duplicate savepoint names are allowed, but a ROLLBACK TRANSACTION using the duplicate savepoint name rolls back only to the most recent SAVE TRANSACTION using that savepoint name.
Paul recommand doesn’t use Nested Transaction.
Simon's SQL 