SQL Server 2005 から CLR 統合機能として ストアドプロシージャや関数、ユーザー定義型、トリガーを .NET で記述できるようになりました。今回は CLR トリガーを試しに作成してみたのでその覚書を記載します。CRL トリガーについての説明は次のリンクを参照してください。
CLR トリガー
http://msdn.microsoft.com/ja-jp/library/ms131093.aspx
今回作成するCLR トリガーはシンプルな機能で、追加および削除されたレコードの内容を xml で出力ストリームに出力します。Management Studio 上で トリガーが起動すると 追加削除されたレコードがメッセージタブに出力されます。
検証環境は次の通りです
- Visual Studio 2010 Premium, .NET 3.5
- SQL Server 2008 R2
デプロイは Visual Studio のソリューションエクスプローラーから直接行う方法と、手動で T-SQL で行う方法を記載しています。
1. CLR 統合を有効にする
SQL Server で CLR 統合が無効(既定) の場合、有効にしておきます。SQL Server Management Studio を起動して対象のSQL Serverインスタンスに接続し、次のクエリを実行します。
sp_configure 'clr enabled', 1 RECONFIGURE
作成するCLR トリガーがファイルなど外部リソースにアクセスする場合は データベースの TRUSTWORTHY を ON にします。本例では対象のDBを SampleDB としています。
ALTER DATABASE SampleDB SET TRUSTWORTHY ON GO
TRUSTWORTHYはCREATE ASSEMBLY で PERMISSION_SET を UNSET か EXTERNAL_ACCESS にする場合に設定します。(詳細なセキュリティ設定を行えばTRUSTWORTH は OFFのままでも動作させることができるみたいです。)
2. CLRトリガーのプログラムを作成する
Visual Studio を起動し、新規プロジェクトを作成します。今回は下図のように SQL Server プロジェクトテンプレートから Visual C# SQL CLR データベース プロジェクトテンプレートを選択して作成します。下図で ターゲットフレームワークを .NET Framework 3.5 としているので注意してください。本例ではソリューション名を CLRTriggerSample としました。
ソリューションエクスプローラー上で プロジェクトを右クリック→追加→トリガー を選択します。
新しい紅毛の追加画面で、 トリガーを選択します。なめを TestTrigger.cs として追加ボタンをクリックします。
ソースファイルが作成されるので、次のように作成します。トリガー用のスタティックメソッドとして TestTriger を定義しています。その他のメソッド ProcessInsert, ProcessDelete, ProcessOtherAction はトリガー起動の種別に応じてメッセージを作成し、 SqlPipe を使用して出力しています。今回はサンプルのためメッセージを出力していますが通常はメッセージを出力する処理は行わないです。
トリガー用のメソッド TestTrigger には SqlTriggerAttribute が付与されています。本設定は Visual Studio から直接CLRトリガーをデプロイするときに使用される設定です。手動でデプロイする場合はなくても問題はありません。SqlTrigger属性でトリガー名やトリガーを設定するテーブル名、トリガーが起動するイベントを指定できます。
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
// SqlTrigger 属性は Visual Studio から直接トリガーを
// デプロイする場合に使用する
// ターゲットの既存のテーブルまたはビューを入力して、属性行のコメントを解除します
// [Microsoft.SqlServer.Server.SqlTrigger (Name="TestTrigger", Target="Table1", Event="FOR UPDATE")]
[SqlTrigger(Name="trg_SampleTrigger", Target="TestTable", Event="FOR INSERT, UPDATE, DELETE")]
public static void TestTrigger()
{
// トリガーコンテキスト
SqlTriggerContext context = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
switch (context.TriggerAction)
{
case TriggerAction.Insert:
ProcessInsert(context, pipe);
break;
case TriggerAction.Update:
ProcessInsert(context, pipe);
ProcessDelete(context, pipe);
break;
case TriggerAction.Delete:
ProcessDelete(context, pipe);
break;
default:
ProcessOtherAction(context, pipe);
break;
}
// ユーザーのコードで置き換えてください
SqlContext.Pipe.Send("Trigger FIRED");
}
private static void ProcessInsert(SqlTriggerContext context, SqlPipe pipe)
{
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "select * from inserted for xml auto, type";
cmd.CommandType = CommandType.Text;
string msg = (string)cmd.ExecuteScalar();
pipe.Send(msg);
}
}
}
private static void ProcessDelete(SqlTriggerContext context, SqlPipe pipe)
{
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "select * from deleted for xml auto, type";
cmd.CommandType = CommandType.Text;
string msg = (string)cmd.ExecuteScalar();
pipe.Send(msg);
}
}
}
private static void ProcessOtherAction(SqlTriggerContext context, SqlPipe pipe)
{
pipe.Send(string.Format("Trriger Action {0}", context.TriggerAction.ToString()));
}
}
ソースをビルドしてエラーが発生しないことを確認してください。
3. Visual Studio からCLRトリガーのデプロイ
Visual Studio からデプロイを行うための設定を行い、実際にデプロイしてみます。
サーバーエクスプローラーを起動(メニューの表示→サーバーエクスプローラーをクリック)し、デプロイ対象のデータベースに接続を行っておいてください。
ソリューションエクスプローラー上で対象のプロジェクトを右クリック→プロパティをクリックします。プロパティ画面が表示されるので、データベースタブを選択します(下図参照)。 接続文字列で参照ボタンをクリックし対象のDBを選択します。アクセス許可のレベルで必要なアクセス許可のレベル(本例ではセーフ) を選択します。ファイルなど外部リソースにアクセスする場合は 外部, ネットワーク上のリソース、Unsafeコードにアクセスする場合はアンセーフなど必要なアクセス許可のレベルの応じて選択します。
本設定は デプロイ時に使用されるCREATE ASSEMBLY ステートメントに影響します
CREATE ASSEMBLY (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms189524.aspx
準備が整いました。
プロジェクトを右クリック→配置 をクリックします。デプロイがが成功すると出力ウィンドウやステータスバーに 配置正常終了 や 配置完了というメッセージが表示されます。
デプロイに使用されるSQLはビルドファイルの出力フォルダに配置されています。中身を確認すると アセンブリ,CLRトリガーのDrop後 アセンブリの登録と CLRトリガーの設定を行っていることがわかります。
デプロイ後動作確認が行えます。その前に 手動でデプロイする手順も記載します。
4. SQL Server Management Studio を使用したT-SQLによる手動デプロイ
T-SQL によるデプロイは簡単です。下記クエリサンプルを SQL Server Management Studio 上で実行します。 アセンブリの登録を行い トリガーの設定を行っています。外部リソースにアクセスしないので、 CREATE ASSEMBLY の Permission_set を SAFE にしています。トリガー作成用のステートメントでは EXTERNAL NAME で CREATE ASSEMBLYで 登録した CLRのアセンブリ名に名前空間、クラス名を含むトリガー用のメソッド名を指定しています。
use SampleDB;
GO
-- アセンブリとトリガーを削除する場合
-- DROP ASSEMBLY [CLRTriggerSample];
-- DROP TRIGGER trg_SampleTrigger;
-- アセンブリ登録
CREATE ASSEMBLY [CLRTriggerSample]
FROM 'C:\project\CLRTriggerSample\CLRTriggerSample\bin\Debug\CLRTriggerSample.dll'
WITH PERMISSION_SET = SAFE;
GO
-- トリガー設定
CREATE TRIGGER [dbo].[trg_SampleTrigger]
ON [dbo].[TestTable]
FOR INSERT, DELETE, UPDATE
AS EXTERNAL NAME [CLRTriggerSample].[Triggers].[TestTrigger];
GO
sys.assemblies システムビューを確認すると アセンブリが登録されていることが確認できます。
5. 動作確認
SQL Server Management Studio 上で クエリを作成し トリガーを設定したテーブル(本例では TestTable) に対して INSERT を行うとクエリ結果のメッセージタブに次のようなメッセージが出力されます(テストように作成したテーブルのスキーマによって内容は異なります)。
<inserted id="8" code="1002" value="12" description="Desc2" /> Trigger FIRED
同様にdeleteを行うとメッセージタブに次のようなメッセージが出力されます。
<deleted id="10" code="1003" value="13" description="Desc2" /><deleted id="9" code="1002" value="12" description="Desc2" /> Trigger FIRED
うまく動作しない場合は 冒頭で記載した CLR が有効になっていることを確認してください。CLRトリガーが外部リソースにアクセスする場合はTRUST WORTH がONになっていることを確認してください。
パフォーマンスですが、最初の1回目(初回起動)のトリガーの起動は少し(1秒もないと思いますが) 時間がかかります。それ以降はManagement Studio 上で操作する限りは特に気になりませんでした。CLR統合機能を行った方が効率のよい(もしくはCLR統合機能を使わないと実現できない)場合にしか使用しないと思うのでT-SQLのみのトリガーとパフォーマンスの比較を考えるのはあまり意味がないかもしれませんが。
6. まとめ
今回の説明は以上です。間違い、指摘点などありましたらご連絡ください。
さんのコメント: さんのコメント: