[SQL SERVER][Memo]使用XACT_STATE確認失敗交易
交易處理作業應該每個人都會碰到，就好比提款作業，
當提款機正確吐鈔後就必須將金額從相關帳戶中減去，
這整個過程不是全部成功就是全部失敗，但難免會有交易失敗的情況發生(提款機現金不足)，
這時我們可以使用XACT_STATE來確認交易失敗就還原整個交易(rolled back the transaction)，
下面就來測試並記錄整個過程。
交易正常
--當Transact-SQL 陳述式產生執行階段錯誤時，
--SQL Server 是否自動回復目前的交易。
--當 SET XACT_ABORT 是 ON 時，
--如果 Transact-SQL 陳述式產生執行階段錯誤，就會終止和回復整個交易。
--當 SET XACT_ABORT 是 OFF 時，在某些情況下，
--只會回復產生錯誤的 Transact-SQL 陳述式，
--交易會繼續進行。隨著錯誤嚴重性而不同，即使 SET XACT_ABORT 是 OFF，
--也有可能回復整個交易。OFF 是預設值。
SET XACT_ABORT ON;
BEGIN TRANSACTION ATM
BEGIN TRY
DECLARE @money INT;
SET @money =1000;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
IF (XACT_STATE()) = 0
BEGIN
COMMIT TRANSACTION ATM
END
ELSE
BEGIN
ROLLBACK TRANSACTION ATM
END
執行成功。
當交易失敗但並未使用XACT_STATE判斷
SET XACT_ABORT OFF;
BEGIN TRANSACTION ATM
BEGIN TRY
DECLARE @money INT;
SET @money ='ricoisme';
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
COMMIT TRANSACTION ATM
由於交易失敗所以不應該commit transaction，故產生無法認可目前的交易錯誤訊息。
利用XACT_STATE判斷
SET XACT_ABORT OFF;
BEGIN TRANSACTION ATM
BEGIN TRY
DECLARE @money INT;
SET @money ='ricoisme';
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
IF (XACT_STATE()) = 0
BEGIN
COMMIT TRANSACTION ATM
END
ELSE
BEGIN
ROLLBACK TRANSACTION ATM
END
因為使用XACT_STATE判斷交易是否失敗，在交易失敗後應該要還原而非認可該交易，而且也不會跳出相關錯誤訊息。
參考