sql-server - 如果事务 2 中出现一些错误，会发生什么？exec 是否会停止并回滚所有 trans 并且不会 exec 事务 3

Question

就我而言，我想停止对所有其他嵌套事务的任何进一步执行。如果 tran1 或 tran 2 执行中出现错误，应停止并抛出错误，回滚之前所做的所有更改。

SET XACT_ABORT ON;


BEGIN TRY
BEGIN TRANSACTION 1
  Insert into Table1 Values (.....)
  Delete from Table2 
  --
  --other example code
  --
COMMIT TRANSACTION 1

BEGIN TRANSACTION 2

  ALTER TABLE [dbo].[Table1] NOCHECK CONSTRAINT [FK_Table2]
  WHILE 1=1

    BEGIN
    WAITFOR DELAY '00:00:01'

    DELETE TOP (1000) ufb 
    FROM Table1 ufb 
    INNER JOIN Table2 mbss on mbss.ID=ufb.ID 
   -- 
    IF @@ROWCOUNT < 1 BREAK 
    END
    --other example code
COMMIT TRANSACTION 2

BEGIN TRANSACTION 3

    --Some insert code
    --other example code
COMMIT TRANSACTION 3

END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRAN
   DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
   DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
   DECLARE @ErrorState INT = ERROR_STATE()
   RAISERROR (@ErrorMessage, 
           @ErrorSeverity, 
           @ErrorState 
           );
           END CATCH

标签： sql-servertsql