[SQL]Table-Variable in Transaction

2012-05-02

如果在交易之中需要記錄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;

以上，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;

Hi,

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

請大家繼續支持 ^_^

亂馬客