Access Web データベースから Oracle のトリガーを呼び出す（問題とその解決編）

さて、アプリケーションページには、Oracle のトリガーを呼び出すコードは書けているので、これを CLR トリガーに移植しようと粛々と作業。コンパイルが通り、DLL をデプロイしようとしたが、次々に起こる問題。

1. CLR トリガーそのものから、ODP.NET が呼び出せない

SqlAssembly 'Database1' の作成に失敗しました。(Microsoft.SqlServer.Smo)
追加情報:
→Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。
(Microsoft.SqlServer.ConnectionInfo)
→このクエリを実行するには、リソースプール 'default' のシステムメモリが不足しています。(Microsoft SQL Server、エラー:701）

うーーーーーむ。
これについては、いろいろ調べてみたのだけれども、DataSet クラスの使用することにコストがかかりすぎるということと、そもそも、SQL Server の CLR トリガーから、ODP.NET は呼び出せないのではないかというところに落ち着いた。DataSet クラスについては、DataReader への書き換えはできるが、ODP.NET が呼び出せないのであれば、CLR トリガーだけで処理はできないということになる。。。

脱力感に支配されながら、代替策を考える。

CLR トリガーから、シェルを呼び出して、コマンドとして実行する
CLR トリガーから、もともとのアプリケーションページを呼び出す

コマンドを呼び出しは古臭さが否めなかったし、アプリケーションページの呼び出しならば、そのまま使えそうだったので、後者をとる。CLR トリガーを全面的に書き直し、結局、HttpClient を呼び出すだけのコードになった。

    Dim wc As WebClient = New WebClient()
    wc.Credentials = New NetworkCredential(login, pwd)  ' SharePoint の基本認証
    wc.DownloadData(url)

2. CLR トリガーが外部リソースにアクセスするためには設定が必要

さて、生まれ変わった CLR トリガーのアセンブリは問題なくデプロイされた。SSMS から、INSERT 文を実行し、動きを確かめてみる。すると、

メッセージ 6522、レベル 16、状態 1、プロシージャ SqlTrigger1、行 1
ユーザー定義のルーチンまたは集計 "SqlTrigger1" を実行中に .NET Framework エラーが発生しました:
System.Security.SecurityException: 型 'System.Net.WebPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' のアクセス許可の要求に失敗しました。
System.Security.SecurityException:
場所 System.Net.WebClient.DownloadDataInternal(Uri address, WebRequest& request)
場所 System.Net.WebClient.DownloadData(Uri address)
場所 SalesQuotationTrigger.OrderQuotationTrigger.OnInsertUpdate()

うがっ。WebClient が呼べてない。。。これは少し調べると原因がわかった。
CLR トリガーには、権限の設定があるのだそうだ。
まずは、プロジェクトプロパティの Permission Level 、これを EXTERNAL_ACCESS にする。

これでビルドしたうえで、アセンブリの登録時に、権限セットを「外部アクセス」にする。

これで、再び実行すると、WebClient が呼べるようになったようだが、応答が返ってくるまでやたらと時間がかかる。うーん、これは明らかに WebClient のタイムアウトと思しき時間だなあ。。。重いのであまりやりたくないが、アプリケーションページをデバッグ起動してみる。すると、わかったのは、

3.CLR トリガーが呼び出されているとき、該当レコードは排他ロックされている

アプリケーションページは呼び出された先で、SQLServer を読みにいって、Oracle トリガーに渡す値を抽出するのだが、このとき、挿入、または更新された行は、排他ロックされており、SELECT ですら、ロックが解放されるまで待機してしまう。うーむ。
では、CLR トリガーで、ワークテーブルに同じ内容をコピーして、その内容を読み取るようにすればどうかと実装してみたところ、そのコピーしたレコードも排他ロックされていた。。。
結局、その実装はとっぱらい、アプリケーションページ側で、リクエストを受信したら、すぐにレスポンスを返すようにし、1秒後に別のスレッドで、これまでのコードを実行するようにしたら、ロックは解放された状態でテーブルを参照することができた。ここまでくると、なんだかアホらしくなってきたが、さあ、あとは削除だけだ。

4.削除トリガーが実行され、1 秒後には、該当レコードは消えている（あたりまえ）

ここまでの方法で削除トリガーを実装してみたら、該当レコードが取得できずにエラーとなってしまった。なんでやろ？と考えると、削除トリガーなのだから、ロックもなにも、1 秒後にはレコード自体が消えていることに気がついた。当たり前だ。
しぶしぶ、さっき実装したワークテーブルへのコピーのコードを復活させ、コピーの行を使って、値をとるようにした。CLR トリガー側でコピーした行は、アプリケーションページで削除するようにした。

やっとできた。Access Web データベースからの呼び出しも、多少の重さは感じるが、実用には問題ないレベルとなった。

いやー。
SQL Server ではリンクサーバーという機能があり、OLE データベースをリンクして、ビューとして見ることができたりするのだが、これで見えるのは、テーブルとビューだけで、さらに、OLE オブジェクトは、SQL Server のアーキテクチャからアクセスできないといけない。つまり、SQL Server が 64bit ならば、Oracle Client は 64bit でなければならない。この件も含めて、広くいろいろなことができるように見えるが、実現までには、たくさんのハードルがあるということがよくわかった。
Oracle のストアドを使って、外部ファイルを出力するとき、UTL_FILE を使えばいいが、SQL Server のストアドでは、ネイティブでファイル出力する機能はないらしく、結局、外部コマンドを呼び出すということになるらしい。だから、CLR トリガーや CLR ストアドが用意されているのだろうけれど、標準 API しか呼び出せないのであれば、ファイル出力とか、メール発信とかまでにとどめておいたほうがいいなあ。今回は、SharePoint があったから助かったし、Java EE のサーバーがあれば、同等のことはできるだろうけど、SQL Server しかない場合は、Oracle トリガーを呼び出すということはできないと言い切ったほうがいいのかもしれない。