回滾截斷表

我有一個執行以下操作的儲存過程：

BEGIN TRANSACTION

   -- Code to delete updated records from production (dbo) table
   DELETE FROM [dbo].[factMyTable]
   WHERE exists (SELECT *
       FROM [RAW].[MyTable]
       WHERE [RAW].[MyTable].[refno] = [dbo].[factMyTable].[refno]
       AND [RAW].[MyTable].[modification_dttm] >= [dbo].[factMyTable].[modification_dttm]
       )

   -- Code to perform the append of incremental records 
   INSERT INTO [dbo].[factMyTable]
   SELECT
        [refno]
       ,[field1]
       ,[field2]
       ,[field3]
       ,[FieldN]
       ,[modification_dttm]
   FROM [RAW].[MyTable]

-- Truncate stage table and get ready for next load
TRUNCATE TABLE [RAW].[MyTable]

COMMIT TRANSACTION

正如您在上面看到的，我有一個包含在 BEGIN/COMMIT 事務塊中的截斷命令。但是，在插入命令中執行此儲存過程時出現錯誤，其中設置為 NOT NULL 的欄位正在接收 NULL 值。因此：

1. 將 RAW 表中的記錄插入 dbo 表的操作被回滾；但
2. RAW 表的截斷沒有回滾。

這個想法是，如果插入數據時出現錯誤，則不應發生截斷。

根據這篇文章，我們可以回滾 truncate 命令，但也許我的儲存過程沒有正確編寫腳本。也許有一種更直接的方法可以確保只有在插入沒有返回錯誤時才會發生截斷？我該怎麼辦？

1. 將 RAW 表中的記錄插入 dbo 表的操作被回滾；但
2. RAW 表的截斷沒有回滾。

不，根本沒有rollback，這是複製品。

這xact_abort off是您default設置的選項，我創建了 2 個表，然後我打開事務結束執行 2inserts個，其中一個（第二個）失敗，我從兩個表中添加select @@trancount，select以便您可以更好地查看發生了什麼：

--set xact_abort on

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go


create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin transaction

   insert into dbo.t2
   values(-1);

   insert into dbo.t2
   select col1
   from dbo.t1;

   select @@trancount as [@@trancount before truncate];

   truncate table dbo.t1;

commit transaction;

select @@trancount as [@@trancount after commit];

select *
from dbo.t1;

select *
from dbo.t2;

如您所見，沒有rollback製作，只有您的commit. 您將 (-1) 插入dbo.t2其中，並且該行永久存在。這是因為錯誤

消息 515，級別 16，狀態 2，第 18 行無法將值 NULL 插入列 ‘col1’，表 ‘dbo.t2’；列不允許空值。

只是語句終止。第二條語句失敗，因此沒有插入任何行，但是 (-1) 的插入沒有回滾，並且正如您在錯誤之後看到的那樣，您transaction仍然是open。commit送出 -1 和 table的插入是你的truncation。

---

現在第二個測試：取消註釋set xact_abort on，這將statment terminating只產生錯誤batch aborting，所有的statements內部事務都將rolled back是，一旦錯誤發生，執行將被中斷。

所以t1table 永遠不會被截斷， (-1) in 的插入t2將是rolled back.

現在應該如何編寫程式碼：

set xact_abort on;

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin try
begin transaction

   insert into dbo.t2
   values(-1);

   insert into dbo.t2
   select col1
   from dbo.t1;

   select @@trancount as [@@trancount before truncate];

   truncate table dbo.t1;

commit transaction;
end try

begin catch
   select @@trancount as [@@trancount in catch before rollback];
   if @@rowcount > 0 rollback;
   throw;
end catch;

您的程式碼應始終設置 xact_abort 並且它應該有try..catch塊。

你應該rollback從catch廣告throw中做error

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