sql - 无法回滚子事务。未找到该名称的事务或保存点

Question

我在 sql 中有一个循环，它可以做一些事情

    begin tran one

    do some inserts in others tables

      --start loop
     begin tran two
      --do something
      begin try
--if something fail then a trigger does rollback and this return a error (and this goes to catch), then don't i need do the rollbak in catch? this could not be dissable because this is working on production
      --something finished ok 
      commit tran two
      end try
      begin catch
     rollback tran two
      end catch

    --finished loop
    commit


----------

我收到这个错误

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

begin tran one
begin tran two

rollback tran two

做这个代码我得到这个:

Cannot roll back two. No transaction or savepoint of that name was found.

我只希望子查询回滚第二个循环并继续处理其他记录。

Answer 1

运算符(operator)回滚回滚所有事务，对于仅回滚第二个循环，您必须使用保存点:

  begin tran one

-- do some inserts in others tables

  --start loop
  save tran two -- begin tran two

  --do something
  begin try
     update product set id = 1 --if something fail then a trigger does rollback and this return a error (and this goes to catch), then don't i need do the rollbak in catch? this could not be dissable because this is working on production

  --something finished ok 
  commit tran two
  end try
  begin catch

    rollback tran two
  end catch

--finished loop
commit

触发示例:

create table product (id int)
GO  
create trigger product_trigger on product for update
as
  set xact_abort off

  if (select count(*) from inserted i join product p on i.id=p.id)=0 begin 
    if (@@trancount>0) begin 
      /* rollback */ 
      raiserror('product does not exist', 16, 1) 
    end 
  end