嵌套事务

@@TRANCOUNT 函数记录当前事务的嵌套级别。每个 BEGIN TRANSACTION 语句使 @@TRANCOUNT 增加 1。每个 COMMIT TRANSACTION 或 COMMIT WORK 语句使 @@TRANCOUNT 减去 1。没有事务名称的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句将回滚所有嵌套事务，并使 @@TRANCOUNT 减小到 0。使用一组嵌套事务中最外部事务的事务名称的 ROLLBACK TRANSACTION 将回滚所有嵌套事务，并使 @@TRANCOUNT 减小到 0。在无法确定是否已经在事务中时，可以用 SELECT @@TRANCOUNT 确定 @@TRANCOUNT 是等于 1 还是大于 1。如果 @@TRANCOUNT 等于 0，则表明不在事务中。

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO

CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
----------------------------------------------------------------------------
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
----------------------------------------------------------------------------
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are 
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;

from: http://wenku.baidu.com/view/7be79a0c844769eae009ed30.html

保存点：sqlserver  提供一种用于回滚事务的机制：Save Tran, 它不会对@@trancount产生任何
影响，只是标记回滚事务时可以到达的点。

DECLARE @nestFlag BIT
IF(@@TRANCOUNT>0)
BEGIN
	SET @nestedFlag = 1
	SAVE TRAN TestA
END
ELSE
BEGIN
	SET @nestFlag = 0
	BEGIN TRAN TestA
END
--执行业务操作，如果出错就回滚事务点，并立即返回
IF (@@error <> 0)
BEGIN
	ROLLBACK TRAN TestA
	RETURN 0
END
IF (@testedFlag=0)
BEGIN
	COMMIT TRAN TestA
END