トランザクションをロールバックしたときに、一部の更新をコミットする方法です。
たとえば、次のような場合です。
データベース処理中の記録を専用のテーブルに残したい。
もし、処理中にエラーが発生し、ロールバックした場合にでも、記録は残しておきたい。
ここでは、テーブル変数への更新は、トランザクションに影響されないことを使っています。
米田さんの提案と、PASSJ-ML での内容を元にまとめました。
メッセージキュー版とあわせて検討していただければと思います
■実行例
-- テーブル TData, TLog を初期化
TRUNCATE TABLE TData
TRUNCATE TABLE TLog
-- 実行 1 成功する
EXEC usp_S1 1
SELECT * FROM TData
SELECT * FROM TLog
-- 実行 2 エラーになる
EXEC usp_S1 2
SELECT * FROM TData
SELECT * FROM TLog
■実行例結果
- テーブル TData
テーブル TLog
| ID |
Spid |
ProcId |
User |
Label |
Body |
Updated |
| 0 |
54 |
146099561 |
dbo |
usp_S1:開始 |
開始 @v = 1 |
2004-06-22 10:23:04.973 |
| 0 |
54 |
146099561 |
dbo |
usp_S1:@v |
INSERT @v 成功 |
2004-06-22 10:23:04.973 |
| 0 |
54 |
146099561 |
dbo |
usp_S1:@v+1 |
INSERT @v + 1 成功 |
2004-06-22 10:23:04.973 |
- テーブル TData
テーブル TLog
| ID |
Spid |
ProcId |
User |
Label |
Body |
Updated |
| 0 |
54 |
146099561 |
dbo |
usp_S1:開始 |
開始 @v = 1 |
2004-06-22 10:23:04.973 |
| 0 |
54 |
146099561 |
dbo |
usp_S1:@v |
INSERT @v 成功 |
2004-06-22 10:23:04.973 |
| 0 |
54 |
146099561 |
dbo |
usp_S1:@v+1 |
INSERT @v + 1 成功 |
2004-06-22 10:23:04.973 |
| 0 |
54 |
146099561 |
dbo |
usp_S1:開始 |
開始 @v = 2 |
2004-06-22 10:23:04.983 |
| 2627 |
54 |
146099561 |
dbo |
usp_S1:エラー |
INSERT エラー @v = 2 |
2004-06-22 10:23:04.983 |
■テーブル定義 TData
CREATE TABLE [dbo].[TData] (
[v] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TData] WITH NOCHECK ADD
CONSTRAINT [PK_TData] PRIMARY KEY CLUSTERED
(
[v]
) ON [PRIMARY]
GO
■テーブル定義 TLog
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TLog]
GO
CREATE TABLE [dbo].[TLog] (
[Id] [int] NULL ,
[Spid] [int] NOT NULL ,
[ProcId] [int] NULL ,
[User] [nvarchar] (255) COLLATE Japanese_CI_AS NOT NULL ,
[Label] [nvarchar] (100) COLLATE Japanese_CI_AS NULL ,
[Body] [nvarchar] (1000) COLLATE Japanese_CI_AS NULL ,
[Updated] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TLog] ADD
CONSTRAINT [DF_TLog_Spid] DEFAULT (0) FOR [Spid],
CONSTRAINT [DF_TLog_Updated] DEFAULT (getdate()) FOR [Updated]
GO
■ストアドプロシージャ usp_S1
TData に @v の値と、@v + 1 の値を挿入します。
いずれかが挿入できなかった場合、すべてロールバックします。
ALTER PROCEDURE usp_S1
(
@v INT
)
AS
BEGIN
SET NOCOUNT ON
-- エラー発生後も処理を継続
SET XACT_ABORT OFF
-- エラーログ用テーブル
DECLARE @TLog TABLE
(
[Id] INT,
[Spid] INT,
[ProcId] int,
[User] NVARCHAR(255),
[Label] NVARCHAR(100),
[Body] NVARCHAR(1000),
[Updated] DATETIME
)
DECLARE @ErrCount INT
SET @ErrCount = 0
DECLARE @Id INT
DECLARE @Body NVARCHAR(1000)
BEGIN TRANSACTION
-- ログ出力
SET @BODY = N'開始 @v = ' + CONVERT(NVARCHAR, @v)
INSERT INTO @TLog
VALUES (0, @@SPID, @@PROCID, CURRENT_USER,
N'usp_S1:開始', @BODY, getdate())
-- データ出力
INSERT INTO TData
VALUES(@v)
SET @Id = @@ERROR
IF @Id > 0
BEGIN
-- ログ出力
SET @BODY = N'INSERT エラー @v = ' + CONVERT(NVARCHAR, @v)
INSERT INTO @TLog
VALUES (@Id, @@SPID, @@PROCID, CURRENT_USER,
N'usp_S1:エラー', @BODY, getdate())
GOTO END_ERR
END
-- ログ出力
SET @BODY = N'INSERT @v 成功'
INSERT INTO @TLog
VALUES (0, @@SPID, @@PROCID, CURRENT_USER,
N'usp_S1:@v', @BODY, getdate())
-- データ出力
INSERT INTO TData
VALUES(@v + 1)
SET @Id = @@ERROR
IF @Id > 0
BEGIN
-- ログ出力
SET @BODY = N'INSERT エラー @v + 1 = ' + CONVERT(NVARCHAR, @v + 1)
INSERT INTO @TLog
VALUES (@Id, @@SPID, @@PROCID, CURRENT_USER,
N'usp_S1:エラー', @BODY, getdate())
GOTO END_ERR
END
-- ログ出力
SET @BODY = N'INSERT @v + 1 成功'
INSERT INTO @TLog
VALUES (0, @@SPID, @@PROCID, CURRENT_USER,
N'usp_S1:@v+1', @BODY, getdate())
END_OK:
-- コミット
COMMIT TRANSACTION
GOTO END_ALL
END_ERR:
-- ロールバック
ROLLBACK TRANSACTION
GOTO END_ALL
END_ALL:
-- ログを TLog に保存
INSERT INTO TLog
SELECT * FROM @TLog
END
ぜひ、コメントをいただければと思います。