Sql-Server

SQL Server 嘗試擷取事務

  • October 15, 2016

如果發生不好的事情,我需要知道下面的 T-SQL 是否正確回滾。

我首先創建一個臨時表

IF OBJECT_ID(N'tempdb..#LocalSummary') IS NOT NULL  
   DROP TABLE #LocalSummary   

CREATE TABLE  #LocalSummary (

然後我填充表格

INSERT INTO #LocalSummary
  SELECT *....

然後我開始嘗試捕捉

BEGIN TRY
BEGIN TRANSACTION 

   DELETE FROM LocalSummaryRealTable

   INSERT INTO LocalSummaryRealTable
       SELECT S.*
       FROM #LocalSummary AS S 

   COMMIT
END TRY
BEGIN CATCH
      IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION 
   ...

我注意到當我插入LocalSummaryRealTable列時沒有對齊,我得到了一個錯誤。我預計事情會回滾,但在 SSMS 查詢視窗中,它給了我一條消息,指出未送出 trans 並詢問我是否要送出它。為什麼這會問我什麼時候應該回滾?

你想做BEGIN TRANSACTION之前的BEGIN TRY

BEGIN TRANSACTION 
BEGIN TRY
   DELETE FROM dbo.LocalSummeryRealTable;
   INSERT INTO dbo.LocalSummeryRealTable
   SELECT S.*
   FROM  #LocalSummery' AS S;
   COMMIT TRANSACTION;
END TRY
BEGIN CATCH
      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; 
END CATCH

此外,請對錶、視圖、儲存過程等數據庫對象的引用進行模式限定。此外,您應該養成在每條語句的末尾添加分號的習慣。

SET XACT_ABORT ON;如果您明確回滾塊內的事務,則不需要。CATCH

引用自:https://dba.stackexchange.com/questions/152349