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. まとめ

今回の説明は以上です。間違い、指摘点などありましたらご連絡ください。