sql server 觸發器失敗時我們可以送出事務嗎？

當表上的插入觸發器失敗時，我試圖避免數據失去。我正在嘗試以下場景，但我的程式碼失敗了。

當客戶表上發生插入時，我想將相同的數據插入到存檔表中。當觸發器失敗時，我不想回滾整個事務，這會導致客戶表上的數據失去。

即使觸發器失敗，我也希望將數據插入到客戶表中，並且儲存過程應該像往常一樣返回Customer_ID 。

ALTER TRIGGER [dbo].[Customer_Insert_Trigger_Test] 
  ON  [dbo].[Customers]
  AFTER INSERT
AS 
BEGIN

BEGIN TRY
   begin transaction;
   set nocount  on;
   SAVE TRANSACTION InsertSaveHere;
   --Simulating error situation
   RAISERROR (N'This is message %s %d.', -- Message text.
      11, -- Severity,
      1, -- State,
      N'number', -- First argument.
      5); -- Second argument.

  Insert into Archive select * from Inserted;
   commit transaction;

 END TRY

 BEGIN CATCH
   ROLLBACK TRANSACTION InsertSaveHere;
 END CATCH
END

我的問題主要是關於如果觸發器失敗，如何避免客戶表上的實際插入回滾。如何為此更改我的程式碼？

該問題提到“程式碼失敗”，但沒有任何錯誤消息或具體失敗的跡象。包括這些資訊中的至少一個（如果不是兩者）總是有助於獲得更好的答案。

目前，我看到一些關於觸發器和事務的假設似乎是不正確的：你@@TRANCOUNT通過呼叫增加，但只有在沒有錯誤並且行在塊內執行時BEGIN TRAN;才會減少。如果發生錯誤，則跳過並發生保存點的 a。但是回滾保存點不會減少，在這種情況下操作結束並且事務仍然處於活動狀態。@@TRANCOUNT``COMMIT TRAN;``TRY``COMMIT``ROLLBACK``@@TRANCOUNT``INSERT

觸發器存在於內部啟動的事務中，該事務將其綁定到觸發觸發器的 DML 操作。這就是您能夠ROLLBACK在觸發器中呼叫以取消該 DML 操作的方式。

選項 1（首先防止錯誤 - 如果可以可靠地測試錯誤條件，則首選）

（添加於 2020 年 10 月 21 日：不確定我在發布此答案時如何或為什麼沒有想到此選項）

如果可能的話，如果可以測試導致錯誤的條件，那麼最好在嘗試有時會失敗的操作之前測試該條件。如果錯誤從未真正發生，那麼您無需更改預設行為和/或添加自定義事務處理。在您的情況下（即將新記錄添加到存檔表），您可以執行以下操作之一：

SET NOCOUNT ON;

BEGIN TRY

   INSERT INTO dbo.Archive
       SELECT *
       FROM   inserted ins
       WHERE  NOT EXISTS (SELECT *
                          FROM   dbo.Archive arc
                          WHERE  arc.[Login] = ins.[Login])
                -- assuming [Login] field exists and should be unique
END TRY
BEGIN CATCH
   DECLARE @DoNothing INT;
END CATCH;

有時這不會擷取所有內容，並且仍然可能偶爾出現違規，在這種情況下，您可能仍然需要使用選項 2。但是，即使使用選項 2 並且它總是有效，仍然最好嘗試防止錯誤，因為必須完成工作才能達到導致錯誤的條件，而這只是浪費時間、IO、爭用等。

選項 2A（防止錯誤取消事務 - 如果觸發器中有多個 DML 語句，則首選）

考慮到這一點，您應該能夠刪除BEGIN TRAN;和COMMIT TRAN;線以使其正常工作。最終效果是，如果沒有錯誤，INSERT進入Archive表將按預期送出，但如果有錯誤，它將執行ROLLBACK保存點並繼續。

但是，刪除這兩個部分後，您仍然會遇到以下錯誤的棘手情況：

消息 3931，級別 16，狀態 1，過程 Customer_Insert_Trigger_Test，第 XXXXX 行

目前事務無法送出，也無法回滾到保存點。回滾整個事務。

這種行為的原因似乎是XACT_ABORT ON系統在呼叫觸發器時的隱式設置。的效果XACT_ABORT ON是取消大多數錯誤（編譯錯誤或 from 除外）的事務（和查詢批處理RAISERROR）。補救措施？只需設置XACT_ABORT OFF在觸發器的開頭。

例如，以下對我有用：

CREATE
--ALTER
TRIGGER [dbo].[Customer_Insert_Trigger_Test] 
  ON  [dbo].[Inserts]
  AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT OFF;

BEGIN TRY
   PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR(10), @@TRANCOUNT); -- for debug only
   SAVE TRANSACTION InsertSaveHere;

   -- Simulating error situation
   DECLARE @Error INT = 1 / 0; -- runtime error

  -- Insert into Archive select * from Inserted;
END TRY
BEGIN CATCH
   PRINT 'Entering CATCH block...'; -- for debug only
   ROLLBACK TRANSACTION InsertSaveHere;
END CATCH;

END;

請注意，此方法不會改變此表上觸發器的預期行為：1）COMMIT在最外層實際發生的情況（初始 DML 語句或如果在此之前已啟動顯式事務，則超出該語句）語句），2）該表上的其他潛在觸發器發出 ROLLBACK 以取消操作的能力，以及 3）在該表上的 DML 語句之前啟動的顯式事務的能力，從發出 ROLLBACK 取消所有包括對該表的 DML 操作在內的更改。

選項 2B（防止錯誤取消事務 - 如果觸發器中有單個 DML 語句，則首選）

當然，如果這裡唯一可能出錯的是INSERTinto the Archivetable，那麼您可能也可以擺脫SAVE TRANandROLLBACK TRANSACTION InsertSaveHere;並在CATCH塊中做一些事情以使其不為空，類似的事情DECLARE @Test INT;可能會起作用。這裡的原因是一個錯誤從未真正發生過的單個 DML 語句，因此沒有什麼可以回滾;-)。

---

選項 3（不推薦）

要回答標題中所述的問題：您應該能夠COMMIT在觸發器內，但我會非常謹慎地做這樣的事情，因為它會改變事務何時送出或回滾的預期行為，這可能會阻止正確操作此表上的其他觸發器（如果此觸發器首先執行，它們將無法發出ROLLBACK取消操作），並且會阻止在此表上的 DML 操作之前啟動的顯式事務的預期操作。

為此（注意：在繼續閱讀本段之前，您需要直接閱讀上面的段落），您將發出一個COMMIT TRAN;（因為觸發器已經存在於事務中），然後執行一個BEGIN TRAN;. 將COMMIT TRAN;送出初始 DML 操作，並將BEGIN TRAN;返回@@TRANCOUNT1，以便當觸發器執行結束時，您不會收到錯誤說明觸發器以不同於@@TRANCOUNT開始時的方式結束。

引用自：https://dba.stackexchange.com/questions/137305