前回の記事「行レベルのバージョン管理の隠れたコスト」で学習したように,SQL Server 2005においてトリガーは,「行レベルのバージョン管理(RLV)」と呼ばれる新しい技術を使っている。トリガー・コードの内側では,以前と同様に「inserted」や「deleted」と呼ばれる2種類の仮のテーブルにアクセスできる。実を言うと,これら仮のテーブルで参照できる変更済みデータの旧バージョンと新バージョンは,SQL Server 2005ではRLVを使って管理されているのだ。
筆者は前回の記事で,バージョン・ストアが筆者のトリガーによって処理された列を含んでいることを説明するために,スナップショット・アイソレーションが有効になっていないデータベースにおけるトリガーの例を紹介した。データベース・アドミニストレータ(DBA)は,「たとえスナップショット・アイソレーションを使わない場合でも,tempdbでバージョン・ストアを管理する必要がある」という点を,筆者は強調した。
今回も引き続きトリガーがバージョン・ストアに与える影響を考察していこう。特に,スナップショット・アイソレーションが有効になっているデータベースを見ていく。なおここでは,AFTERトリガーのみを取り上げる。SQL Server 2005は,INSTEAD OFトリガーに渡されるinsertedテーブルとdeletedテーブルの処理にバージョン・ストアを使わないからだ。
トリガーとバージョン・ストア
バージョン・ストア内の行数を調べるには,以下のシンプルなクエリーを実行すればいい。
SELECT count(*) AS NumRows FROM sys.dm_tran_version_store] リスト1のコードを実行して,筆者が前回使ったDepartmentテーブルをもう一度作成しよう(スペースの都合上,本記事に掲載されているコードのいくつかの行はワードラップ処理されている)。今回,このスクリプトはテーブル上にいかなるトリガーも作成しないが,スナップショット・アイソレーションを有効にするという働きがある。
リスト1●Departmentテーブルを作ってスナップショット・アイソレーションを可能にする
-- Turn off the snapshot options to start with ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT OFF GO USE AdventureWorks GO -- Make a copy of the Department table (15 rows) IF OBJECTPROPERTY(object_id('Department'), 'IsTable') = 1 DROP TABLE Department GO SELECT * INTO Department FROM HumanResources.Department GO ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON GO
続いて以下のUPDATEを実行して,バージョン・ストア内の行数を調べよう。
UPDATE Department SET ModifiedDate = getdate() WHERE DepartmentID = 11;] 前回,UPDATEトリガーを持ち,スナップショット・アイソレーションを持たないデータベース内でこのUPDATEステートメントを実行したときは,バージョン・ストア内に二つの行が生成された。だが今回は,一つの行しかないはずだ。格納する必要があるのは,「department 11」に対応する行の前のバージョンだけだからだ。スナップショット・アイソレーションを通してこのテーブルにクエリーを出している他の接続は,そのバージョンの行を使うことができる。
前回の記事中にあるDELETEステートメントを実行し,さらにバージョン・ストアに行を追加する他の何らかのステートメントを実行してから1分以上が経過している場合,バージョン・ストア内には一つの行があるはずだ。
トリガーとスナップショット・アイソレーション
それではトリガーを作成して,トリガーとスナップショット・アイソレーションの両方を使ったときに何が起きるのか見てみよう。リスト2のコードを実行して,前回と同様に,バージョン・ストアのサイズのみをレポートするトリガーを作成しよう。トリガー作成後,リスト3にあるUPDATEとDELETEのステートメントを実行しよう。
リスト2●二つのトリガーを生成する
CREATE TRIGGER upd_Department ON Department FOR UPDATE AS SELECT count(*) AS NumRows FROM sys.dm_tran_version_store GO CREATE TRIGGER del_Department ON Department FOR DELETE AS SELECT count(*) AS NumRows FROM sys.dm_tran_version_store GO
リスト3●トリガーとスナップショット・アイソレーションの両方を使用するUPDATEとDELETE
UPDATE Department SET ModifiedDate = getdate() WHERE DepartmentID = 7; DELETE Department WHERE DepartmentID = 11
行の数が累計でないことに,注意しよう。トリガーだけを使って,スナップショット・アイソレーションを使わなかったとき,バージョン・ストア内のUPDATEに対応する行は二つだった。そしてスナップショット・アイソレーションだけを使って,トリガーを使わずにUPDATEを実行した後だと,行の数は一つだった。だがトリガーとスナップショット・アイソレーションの両方を使用しても,行の数は二つだけなのである。
DELETEの場合,バージョン・ストア内に追加で必要な行は一つだけだ。SQL Serverは,バージョン・ストア内にあるトリガー用の行とスナップショット・アイソレーション用の行を別々の目的に使用するが,情報の複製は行わない。UPDATEトリガーとスナップショット・トランザクションはどちらも,「department 7」行の前のバージョンに対応するバージョン・ストア行を一つ使うことができる。同様に, DELETEを実行するときに必要なバージョン管理された行は,一つだけだ。スナップショット・トランザクションとDELETEトリガーの両方が,この行を使うことができるからだ。
UPDATEトリガーとバージョン・ストア
それでは少しレベルを上げて,実際に変更を加えるトリガーを見てみよう。