sql-server - 如何确保执行所有 SQL 更新

Question

我们在生产环境中遇到了一个问题，即只运行了前 2 个更新，而后 2 个没有。当从 BIDS (sql server) 运行以下脚本以确保运行 4 个 sql 更新语句中的每一个时，我们可以实现什么？我们是否需要将整个内容包装在 Try....Catch 中？

下面基本上是我在生产中使用的最终工作代码。

SET XACT_ABORT ON;
GO

BEGIN TRANSACTION

-- Batch 0
BEGIN TRY

    --There is a step prior to this step to delete table
    Insert Into Table X (Field1, Field2, Field3)

    Select
            Field1
            ,Field2
            ,Field3
    FROM Table Loans

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  ROLLBACK TRANSACTION;
END CATCH;
GO

-- Batch 1
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  --Update A Fields
    Update X Table
    Set X Field4 = A Field
    From X Table
    Left OUTER JOIN A Table
        ON X Key = A Key

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Batch 2
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  --Update B Fields
    Update X Table
    Set X Field5 = B Field
    From X Table
    Left OUTER JOIN B Table
        ON X Key = B Key

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Batch 3
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  --Update C Fields
    Update X Table
    Set X Field5 = C Field
    From X Table
    Left OUTER JOIN C Table
        ON X Key = C Key

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Batch 4
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  --Update D Fields
    Update X Table
    Set X Field7 = D Field
    From X Table
    Left OUTER JOIN D Table
        ON X Key = D Key

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Commit transaction
IF XACT_STATE() = 1
BEGIN
  COMMIT TRANSACTION;
  PRINT 'Transaction committed.';
END;

标签： sql-server

Answer 1

我会将它包装在事务中，如果出现问题，它会自动回滚更改。

BEGIN TRANSACTION
    your tsql code...
COMMIT

在仍在使用事务的同时，将您的代码包装在 try/catch 中，然后您可以选择/打印/记录您的错误。 尝试...捕获 (Transact-SQL)