ケント・ミリガン著
V5R1 で DB2 UDB の SQL トリガーと新規外部トリガーが実現

V5R1でようやく iSeries に SQL ベースのトリガーがインプリメントされました。SQL トリガーは iSeries の DB2 ユニバーサル・データベース (DB2 UDB) に対する最も主要な V5R1 新機能です。

SQL CREATE TRIGGER ステートメントはオラクルのような他のデータベース製品の場合と変わりません。これにより顧客や ISV が望んでいたポータブルなトリガー方法が実現します。他のデータベースと違い DB2 UDB は SQL 標準に基づいてインプリメントされています。iSeries SQL トリガー・サポートは、他の DB2 UDB 製品で提供される標準サポートを超えた機能であることがおわかりになると思います。

非 SQL（外部）トリガー・サポートを改善する機能拡張はもちろん、SQL トリガーのインプリメントについて詳細に検証します。

新たなる柔軟性

V5R1 ではもはや（before update のような）イベント 1 つに対してトリガーが 1 個という制限はありません。テーブル 1 つにつき最大トリガー数（SQL トリガーと外部トリガー）は 6 個から 300 個です。最大数が増えたで、ソフトウェア・パッケージでイベント 1 つに対してトリガーが 1 個と定義されていたためテーブルのトリガーが定義できなかった、というケースはなくなります。

イベント 1 つに対してトリガーが複数ある場合は、DB2 UDB はトリガーが作成されたタイムスタンプを使用します。最も古いタイムスタンプ値を持つトリガーがまず DB2 UDB で起動されます。トリガーが複数ありその実行順序を変更する場合は、その順番どおりにトリガーを再作成する必要があります。

トリガーにより柔軟性を持たせている他の機能拡張として、新しく追加された CL コマンド CHGPFTRG（物理ファイル・トリガーの変更）があります。このコマンドでデータベース・トリガーを使用可能にしたり無効にしたりできます。以前のリリースでは、トリガーを無効にする場合はそのトリガーを削除し、再度定義するしかありませんでした。新しく追加されたこのコマンドにより、バッチ処理やデータのインポートなどトリガーに組み込まれたビジネス・ロジックが必要ない場合に、以前よりずっと簡単にトリガーを無効にできます。

ラージ・オブジェクト (LOB) データ・タイプ (BLOB、CLOB、DBCLOB) が V4R4 でサポートされたとき、LOB カラムでテーブルのトリガーを定義できないというマイナーな制限がありました。V5R1 でこの制限は解除されました。この機能拡張には興味深い副作用があります。それはシステム基盤が変更されたために、すべての外部トリガー・プログラムの入力として渡されるトリガー・バッファーのサイズが変更されることです。したがって既存のトリガー・プログラムは正しく動作しません。たとえば、（長さの変更をチェックせずにバッファー全体をデータ・キューに置くなど）トリガー・バッファーの長さが変更される可能性があるということを考慮できないトリガー・プログラムが影響を受けます。トリガー・バッファー・パラメーターの位置がハードコーディングされているトリガー・プログラムにも障害が発生する確率が高いのです。しかし、トリガー・バッファーに渡されたオフセットや長さを使用して正しくトリガー・バッファー値にアクセスしているトリガー・プログラムは V5R1 システムでも正常に動作します。

外部トリガーに悪影響を与える変更に SQL トリガーに権限を追加しなければならないという点があります。V5R1 以前は、内部テーブルに *READ 権限だけあればトリガーを定義できました。V5R1 では *OBJOPR 権限も必要です。また、ALWREPCHG(*YES) パラメーターを使用している場合、*UPD 権限と *OBJOPR 権限も必要です。

SQL トリガー・サポートの結果直接提供される外部トリガーの利点は、システム共通のカタログが使用できることです。カタログにより、どのトリガー（外部および SQL）がシステムに定義されているか照会できる単一のリポジトリーが提供されます。システムを V5R1 にアップグレードすると、DB2 は既存トリガーのカタログを自動的に作成します。

V5R1 では外部トリガーを再定義する必要はありませんが、トリガーにトリガー・プログラム名とは違う（最大 128 文字の）名前を割り当てる際に再定義する必要があります。名前を指定しない場合は、DB2 UDB が自動で名前を生成してくれます。トリガー名は新しく追加された CHGPFTRG コマンドで使用され、トリガー・カタログ・ビューで（ライブラリー名が付いた）基本キーの一部にもなります。

トリガー関連では、SYSTRIGGERS、SYSTRIGCOL、SYSTRIGDEP、SYSTRIGUPD の4 つのカタログ・ビューが QSYS2 に定義されています。外部トリガーの情報は SYSTRIGGERS ビューだけに含まれています。これらトリガー・ビューに含まれる情報の詳細は、「DB2 UDB for iSeries SQL リファレンス・マニュアル」の「付録 G」を参照してください。

トリガーの要件

SQL ストアード・プロシージャーまたは SQL 関数を以前のリリースでコーディングしてある場合は、iSeries で SQL トリガーを開発する際に多くの類似点を見つけることができるでしょう。トリガー内のビジネス・ロジックをコーディングするときに同じステートメントを使用するだけでなく、SQL トリガーは前述の SQL 機能のように C プログラム・オブジェクトとしてインプリメントされます。

C プログラム・オブジェクトはかつて ILE C コンパイラーが必要だというマイナス面を持っていましたが、V5R1 ではプロシージャー、関数、トリガーに関するその要件はなくなりました。SQL トリガーを実行するのに余分なライセンス製品は必要ありません。SQL トリガーを作成するには、iSeries ライセンス製品の DB2 UDB Query マネージャーと SQL Development Kit をインストールする必要があります。

トリガー・オプション

図 1 は CREATE TRIGGER 構文図を示し、SQL トリガー・インターフェースがサポートしているオプションの概要を理解する助けになります。ほとんどの場合 SQL トリガーは、外部トリガーが使用できない新機能とともに、現在の外部トリガーに同等の機能性を提供します。

テーブル同様トリガーには、スキーマ（ライブラリー）名を含む 2 セットの名前があります。トリガー名はスキーマ内で固有である必要があります。前述のように、ADDPFTRG CL コマンドは外部トリガーにも名前が割り当てられるよう機能拡張されています。

SQL トリガーは、テーブルの列の更新、削除、挿入といった外部トリガーと同じトリガー・イベントをサポートします。さらに SQL トリガーはすべてのカラムではなく、ある特定のカラムにだけ update トリガー・イベントを適用させることもできます。トリガー・イベントと特定のカラムまたはカラム・セットに範囲指定させる機能は SQL トリガーでのみサポートされています。UPDATE イベント指定に OF 節を追加することで範囲指定ができます。たとえば、「. . . UPDATE OF アドレス、都市、県、郵便番号」といった具合です。この SQL 固有のオプションにより、列のカラムが変更されるたびではなく、指定したカラム（複数の場合もあり）を変更したときだけトリガーが実行されることで、パフォーマンスが大幅に向上します。

同じ活動化（トリガー）時間が SQL トリガーと外部トリガー両方でサポートされています。活動化時間はデータベースにおいて トリガー・イベントが発生する前後になります。トリガーはしばしばイベント・プロパティーと時間プロパティーの組み合わせにより、「Before insertion」トリガーとか「After update」トリガーなどと呼ばれます。

前述のように複数のトリガーは同じトリガー・イベントとトリガー時間を持つことができます。標準では before SQL トリガーでは、トリガー・ロジックによるデータ変更はできません。SQL 標準により before トリガーは別の before トリガーを起動できないためです。その活動化は NO CASCADE 節で回避できます。したがってデータ変更ステートメント (INSERT、UPDATE、DELETE、CREATE) は Before トリガーでは使用できません。

SQL トリガーは外部トリガーができない制御を行うことができます。トリガーを起動させる SQL ステートメントはデータベースの列（複数の場合もあり）に影響を与えます。たとえば、SQL Delete ステートメントでテーブルから列を削除できます。

SQL トリガーを定義するときに、トリガーを SQL ステートメントに対して 1 回だけ起動するか (FOR EACH STATEMENT)、修正する各列に対して 1 回起動するか (FOR EACH ROW) を指定できます。これはトリガーの細分性または方向性と呼ばれ、それぞれステートメントレベル・トリガーまたは列レベル・トリガーと呼ばれます（外部トリガーは常に列レベル・トリガーです）。

Delete ステートメントで 1 列削除されると、ステートメントレベル・トリガーが一度起動してから、列レベル・トリガーが起動します。10 列削除する場合は、ステートメントレベル・トリガーは一度だけ起動しますが、列レベル・トリガーは 10 回起動します。ゼロ列を削除する場合、ステートメントレベル・トリガーは起動しますが、列レベル・トリガーは起動しません。ステートメントレベル・オプションは After トリガーでのみ指定できます。イベント 1 つに対して複数のトリガーが存在する場合は、ステートメントレベル・トリガーと列レベル・トリガーは、トリガーの作成順序に従い混在して実行されます。

トリガーの SQL 固有オプションはトリガー・モードです。トリガー・モードは DB2SQL や DB2ROW といった SQL トリガーに使用できます。DB2SQL は他の DB2 UDB 製品でサポートされているモードです。DB2ROW は常に外部トリガーで使用されるモードです。トリガー・モードによりトリガーの実行方法が多少変化します。DB2ROW モードを使用するとトリガーは各列が変更された後に起動されます。当然ですが、このモードは列レベル・トリガーでのみ指定できます。一方 DB2SQL モードは列がすべて変更されるまで待ち、それからトリガーを起動します。

この違いは列レベル対ステートメントレベルの細分性と同じではありません。違いをさらに理解するために、両モードを使用する delete 列レベル・トリガーの例を見てみましょう。triggering delete ステートメントが 10 列削除する場合、合計 10 回各列が削除されてから DB2ROW 版のトリガーが起動します。DB2SQL 版の delete トリガーも 10 回起動します。しかし、DB2SQL モードでは、10 列すべてが削除されるまでトリガーは起動されません。各列の削除に関するトリガー・データは保存されます。次に、すべての列が削除されたら、この保存データを使用して削除された各列に対してトリガーが起動します。これは、各列が 2 度処理されるため DB2ROW より DB2SQL モードの方が効率が悪いことを示しています。DB2SQL モードは after トリガーでのみサポートされています。DB2SQL を before トリガーで指定すると、自動的に DB2ROW モードに変換されます。

モードにより動作は違いますが、通常 DB2SQL モードを使用しても、DB2ROW モードを使用しても最終的な結果は変わりません。唯一の例外は自己参照トリガーです。DB2ROW トリガーが（トリガー下のテーブルに対してカウントするような）集合操作をする場合は、DB2SQL トリガーを使用した場合と異なる値を返すことがあります。DB2SQL 集合操作は列操作がすべて完了しない限り実行されません。DB2ROW 集合関数は各列を操作した後にテーブルに対して実行されます。

この違いを理解するために、triggering ステートメントが 10 列削除した場合の delete トリガーの例を再度見てみましょう。トリガー・ロジックによりその下のテーブルの列がカウントされると、DB2SQL トリガーはどれを実行しても同じカウント値を返します（合計列は 10）。DB2ROW モード列カウントはトリガーを起動するたびに異なります。1 回目に実行した場合の合計列は 1、2 回目の実行時には 2 というようになります。

トリガー・データへのアクセス

外部トリガーが起動すると、トリガー・データベース要求からのデータが頻繁に使用されます。たとえば insert トリガーはデータ・キューに新しい列のデータを挿入できます。このデータはトリガー・バッファー経由で外部トリガーに使用できます。SQL トリガーは同じデータにアクセスしますが、列遷移変数および遷移表と呼ばれる異なるインターフェースでアクセスします。REFERENCING 節を使用して列遷移変数の相関名（または相関変数）および遷移表のテーブル名を指定します。

相関変数は外部トリガー・バッファーで渡されるレコード・イメージ値と一致します。古い列変数は、トリガー・イベント前に変更した列の値を表すことで古いレコード・イメージにマッピングし、新しい列変数はトリガー・イベント後に変更した列の値を表すことで新しいレコード・イメージにマッピングします。

しかし SQL トリガーにより古い値および新しい値にはるかに簡単にアクセスできるようになりました。トリガーはカラム名を定義した相関名で修飾する必要があります。図 2 の例では、新しい相関変数を使用して新規に挿入されたカラム値を出張旅費監査表にコピーしています。

遷移表変数は一時的な読み取り専用テーブルを表しています。古いテーブル変数にはトリガー・イベント以前のままで、新しいテーブル変数にはトリガー・イベント後のままで変更されたすべての列が入っています。テーブルの 10 列を更新する Update ステートメントでトリガーされた after update トリガーがある場合は、update トリガーでアクセスされる遷移表に 10 列入ります。変更しなければ、遷移表はトリガーの定義時に他のテーブルでも使用できます。

図 3 は update トリガーで遷移表を使用している例で、アカウント変更数を記録しています。遷移表は after トリガーでのみ使用できます。

トリガーのタイプによっては、使用できない相関変数と遷移表もあります。たとえば、insert トリガーは新しい遷移変数にのみアクセスできます。図 4 のテーブルでは各トリガー・タイプに有効な遷移変数のタイプをまとめています。

トリガー・バッファーの外部トリガーに渡されますが、SQL トリガーが使用できない値があります。トリガー・イベント、トリガー時間、コミット・レベル、相対レコード番号、ファイル名、ライブラリー名、メンバー名など SQL トリガーが使用できない外部トリガー・バッファー値のリストを示します。

外部トリガーのように、CREATE TRIGGER ステートメントで生成された C プログラムをデバッグしたい場合は、トリガー定義に SET OPTION DBGVIEW=*STMT を指定して、DB2 UDB にデバッグ可能な生成済み C プログラム・オブジェクトを作成させます。他の SET OPTION バリエーションも使用できますが、SQL リファレンス・ガイドを参照することをお勧めします。

SQL トリガー WHEN 節により、トリガー・ロジックを条件付きで実行できます。海外注文などのみで必要な特殊な処理をする場合、WHEN (n.country<>'USA') などの WHEN 節を使用すれば、オーダー表に挿入するたびにトリガーを起動しなくても、条件付きでトリガーを起動できます。

WHERE 節のように WHEN トリガー条件に副照会を含む、1 つまたは複数の述部を入れることができます。WHEN 節はまた図 2 の例のように遷移変数と遷移表を参照できます。WHEN 節を使用して ADDPFTRG コマンドで外部トリガー TRGUPDCND(*CHANGE) (トリガー更新条件) をシミュレートできます。SQL 列レベル update トリガーにはデフォルト動作の TRGUPDCND(*ALWAYS) があり、*SQL カラムレベル update トリガーは TRGUPDCND(*CHANGE) 動作でインプリメントされます。

この外部トリガー・オプションにより、update トリガーは値が実際に更新されるときだけ起動します。WHEN(n.salary <> o.salary)などの WHEN 節により SQL トリガーは salary カラムが変更されるときだけ起動されます。

データの変更と修正

ALWREPCHG（変更の繰り返し許可）も SQL トリガーがサポートしているかどうか悩む外部トリガー・オプションです。SQL トリガーは常に ALWREPCHG(*YES) 値で作成されます。まず外部トリガーはこのオプションを使用して、新規レコード・イメージのデータを修正し、それらの変更内容をその基本となるデータベースの変更時に使用します。SQL トリガーは異なるメカニズムで同様の機能を提供します。

*YES で指定された外部トリガーとは異なり、SQL トリガーはトリガー列を更新または削除できません（これは復元できないデータ変更と呼ばれています）。before insert または after update SQL トリガーにより、SET ステートメントまたは SELECT INTO ステートメントを使用して新しい相関変数の値を変更できます。そして更新された値は insert 要求または update 要求時に DB2 UDB で使用されます。またストアード・プロシージャー呼び出しの OUT または INOUT パラメーターとして新しい相関変数を渡してそれらを変更できます。図 5 の例では SET ステートメントを使用しており、state 値が大文字で外部ストアード・プロシージャー呼び出しを使用して固有のオーダー番号を割り当てています。

トリガー・ロジック

トリガー自体は 1 つまたは複数の SQL ステートメントで構成されています。一般的にこれはどの SQL ステートメントでもあり得ます。前述した制限事項では、before トリガーは Update または Create といったデータ変更ステートメントを実行できませんでした。この制限を除けば、SQL プロシージャーおよび関数で使用されているステートメントはほとんど SQL トリガーで使用できます。

前述のように Create Trigger ステートメントを実行すると、DB2 UDB はトリガー定義をインプリメントするために C プログラム・オブジェクトを生成します。このプログラム作成中に、トリガーで参照されるすべてのテーブル、ビュー、別名、ユーザー定義タイプ、ユーザー定義関数、プロシージャーが標準にしたがって存在していることを確認する必要があります。

別名が参照するテーブルまたはビューもトリガー作成時に存在している必要があります。C プログラム・オブジェクト生成中、テーブルをドロップするときにトリガー依存性を管理する標準要件を満たそうと次の方法でトリガー自体が変更されます（詳細は後述）。

ネーミング・モードが SQL ネーミングに切り替わる。
すべての非修飾オブジェクト参照が明に修飾される。
すべての暗黙カラム・リスト（カラム・リストがない SELECT * や INSERT、UPDATE SET ROW など）が実カラム名のリストに展開される。

これは外部トリガーの場合とかなり違います。外部トリガーではトリガー実行中に非修飾オブジェクト参照が使用できます。

エラー処理

SQL トリガーの実行中に発生するエラーは SQLSTATE 09000 および SQLCODE -723 を使用して戻されます。また SQL トリガーは SIGNAL ステートメントを使用して自らエラー・タイプを生成できます。その場合、SQLCODE -438 と SIGNAL ステートメントで指定した SQLSTATE が戻されます。この SQL エラー・フィードバックは呼び出しているアプリケーションの SQLCA でアクセスされます。こうしたエラーを受信した場合は、基本となるデータベース操作に障害が発生しています。

例外処理に対してトリガーにハンドラーを使用する場合は、undo ハンドラーは SQL トリガーでは使用できないことに注意してください。図 6 は、SIGNAL ステートメントで特定のカラムに対してサラリーを超える更新を停止させるカラムレベル・トリガーの例です。このビジネス・ルールを破る更新ステートメントは次のメッセージを出して失敗します。

[SQL0723] SQL trigger SALARYCHECK in MYSCHEMA failed with SQLCODE -438 SQLSTATE SA001

トランザクション分離機能とリカバリー

すべてのトリガーは起動時に、分離レベルをトリガーしているアプリケーションと同じ分離レベルに設定します。トリガー内に SET TRANSACTION ステートメントを置くことでこの分離レベルをオーバーライドできます。

アプリケーションとトリガーを異なる分離レベルで実行する場合はよく考えてから行ってください。とりわけアプリケーションがコミットメント制御で実行され、トリガーがそうでない場合、またその逆の状況を避ける必要があります。こうしたミスマッチした状態でコミットメント制御を使用することにより、アプリケーションおよびトリガー両方で行われたデータベースの変更内容をリカバリーすることが難しくなります。アプリケーションとトリガー両方が No Commit 以外の分離レベルで実行している場合は、トリガーしているアプリケーションがコミットまたはロールバックを発行するたびに、トリガー変更がコミットまたはロールバックされます。他のコミットメント制御の組み合わせもサポートされていますが、お勧めできません。

トリガー管理

操作不能トリガーとは DB2 UDB で起動できないトリガーのことです。Insert、Update、Delete 操作は操作不能トリガーに関連付けられたテーブルでは使用できません。次の場合にトリガーは操作不能になります。

自己参照トリガーが付いたテーブルは CRTDUPOBJ CL コマンドで複製されます。テーブルは CRTDUPOBJ で新しいライブラリーに複製され、トリガーは新しいライブラリーにないテーブルを参照します。
テーブルは RSTOBJ コマンドまたは RSTLIB CL コマンドで新しいライブラリーに復元され、テーブルは自己参照しています。

トリガーが操作不能な状態から抜け出す唯一の方法は、トリガーをドロップし再作成することです。

操作不能なトリガーだけでなく、トリガーが参照しているテーブルを削除する場合にも注意が必要です。テーブルをドロップするときに（例：DROP TABLE mytable CASCADE） Cascade オプション（デフォルトではない）を使用する場合は、そのテーブルを参照しているトリガーも削除されます。このため、トリガー定義で参照されているすべてのテーブルがトリガーの作成時に存在していなければならず、すべての非修飾オブジェクト参照が修飾されるわけです。その結果、DB2 UDB はトリガーで参照されるすべてのオブジェクトを判別できます。このトリガー依存情報は SYSTRIGDEP カタログ・ビューにあります。Cascade オプションは Drop Trigger ステートメントではサポートされていません。したがってトリガーだけが削除されます。

すべてのオブジェクト参照はトリガー作成中にライブラリー名で明に修飾されるため、SQL トリガーを開発システムから生産システムに移動するのはやっかいです。同じライブラリーが開発システムと生産システムで使用されていない限り、SQL トリガーを開発システムから生産システムに移動する最善の方法は生産システムでトリガーを再作成することです。この場合、開発システムと生産システムに DB2 SQL Development Kit をインストールしておく必要があります。（これは SQL ストアード・プロシージャーと SQL 関数により異なります。この場合、推奨する方法は単に生成された C プログラム・オブジェクトを移動することです。）

トリガーの成熟期

SQL トリガーと外部トリガー両方で使用できる他の V5R1 拡張機能の使用方法はもちろん、ビジネス・ロジックとビジネス・ルールを SQL トリガーに組み込む方法がおわかりいただけたかと思います。詳細については、「DB2 UDB for iSeries SQL リファレンス・マニュアル」を参照してください。

ケント・ミリガンは PartnerWorld for Developers、iSeries の iSeries 用 DB2 UDB テクノロジー・スペシャリストです。ロチェスターにある IBM DB2 開発部隊に 8 年間勤務していました。