SQL Server TransAction 全部復原

使用的表結構如下：

USE [TestDB]
GO
/****** 對象:  Table [dbo].[Person]    指令碼日期: 11/23/2008 13:37:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
    [PersonId] [nchar](18) NOT NULL,
    [PersonName] [nchar](20) NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [PersonId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

 

預設情況下如果執行一個事務中出現錯誤，則只復原錯誤動作陳述式（就是說這句不執行了，算不上復原），錯誤處之前或之後的正確動作陳述式還是會被提交。如：

Use TestDB

Begin TransAction
    Insert Into Person(PersonId,PersonName)
                Values('1','Name1')
    Insert Into Person(PersonId,PersonName)
                Values('1','Name1')
    Insert Into Person(PersonId,PersonName)
                Values('3','Name3')
Commit TransAction
/*
    Select 一下 有'1','Name1'和'3','Name3'，
    說明只有第二句的錯誤被取消了
*/

 

全部復原的方法1：開啟 XACT_ABORT

Use TestDB
SET XACT_ABORT ON -- 開啟
Begin TransAction
    Insert Into Person(PersonId,PersonName)
                Values('1','Name1')
    Insert Into Person(PersonId,PersonName)
                Values('1','Name1')
    Insert Into Person(PersonId,PersonName)
                Values('3','Name3')
Commit TransAction
/*
    當 SET XACT_ABORT 為 ON 時，
    如果執行 Transact-SQL 語句產生執行階段錯誤，
    則整個事務將終止並復原。 
    預設情況下它是OFF狀態。
*/

 

全部復原方法2：使用Try...Catch

Use TestDB
Begin Try
    Begin TransAction
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
        Insert Into Person(PersonId,PersonName)
                    Values('3','Name3')
    Commit TransAction
End Try
Begin Catch
    Rollback TransAction
End Catch
/*
    使用TryCatch來捕獲異常。
    如果 TRY 塊內產生的錯誤導致當前事務的狀態失效，
    則將該事務歸類為不可提交的事務。
    如果通常在 TRY 塊外中止事務的錯誤在 TRY 內發生時，
    就會導致事務進入不可提交狀態。
    不可提交的事務只能執行讀操作或 ROLLBACK TRANSACTION。
    該事務不能執行任何可能產生寫操作或 COMMIT TRANSACTION 的 Transact-SQL 語句。
    如果事務被分類為不可提交的事務，則 XACT_STATE 函數會傳回值 -1。
*/

 

全部復原方法3：自訂錯誤變數

Use TestDB
Declare @tranError int -- 定義變數
Set @tranError=0
    Begin TransAction
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
            Set @tranError = @tranError + @@Error
        Insert Into Person(PersonId,PersonName)
                    Values('1','Name1')
            Set @tranError = @tranError + @@Error
        Insert Into Person(PersonId,PersonName)
                    Values('3','Name3')
            Set @tranError = @tranError + @@Error
    If @tranError = 0
        Commit TransAction
    Else
        Rollback TransAction
/*
    自訂一個變數來判斷最後是否發生過錯誤。
*/

 

 

最後要注意的是：如果一個事務寫了 Begin TransAction 而沒寫 Commit TransAction 或 Rollback TransAction 則相關操作的資料（也許是表，也許是列，這我還沒測試。。。）會被鎖住。。。而對於鎖住的解決辦法就是單獨執行一下Commit TransAction 或 Rollback TransAction