事务(不同层)回滚测试问题经典例子设计

USE emrdb
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'TestTransDemoA')
    DROP TABLE TestTransDemoA
GO

CREATE TABLE TestTransDemoA
(
    TransDemoID        INT                IDENTITY,
    TransName        VARCHAR(12)        NOT NULL,
    Step            INT                NOT NULL
)
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'TestTransDemoB')
    DROP TABLE TestTransDemoB
GO

CREATE TABLE TestTransDemoB
(
    TransDemoID        INT                IDENTITY,
    TransName        VARCHAR(20)        NOT NULL,
    Step            INT                NOT NULL
)
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'TestTransDemoC')
    DROP TABLE TestTransDemoC
GO

CREATE TABLE TestTransDemoC
(
    TransDemoID        INT                IDENTITY,
    TransName        VARCHAR(20)        NOT NULL,
    Step            INT                NOT NULL
)
GO

USE emrproc
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'xpTestTransDemo')
    DROP PROC xpTestTransDemo
GO

CREATE PROC xpTestTransDemo
(
    @EnterValue        INT
)
AS
    DECLARE @oTestDemoName VARCHAR(30) = 'Trans - Demo' --Length = 12
    BEGIN TRAN
    INSERT INTO emrdb..TestTransDemoA(TransName,Step)
    VALUES(@oTestDemoName,1)
    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRAN
        SELECT -1 AS ERRORCODE, '插入第1层记录失败!' ERRORMSG
        RETURN -1
    END
    IF @EnterValue > 1
    BEGIN
        INSERT INTO emrdb..TestTransDemoB(TransName,Step)
        VALUES(@oTestDemoName + CONVERT(VARCHAR(50),POWER(10,@EnterValue)),2)
        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK TRAN
            SELECT -2 AS ERRORCODE, '插入第2层记录失败!' ERRORMSG
            RETURN -2
        END
        IF @EnterValue > 2
        BEGIN
            INSERT INTO emrdb..TestTransDemoC(TransName,Step)
            VALUES( @oTestDemoName + CONVERT(VARCHAR(50),POWER(1000,@EnterValue)),3)
            IF @@ROWCOUNT = 0
            BEGIN
                ROLLBACK TRAN
                SELECT -3 AS ERRORCODE, '插入第3层记录失败!' ERRORMSG
                RETURN -3
            END
        END
    END
    COMMIT TRAN
    SELECT * FROM emrdb..TestTransDemoA
    SELECT * FROM emrdb..TestTransDemoB
    SELECT * FROM emrdb..TestTransDemoC
    --WHILE @StartNo <= @EndNo    
    --BEGIN
    --    INSERT INTO hisdb..BASE_InvoiceItem(InvoiceID,HospitalID,Prefex,SerialNo,Operation,OperaTime)
    --    VALUES(@InvoiceID,@HospitalID,@Batch,@StartNo,@AllowStfID,@DateTime)
    --    SET @StartNo += 1                            
    --END
GO

EXEC emrproc..xpTestTransDemo 2

--以上说明嵌套不同层同样可以回滚