[SQL]Table-Variable in Transaction

  • 4167
  • 0
  • SQL
  • 2012-05-14

[SQL]Table-Variable in Transaction

問題

如果在交易之中需要記錄LOG,即使發生錯誤,交易要回覆到最初的狀態,但是需要記錄LOG。要如何處理呢?

資料準備

--建立t1 Table
CREATE TABLE t1(
c1 INT NOT NULL);

	
--設定PK欄位為c1
ALTER TABLE t1
ADD PRIMARY KEY  (c1);

	
--建立t1_log Table
CREATE TABLE t1_log(
c1 INT NOT NULL);

	
--開始新增資料到t1,同時也記錄到t1_log
BEGIN TRY
    BEGIN TRANSACTION;
    
    INSERT INTO t1 
    OUTPUT inserted.c1 into t1_log
    VALUES(1);
    
    INSERT INTO t1 
    OUTPUT inserted.c1 into t1_log
    VALUES(2);

	
    INSERT INTO t1 
    OUTPUT inserted.c1 into t1_log
    VALUES(2);

	
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
    ROLLBACK TRANSACTION;
END CATCH;

 

--因為發生鍵值重覆,所以資料都被RollBack了!
SELECT * FROM t1;
SELECT * FROM t1_log;

 

image

以上,t1在新增資料過程中,同時將資料新增到t1_log的Table之中,如果發生錯誤的話,t1及t1_log的資料全都被Rollback了!

解決方案

這時,可使用Table-Variable來記錄,因為它是不受交易影響的,所以先建立一個Table-Variable的變數來記錄資料,最後再把資料新增到t1_log之中,就可以了! SQL如下,

--使用  table-variable
DECLARE @t1 TABLE (c1 INT);

	
--開始新增資料到t1,同時也記錄到table-variable @t1
BEGIN TRY
    BEGIN TRANSACTION;

	
    INSERT INTO t1 
    OUTPUT inserted.c1 into @t1
    VALUES(1);
    
    INSERT INTO t1 
    OUTPUT inserted.c1 into @t1
    VALUES(2);

	
    INSERT INTO t1 
    OUTPUT inserted.c1 into @t1
    VALUES(2);

	
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
        ROLLBACK TRANSACTION;
END CATCH;

	
--因為發生鍵值重覆,所以資料都t1被RollBack了
--但是table-variable @t1不受交易控制,所以它的資料還在
--就可以把@t1的資料新增到t1_log Table之中
INSERT INTO dbo.t1_log
SELECT c1 FROM @t1;

	
SELECT * FROM t1;
SELECT * FROM t1_log;

	
--最後drop table
DROP TABLE t1;
DROP TABLE t1_log;

 

image

參考資料

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

INF:常見問題集 - SQL Server 2000 - 資料表變數 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^