回滚根本没有发生...为什么？

Question

我有以下内容

Declare @tbl Table(Id int identity, SomeCol varchar(10) not null)

Begin Transaction Tran1
   Declare @ErrorNum int  
   Declare @i int   
   Set @i =1

   --Start Operation
   While(@i <= 10)
    Begin

        If(@i = 9)
            Begin
                Insert into @tbl(SomeCol) Values(null)
                Set @ErrorNum = @@ERROR
            End
        Else
            -- All records will be inserted successfully
            Begin
                Insert into @tbl(SomeCol) Values(@i)
            End
        Set @i = @i +1

    End -- End of while

    -- If there is any error, notify that and roll back the transaction
   IF @ErrorNum <> 0 
   BEGIN 
      RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) 
      Rollback Transaction Tran1
   End
IF (@ErrorNum = 0)
   COMMIT TRANSACTION Tran1

   select * from @tbl

我想做的是，如果 @i 的值为 9 ，我试图向 @tbl 插入一个空值，这根本不应该允许，并且应该回滚所有记录并且只会生成自定义异常。

但它给出了系统和自定义异常，并且除了第 9 条记录外，记录已被插入且未回滚。

以下是我在“消息”选项卡中得到的内容

**(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 515, Level 16, State 2, Line 14
Cannot insert the value NULL into column 'SomeCol', table '@tbl'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
Msg 50000, Level 16, State 1, Line 29
Attempt to insert null value in SomeCol is not allowed
(9 row(s) affected)**

下面是在“记录”选项卡中得到的内容

Id  SomeCol
1   1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
10  10

我不确定我做错了什么。

需要帮助。

Answer 1

表变量不会回滚。尝试使用 #temporary 表，您的脚本应该按预期工作！

create  Table #tbl(Id int identity, SomeCol varchar(10) not null)

Begin Transaction Tran1
   Declare @ErrorNum int  
   Declare @i int   
   Set @i =1

   --Start Operation
   While(@i <= 10)
    Begin

        If(@i = 9)
            Begin
                Insert into #tbl(SomeCol) Values(null)
                Set @ErrorNum = @@ERROR
            End
        Else
            -- All records will be inserted successfully
            Begin
                Insert into #tbl(SomeCol) Values(@i)
            End
        Set @i = @i +1

    End -- End of while

    -- If there is any error, notify that and roll back the transaction
   IF @ErrorNum <> 0 
   BEGIN 
      RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) 
      Rollback Transaction Tran1
   End
IF (@ErrorNum = 0)
BEGIN 
   PRINT 'COMMIT'
   COMMIT TRANSACTION Tran1
END
   select * from #tbl

drop table #tbl

Answer 2

看起来你永远无法到达
回滚事务 Tran1 - 因为 RAISE 之前出现过。
只需交换两行即可

Answer 3

表变量无法回滚。 请参阅此处。

Answer 4

如果将以下内容添加到脚本的开头，则整个事务将在失败时回滚（没有部分加载的表变量），但您可能看不到自定义错误消息：

SET XACT_ABORT ON 

来自 Microsoft 文档：

当 SET XACT_ABORT 为 OFF 时，在某些情况下
仅适用于 Transact-SQL 语句
引发错误的部分已回滚
并且交易仍在继续
加工。取决于
错误的严重性，整个
事务甚至可能被回滚
当 SET XACT_ABORT 为 OFF 时。关闭是
默认设置。

如果您希望回滚事务并发出自定义错误消息，您可以使用 Try...Catch：

BEGIN TRY
    Declare @tbl Table(Id int identity, SomeCol varchar(10) not null)

    Begin Transaction Tran1
       Declare @i int   
       Set @i =1

       --Start Operation
       While(@i <= 10)
        Begin

            If(@i = 9)
                Begin
                    Insert into @tbl(SomeCol) Values(null)
                End
            Else
                -- All records will be inserted successfully
                Begin
                    Insert into @tbl(SomeCol) Values(@i)
                End
            Set @i = @i +1

        End -- End of while

        COMMIT TRANSACTION Tran1

       select * from @tbl
END TRY
BEGIN CATCH
       BEGIN 
          RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) 
          Rollback Transaction Tran1
       End
END CATCH