Nesting Transactions in T-SQL

it's primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or processes that have no active transaction.

"Commit" statement won't take effect in an inner transaction,"rollback" statement does rollback all the changes made either by itself or the outer transaction. "rollback transaction savepoint" is a better choice to use to rollback only the changes made by the stored procedure.

an impression, in T-SQL, savepoint is for rolling back purpose. transaction name is for commit purpose.

if a stored procedure is called when a transaction is active, the nested transaction in it is largely ignored. the transaction will be governed by the out transaction. if it's executed by a process that does not have an active transaction, the commit statement in the stored procedure will effectively commit the changes to database.

a rollback statement will rollback everything, wherever the place it's executed. the @@trancount will be reset to 0. to be safe, it should check the @@trancount before issuing the "commit transaction" or "rollback " statements.

in a stored procedure, using the "save transaction savepointname" is a better way to rollback the changes made by the stored procedure.

sql server automatically starts a implicit transaction when enters in a trigger.

updates after a rollback statement will still be stored into database.

the following code will raise error since the second "rollback transaction" is executed without a transaction content.

BEGIN TRANSACTION
BEGIN TRANSACTION
-– Some Codes
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION