摘要:SQL - 使用 SET XACT_ABORT
在寫 T-SQL 時一般來說都會在開頭加上 BEGIN TRANSACTION 與 結尾加上 COMMIT TRANSACTION 來讓中間的交易行為當遇到異常時，可以 Rollback 資料。但是往往有時候交易出現異常時，仍會有部分資料寫入資料表中，所以就可以使用 XACT_ABORT 來整批 Rollback，以下就來看看...
步驟一：建立兩個資料表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[測試資料表1](
[ID] [int] NOT NULL,
[uName] [nvarchar](50) NULL,
CONSTRAINT [PK_測試資料表1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[測試資料表2](
[ID] [int] NOT NULL,
[uName] [nvarchar](50) NULL,
CONSTRAINT [PK_測試資料表2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[測試資料表2] WITH CHECK ADD CONSTRAINT [FK_測試資料表2_測試資料表1] FOREIGN KEY([ID])
REFERENCES [dbo].[測試資料表1] ([ID])
GO
ALTER TABLE [dbo].[測試資料表2] CHECK CONSTRAINT [FK_測試資料表2_測試資料表1]
GO
步驟二：在 dbo.測試資料表1 中 Insert 幾筆資料
Code：
Insert into dbo.測試資料表1 Values(1, 'aaa')
Insert into dbo.測試資料表1 Values(3, 'bbb')
Insert into dbo.測試資料表1 Values(5, 'ccc')
Insert into dbo.測試資料表1 Values(6, 'ddd')
Insert into dbo.測試資料表1 Values(7, 'eee')
步驟三：測試 SET XACT_ABORT OFF
Code：
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO dbo.測試資料表2 VALUES (1, 1);
INSERT INTO dbo.測試資料表2 VALUES (2, 2); --錯誤的新增，因為在 dbo.測試資料表1 中沒有符合的 ID
INSERT INTO dbo.測試資料表2 VALUES (3, 5);
COMMIT TRANSACTION;
GO
結果：
步驟四：測試 SET XACT_ABORT ON
Code：
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO dbo.測試資料表2 VALUES (1, 1);
INSERT INTO dbo.測試資料表2 VALUES (2, 2); --錯誤的新增，因為在 dbo.測試資料表1 中沒有符合的 ID
INSERT INTO dbo.測試資料表2 VALUES (4, 5); --錯誤的新增，因為在 dbo.測試資料表1 中沒有符合的 ID
COMMIT TRANSACTION;
GO
結果：
呆言呆語：XACT_ABORT 預設是 OFF 的，當你 ON 完後，記得再恢復為 OFF 喔...
參考：
SET XACT_ABORT (Transact-SQL)
查詢是否有啟用 XACT_ABORT 選項