ログテーブルへログを登録する - 月のあかり

複雑なストアドでエラーが発生した場合、どのような状態でエラーになったのかを検証することが難しいというケースはよくある。特にビジネスロジックを組み合わせた処理で問題が発生すると業務に支障を最小限とするためストアドの実行をロールバックする仕組みを採用していることが多いだろう。

ロールバックは「データを健全に保つ」意味で非常に大事だといことはよく理解されているが、実際には何らかの問題が発生してロールバック処理が実行された原因を追究しなければならない。適切にログを残していたつもりがロールバック処理でログも残っていなかったという経験があるのではないだろうか？

今回はロールバック処理でもログを残せる方法を一例にしながら

■SQL Server 2005 における
　#table(一時テーブル) と@table(変数テーブル) の違いを書いてみたい

◇#table(一時テーブル)の作成

CREATE TABLE #AAA
([Ref_Date] DATETIME
,[Ref_Num] INT
,[Ref_Nomen] NVARCHAR(50)
)

◇@table(変数テーブル)の作成

DECLARE @LOG_TABLE AS TABLE
([Ref_Date] DATETIME
,[Ref_Num] INT
,[Ref_Nomen] NVARCHAR(50)
)

◇一時テーブルはロールバックしてしまう
前述のように、一時テーブルのデータはトランザクションロールバックが実行されるとロールバックする。

上の結果表示で'ROLLBACK 後' の結果表示が出てないことがわかる。

◇変数テーブルはロールバックしない

一時テーブルの結果と違い'ROLLBACK 後' の結果表示が出ている。

◇ログテーブルにデータを格納し、一時テーブルで保存した場合
実行したコード

/* #Table(シャープテーブル)はロールバックする*/
DECLARE @Ref_Status NVARCHAR(255)
DECLARE @Object_Nomen NVARCHAR(50)
DECLARE @Seq_Num INT
SET @Object_Nomen = 'TEST_Object'
SET @Ref_Status = 'TEST Status'
SET @Seq_Num = 0
/*(1) 一時テーブルを作成*/
CREATE TABLE #LOG_TABLE
([Log_Date] DATETIME
,[Local_Num] INT
,[DB_Nomen] NVARCHAR(50)
,[Ref_Status] NVARCHAR(255)
,[Object_Nomen] NVARCHAR(50)
,[Record_Date] DATETIME
,[Recorder_Num] NVARCHAR(20)
)
BEGIN TRY
BEGIN TRAN
/*▼(2) 処理を実行*/
WHILE (SELECT @Seq_Num) < 5
BEGIN
SELECT TOP 1 @Seq_Num Cnt_Num, PostalCode7, 都道府県名, 市区町村名, 町域名 FROM M_郵便番号
SET @Seq_Num = @Seq_Num + 1
/*処理内容を一時テーブルに格納*/
INSERT INTO #LOG_TABLE
SELECT GETDATE(),@Seq_Num,'TEST_DB',@Ref_Status,'TEST_OBJECT',GETDATE(),'TEST_RECORDER'
/*ループの3回目でERROR を強制発行*/
IF @Seq_Num = 3 BEGIN
RAISERROR (@Ref_Status, 18, 1)
END
CONTINUE
END
/*▲*/
COMMIT TRAN -- TRANSACTION COMMIT
END TRY
BEGIN CATCH
/*ロールバック処理*/
ROLLBACK TRAN
/*エラーログを格納*/
INSERT INTO dbo.log_status
SELECT * FROM #LOG_TABLE
END CATCH
DROP TABLE #LOG_TABLE
SELECT * FROM dbo.log_status
DELETE FROM dbo.log_status

上記の例では郵便番号テーブルを５回一行ずつ出力します。繰り返す間にログを一時テーブルに格納し、３回目でRAISERROR()関数でエラーを強制的に発生さる。
TRY CATCH の中でロールバックしたあと実テーブルへログデータを登録する。

結果表示

最後の実テーブルをSELECT した結果表示にログデータがないことがわかる。

◇ログテーブルにデータを格納し、変数テーブルで保存した場合
実行したコード

/* @Table(変数テーブル)はロールバックしない*/
DECLARE @Ref_Status NVARCHAR(255)
DECLARE @Object_Nomen NVARCHAR(50)
DECLARE @Seq_Num INT
SET @Object_Nomen = 'TEST_Object'
SET @Ref_Status = 'TEST Status'
SET @Seq_Num = 0
/*(1) 一時テーブルを作成*/
DECLARE @LOG_TABLE AS TABLE
([Log_Date] DATETIME
,[Local_Num] INT
,[DB_Nomen] NVARCHAR(50)
,[Ref_Status] NVARCHAR(255)
,[Object_Nomen] NVARCHAR(50)
,[Record_Date] DATETIME
,[Recorder_Num] NVARCHAR(20)
)
BEGIN TRY
BEGIN TRAN
/*▼(2) 処理を実行*/
WHILE (SELECT @Seq_Num) < 5
BEGIN
SELECT TOP 1 @Seq_Num Cnt_Num, PostalCode7, 都道府県名, 市区町村名, 町域名 FROM M_郵便番号
SET @Seq_Num = @Seq_Num + 1
/*処理内容を一時テーブルに格納*/
INSERT INTO @LOG_TABLE
SELECT GETDATE(),@Seq_Num,'TEST_DB',@Ref_Status,'TEST_OBJECT',GETDATE(),'TEST_RECORDER'
/*ループの3回目でERROR を強制発行*/
IF @Seq_Num = 3 BEGIN
RAISERROR (@Ref_Status, 18, 1)
END
CONTINUE
END
/*▲*/
COMMIT TRAN -- TRANSACTION COMMIT
END TRY
BEGIN CATCH
/*ロールバック処理*/
ROLLBACK TRAN
/*エラーログを格納*/
INSERT INTO dbo.log_status
SELECT * FROM @LOG_TABLE
END CATCH
SELECT * FROM dbo.log_status
DELETE FROM dbo.log_status

一時テーブルで実施したコードを変数テーブルで実現してみた。
結果表示

最後の実テーブルをSELECTした結果にデータが格納されていることがわかる。

◇ロールバックの影響をよく考える
今回紹介したのは開発作業において処理内容をログ出力するような機能を提供する場合に役に立つという一例と、ストアドの実行にはトランザクション処理（コミット/ロールバック）は欠かせないものであり、使われる変数テーブルや一時テーブルがどのように作用するのかということを理解することに役立つと考えている。

使い方を誤ると、業務そのものに支障をきたすような場面もあり、システムの健全性・確実なトレース機能の実装・もd内が発生したときにトレース情報を確実かつ画一的な検証を可能にする機能実装を考慮していく必要がある。
その意味で、ストアドの書き方、ロールバックのかけ方、ログの出し方などプロジェクトで明確な方針を決めて作りこむことを推奨したい。