Access Web データベースから Oracle のトリガーを呼び出す（基本実装編）

CLR トリガーは以下のようにつくる。

本家のチュートリアルはこちら → CLR トリガー

SharePoint Server または SQL Server が入っているコンピューターで Visual Studio を実行し、SQL Server プロジェクトをつくる。

プロジェクトプロパティを開けて、C# か VB.NET かを決める。

SQL CLR C#（VB.NET）というカテゴリーの、SQL CLR C# (VB.NET) Trigger という項目を追加する。

生成されたコードに追記したものがこちら。

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers{        
    // Enter existing table or view for the target and uncomment the attribute line

    // 以下は適切な名前に変える
    // Event は FOR INSERT,UPDATE,DELETE のようにカンマでつなげられる
    [Microsoft.SqlServer.Server.SqlTrigger
    (Name="SqlTrigger1", Target="Table1", Event="INSERT,UPDATE,DELETE")]
    public static void SqlTrigger1 (){
        // Replace with your own code
        // SqlContext.Pipe.Send で SQLServer Management Studio(SSMS) の
        // コンソールにメッセージが出力できる
        SqlContext.Pipe.Send("Trigger FIRED");

        SqlCommand command;
        SqlDataReader reader; 
        
        using (SqlConnection connection = new SqlConnection(@"context connection=true")){
            connection.Open();

            switch (SqlContext.TriggerContext.TriggerAction){
                // SqlContext.TriggerContext.TriggerAction で 何が起こったかわかる
                case TriggerAction.Insert:
                    command = new SqlCommand(@"SELECT * FROM INSERTED;",connection);
                    // INSERTED で INSERT された行
                    reader = command.ExecuteReader();
                    break;

                case TriggerAction.Update:
                    command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
                    // UPDATE も INSERTED でとれる
                    reader = command.ExecuteReader();
                    break;

                case TriggerAction.Delete:
                    command = new SqlCommand(@"SELECT * FROM DELETED;", connection);
                    // DELETE は DELETED でとる
                    reader = command.ExecuteReader();
                    break;

                default:
                    break;                
            }
        }

    }
}

どうやら、DataSet は使えないようなので、SqlDataReader でがんばること。

これをビルドすると、環境によっては、Visual Studio 上ではビルド失敗となるが、プロジェクトフォルダの obj フォルダに DLL の日付が変わっていれば、ビルドは成功している。これが CLR トリガーのライブラリになる。
（僕は SQLServer の入っているコンピュータで作らなかったので、ビルドには失敗したが、おそらく、SQLServer の入っているコンピュータでビルドすると、自動的にトリガーがデプロイされて、デバッグができるんだと思う。）

できた DLL を SSMS にデプロイするのだが、そのまえに、SSMS から、まず、CLR 統合の有効化のスクリプトを流す。

sp_configure 'clr enabled', 1
RECONFIGURE

つぎに、外部リソースへのアクセスの有効化のスクリプトを流す。

ALTER DATABASE databasename
SET TRUSTWORTHY ON

そして、DLL をデプロイする。トリガーを仕掛けたいデータベースから、プログラミング→アセンブリ→新しいアセンブリをえらぶ。

DLL を選択して OK を押す。

最後に、実テーブルに対して、トリガーをしかけるために、以下のようなスクリプトを流す。

USE databasename
GO

CREATE TRIGGER [SampleTable_Insert] ON [Access].[SampleTable]
   FOR INSERT
   AS  EXTERNAL NAME [Database1].[Triggers].[SqlTrigger1];
GO

これで CLR トリガーが呼び出せる。

ここまででハマりポイントがひとつある。トリガーを VB.NET でつくったとき、最後に CREATE TRIGGER するところで、

メッセージ 6505、レベル 16、状態 2、プロシージャ SampleTable_Insert、行 2
型 'Triggers' がアセンブリ 'Database1' に見つかりませんでした。

と出る。VB.NET で作ったときは、アセンブリのフルネームは、[Database1].[Database1.Triggers].[SqlTrigger1] となるらしい。デフォルトの名前空間をカブせて指定してください。

さあ、準備は整った。Access Services が作った SQLServer のテーブルに、Oracle のトリガーを呼び出す、CLR トリガーをコーディングしよう！道のりがまだ半分にも満たないことは、このときは知る由もなかった。。。（つづく）