SQL
データベースを更新するコード
普通にSQLクエリアナライザを使ってデータベースを更新するときは以下のようにすると思います。
1: use DatabaseName
2: go
3: create table mytable (
4: cd varchar(6) not null,
5: nm varchar(50) not null,
6: mkday datetime not null default(getdate()),
7: constraint pk_mytable primary key (
8: cd
9: )
10: )
11: insert into mytable (cd, nm) values ('000001', 'あいうえお')
12: insert into mytable (cd, nm) values ('000002', 'かきくけこ')
13: go
14: create view mytableview
15: as
16: select
17: cd,
18: nm,
19: convert(varchar(23), mkday, 121) as mkday
20: from
21: mytable
22: go
もちろんエラーがなければ全く問題ないのですが、何かエラーがあったときは部分的に更新されてしまうため、どこが更新されたのか、どこが更新されなかったのかわからなくなり、結局アップデートに失敗して復旧に大変な手間をかけなければならなくなることもしばしばあります。
SQLクエリアナライザでは何も指示しなければ自動コミットモードで動作します。この場合各バッチ（「go」で区切られた各部分）はランタイムエラーがなければコミットされ、エラーがあればロールバックします。バッチが複数あるとそれぞれにエラーの有無でロールバックしたりコミットしたりと成否がわかれてしまうわけです。
そこで、アップデートに失敗したときにアップデートコード全体をロールバックできないかと考えたのが次のコードです。スクリプト全体をひとつのトランザクションで管理するのではなく、必要に応じてトランザクションを開始したりロールバックしたりして制御します。
1: set implicit_transactions on
2: set xact_abort on
3: set nocount on
4:
5: begin tran
6: begin tran
7: go
8:-- ここにアップデーコードを書く
9:
10:
11: go
12: if @@trancount > 1
13: begin
14: while @@trancount > 0
15: commit
16: print 'アップデートは正常に終了しました。'
17: end
18:
19: else
20: begin
21: if @@trancount > 0
22: rollback
23: print 'このアップデートは適用されません。'
24: end
25:
26: set nocount off
27: set xact_abort off
28: set implicit_transactions off
「set implicit_transactions on」で暗黙のトランザクションモードを使うことを指示します。トランザクションの必要な処理を行うと自動的にトランザクションを開始します。併せて「set xact_abort on」でエラー時に自動的にトランザクションをロールバックし、バッチを終了させることも指示しておきます。この２つによって、どこでもトランザクションが自動的に開始され、エラーでロールバックします。処理中のトランザクションは明示的にコミットされない限り完了しません。それらは放っておけば接続が切れたときにロールバックされます。
最終的にエラーがあったのかどうかを調べるために、はじめに複数の（＝ネストされた）トランザクションを開始しておきます。アップデートコードのどこかでエラーがあった場合、トランザクション全体がロールバックされ、トランザクションの数（深さ）は０になります。そのあと自動的に開始されるかもしれないトランザクションの数は１より大きくならないので、最終的にトランザクションの数が１よりも大きければエラーがなかったことをそうでなければエラーがあったことを検出できます。
最初の明示的なトランザクションの開始の前に暗黙のトランザクションが開始されるようなので「begin tran」は１つでも良いのですが、コードが複数のトランザクションの中で動作していることを理解できるように２つ書いておきます。
トランザクション全体をコミットするには全てのトランザクションをコミットする必要があります。ネストされたトランザクションのコミットは@@trancountをデクリメントする以外に何も引き起こさないため、確実に最初のトランザクションをコミットしなければなりません。
気をつけなければいけないことのひとつは「alter view」と「alter procedure」がトランザクションを開始させないことです。「drop」してから「create」するようにします。それと「raiserror」は例外を発生させますが、ランタイムエラーではないのでトランザクションをロールバックさせません。エラーとしたいところで「rollback」と「return」を記述しておきます。
※ヘルプには暗黙のトランザクションを開始させるとは書いてないのですが、やってみると「alter view」と「alter procedure」は暗黙のトランザクションを開始させます。
コンパイルエラーでは、そのバッチは構築に失敗して全体を実行しませんが、他のバッチは実行します。そして@@errorをセットしますが、トランザクションをロールバックしません。したがって各バッチのあとでエラーがないか調べる必要があるようです。
出力に日時を追加し、実際に使用しているのは、次のようなコードです。多分時間は正確ではありませんが、作業のログとしても利用しています。
set implicit_transactions on
set xact_abort on
set nocount on
print char(13) + char(10)
print 'ScriptName, Copyright (C) yyyy, CompanyName'
print char(13) + char(10)
print convert(varchar(23), getdate(), 121) + ' アップデートを開始します。'
begin tran
begin tran
go
create table mytable (
cd varchar(6) not null,
nm varchar(50) not null,
mkday datetime not null default(getdate()),
constraint pk_mytable primary key (
cd
)
)
print convert(varchar(23), getdate(), 121) + ' mytableを作成しました。'
insert into mytable (cd, nm) values ('000001', 'あいうえお')
insert into mytable (cd, nm) values ('000002', 'かきくけこ')
print convert(varchar(23), getdate(), 121) + ' mytableに'
+ convert(varchar(16), @@rowcount) + '件を追加しました。'
go
if @@error <> 0 begin if @@trancount > 1 rollback end
go
create view mytableview
as
select
cd,
nm,
convert(varchar(23), mkday, 121) as mkday
from
mytable
go
if @@error <> 0 begin if @@trancount > 1 rollback end
else print convert(varchar(23), getdate(), 121) + ' mytableviewを作成しました。'
go
if @@trancount > 1
begin
while @@trancount > 0
commit
print convert(varchar(23), getdate(), 121) + ' アップデートは正常に終了しました。'
end
else
begin
if @@trancount > 0
rollback
print convert(varchar(23), getdate(), 121) + ' このアップデートは適用されません。'
end
set nocount off
set xact_abort off
set implicit_transactions off
ScriptName, Copyright (C) yyyy, CompanyName
yyyy/MM/dd HH:mm:ss.mmm アップデートを開始します。
yyyy/MM/dd HH:mm:ss.mmm mytableを作成しました。
yyyy/MM/dd HH:mm:ss.mmm mytableに1件を追加しました。
yyyy/MM/dd HH:mm:ss.mmm mytableviewを作成しました。
yyyy/MM/dd HH:mm:ss.mmm アップデートは正常に終了しました。
各バッチの間にチェックコードを挿入するのはかなり面倒な作業です。もう少し工夫が必要かもしれません。
ランタイムエラーにはいくつか種類があるかもしれません。例えば以下のコードを実行すると、#tがなければコンパイルエラーではなくランタイムエラーが発生します。「set xact_abort on」としてあればロールバックするはずだと思うのですが、この場合はトランザクションをロールバックしません。
select * from #t