PR
 トリガーは,古くからあるSQL Serverの機能だ。だが,MicrosoftはSQL Server 2005でトリガーの再設計を行ったため,内部の細かい仕様は以前とはかなり異なるものになっている。

 前回の記事「行レベルのバージョン管理の隠れたコスト」で学習したように,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トリガーとバージョン・ストア

 それでは少しレベルを上げて,実際に変更を加えるトリガーを見てみよう。