为什么回滚对 SQL Server 2012 中的变量表不起作用? [英] Why rollback is not working for variable table in SQL Server 2012?

问题描述

我创建了一个变量表.在我的存储过程中，有很多事务.

I have created one variable table. In my stored procedure, there are lots of transactions.

现在每当发生错误时，我都想回滚特定事务，该事务具有一些语句，可以从变量表中插入或更新或删除记录.

Now whenever an error occurs, I want to rollback a specific transactions which has some statements which insert or update or delete records from variable table.

这只是我实际问题的一个例子:

This is just an example of my actual problem :

declare @tab table (val int)

insert into @tab select 2
insert into @tab select 3
insert into @tab select 4

select * from @tab

begin tran
begin try
    update @tab set val = 1
    select 1/0;
    commit
end try
begin catch
    rollback
end catch

select * from @tab

实际输出:-

我的预期输出是:-

所以这里的事务回滚不起作用.为什么它在这里不起作用?我做错了什么吗?

So here rollback of a transaction is not working. Why it is not working here ? Am I doing something wrong ?

推荐答案

您使用的不是 temp 表，而是 variable 表.有区别.

You are not using a temp table, you are using a variable table. There is a difference.

临时表可用于事务，而变量表则不能.参见 http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

Temp tables work with transactions, variable tables don't. See http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

如果您将变量表 @tab 更改为 #tab 的临时表，您将获得所需的行为.

If you were to change your variable table @tab to a temporary table of #tab, you would get your desired behavior.

临时表和变量表的区别:https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Differences between temp and variable tables: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

我已经修改了我的问题.感谢您的知识分享.但问题还是一样.为什么它不适用于变量表?

I have modified my question. Thanks for your knowledge sharing. But question remains the same. Why it is not working for variable table?

我上面发布的链接比我所能提供的更详细.

The links I posted above go through that with more detail than I could.

这篇关于为什么回滚对 SQL Server 2012 中的变量表不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！