SQL トリガーの作成

ここではどのようにして SQL トリガーを作成するかを、簡単に説明していきたいと思います。

以下を読んでいただく上での注意なのですが、SQLの世界ではだいたいユーザーの名前がテーブルの名前のクオリファイアとして使用され、それは iSeriesの世界では「ライブラリー」が指定される位置になっています。
ですので必ずライブラリーと同じ名前のユーザーを作成して、そのユーザーでログインして作業してください。「ライブラリー」=「スキーマ」=「ユーザー」という意識を持って作業するようにしてください。
以下の一連の作業も QEOL というユーザーを作成し、そのユーザープロフィールでログインして行っています。


オペレーション・ナビゲーターの「データベース」の中の「ライブラリー」で、トリガーを作成したいテーブルが入ったライブラリーを選択します。

トリガーを関係づけたいテーブルを選んで右クリックします。
出てきたサブメニューで「プロパティ」を選択します。

テーブルのプロパティが表示されます。

下のタブで「トリガー」を選んでください。
表示された中に「SQL トリガーの追加」ボタンがあります。これを押します。

SQL トリガーを追加する画面になります。

と、ここまでがトリガーの作成画面までの道のりです。

上記の画面でいろいろ選択しながら定義ができます。
別の記事と重複しますが、トリガーの作成の一例を見ていってみましょう。


まず最初の画面ではトリガーの名前とどのイベントで起動されるかを指定します。

このテーブルに対するどのデータベースイベントで起動されるか、をラジオボタンで選択します。いっぺんに複数選択することはできません。(SQL でのきまりです)

あるカラムの更新の場合のみに発火させることもできます。これは無駄にトリガーを起動させないため、パフォーマンス/キャパシティ上有効です。

もともとのSQL文でトリガーを定義する時の構文を見ていただければわかると思いますが、どのイベントを選ぶかによってできること/やりたいことのオプションが変わってきます。

次の「タイミング」タブでは、トリガーが起動されるタイミングとその他のオプションを指定できます。

「トリガーが起動されるタイミング」とは、まず前のタブで指定した動作が行われる前か後か(Before/After)、また各行の更新の度に起動されるかそれともSQL文が実行される毎か(For Each Row/For Each Statement)、の指定です。

Before トリガーであれば、実際に行が更新される前に起動されますので、値のチェックや挿入/更新された行のデータに基づいて計算結果をセットするようなカラムにその計算結果をセットする (SET ステートメントを使用します) といったことを行います。
テーブルの更新は許されません。INSERT/UPDATE/DELETE ステートメントは使えません。(これもSQLでのきまり)

After トリガーの場合、更新や削除によって変わったデータの変更前と変更後の値をみて処理を行う、ということが可能です。

行の場合、「旧行」「新規行」をそれぞれ old、 new といったように名づけ new.column1 = old.column1 などといったようにその行のカラムをアクセスすることが可能です。

また、更新結果を表として扱うこともでき、その場合は「旧テーブル」「新規テーブル」のところに oldtable、 newtable といったように指定します。

下の例では、更新された行の集まりを newtable としてアクセスするように指定しています。

また、「モード」というものがありますが、これは実際にトリガーの中のSQL文が実行されるタイミングを指定します。

実際には For Each Statement の場合は DB2SQLしか指定できないので、この違いが効いてくるのは For Each Row の時だけです。
その場合、DB2ROW の場合は各行毎に実際に実行され、DB2SQLの場合は各行毎に計算は行われるが実際に実行されるのは一番最後というかたちになります。DB2SQL は通常の UDBの場合に実行されるやり方ですが、潜在的に各行は2回アクセスされることになりあまり効率はよくないかもしれません。

こまかくはマニュアルからの抜粋も参照してみてください。

SQL文をここで記述します。

右側の「構文の検査」ボタンでシンタックスチェックを行えますので、かならず実行するようにしてください。

上記の画面で「OK」を押すと以下の画面になります。
この画面で「OK」を押すと実際のコンパイルが行われます。

SQLトリガーの作成についてのいろいろはこの(←)リンクを参照してください。

SQLで作成する場合の構文や注意点はこちらをご参照ください。


付録:マニュアルからの抜粋

REFERENCING
変換変数 の相関名と変 換表 の表名を指定します。相関名 は、トリガー SQL 操作の影響を受ける行セット内の特定行を識別します。表識別コード は、影響を受ける行セット全体 を識別します。次のように相関名 を指定して列を修飾することに より、トリガー SQL 操作の影響を受ける各行が、トリガー・アクションに対 して使用可能になります。
OLD ROW AS 相関名
トリガー SQL 操作の前の行の値を識別する相関名を指定します。
NEW ROW AS 相関名
トリガー SQL 操作とすでに実行された BEFORE トリガー内の SET ステートメントによって変更された行の値を識別する相関名を指定 します。

次のように一時表名を指定することにより、トリガー SQL 操作によって影響を受ける全セット全体が、トリガー・アクショ ン に対して使用可能になります。

OLD TABLE AS 表識別コード
トリガー SQL 操作の前の、影響を受ける行セット全体の値を識別する一時表の名前を指定します。現行トリガーが、あるトリガーの SQL トリガー本体 内のステートメントによって起 動された場合、OLD TABLE には、そのトリガーによって影響を受けた行も含ま れます。
NEW TABLE AS 表識別コード
トリガー SQL 操作とすでに実行された BEFORE トリガー内の SET ステートメントによって変更された、影響を受ける行セット全体の状態を識別する一時表の名前を指定します。

トリガー定義には、最大で、2 つの相関名 (OLD ROW および NEW ROW) と 2 つの表名 (OLD TABLE および NEW TABLE) を含めることができます。名前はすべて相互に固有でなければなりません。

OLD ROW 相関名 および OLD TABLE 表識別コード は、トリガー事象が DELETE 操作または UPDATE 操作の場合にのみ有効です。DELETE 操作の場合、OLD ROW 相関名 は、削除された行内の列の値を取り込み、OLD TABLE 表識 別コード は、削除れた行セット内の値を取り込みます。UPDATE 操作の場合、OLD ROW 相関名 は、UPDATE 操作前の行の列の値を取り込み、OLD TABLE 表識別コード は、更新れたセット内の値を取り込みます。

NEW ROW 相関名 および NEW TABLE 表識別コード は、トリガー事象が INSERT 操作または UPDATE 操作の場合にのみ有効です。どちらの操作の場合も、NEW ROW 相関名 は、挿入または更新された行内の列の値を取り込み、NEW TABLE 表識別コード は、挿入または更新れた行セット内の値を取り込みます。BEFORE トリガーの場合、更新された行の値には、BEFORE トリガーのトリガー・アクション 内の SET ステートメントからの変更が含まれます。

OLD TABLE と NEW TABLE は、BEFORE トリガーまたは MODE DB2ROW の場合は指定できません。

OLD ROW と NEW ROW は、FOR EACH STATEMENT トリガーの場合は指定できま せん。

OLD ROW および NEW ROW 相関名 変数は、AFTER トリガー内では変更できません。

下表は、相関変数と変換表の可能な組み合わせを要約しています。

細分性: FOR EACH ROW

モード 起動時 トリガー操作 許される相関変数 許される変換表
DB2ROW BEFORE DELETE OLD NONE
INSERT NEW
UPDATE OLD, NEW
AFTER DELETE OLD
INSERT NEW
UPDATE OLD, NEW
DB2SQL BEFORE DELETE OLD
INSERT NEW
UPDATE OLD, NEW
AFTER DELETE OLD OLD TABLE
INSERT NEW NEW TABLE
UPDATE OLD, NEW OLD TABLE, NEW TABLE

細分性: FOR EACH STATEMENT

モード 起動時 トリガー操作 許される相関変数 許される変換表
DB2SQL AFTER DELETE NONE OLD TABLE
INSERT NEW TABLE
UPDATE OLD TABLE, NEW TABLE

文字データ・タイプの変換変数は、対象表の列の CCSID を継承します。トリガー・アクション の実行時に、変換変数はホスト変数のように扱われます。したがって、文字変換が行われる可能性があります。

一時変換表は、読み取り専用です。これは変更できません。

相関名 と各表 識別コード の効力範囲は、そのトリガー定義全体です。

FOR EACH ROW
データベース・マネージャーは、トリガー操作が変更する対象表の各行ごとに トリガー・アクション を実行することを指定します。そのトリガー操作がどの行も変更しない場合には、トリガー・アクション は実行されません。
FOR EACH STATEMENT
データベース・マネージャーは、トリガー操作につき一度だけ、トリガー・アクション を実行することを指 定します。UPDATE または DELETE ステートメントのトリガーによって、どの行も影響を受けない場合でも、UPDATE または DELETE FOR EACH STATEMENT トリガーが起動されます。

FOR EACH STATEMENT は、BEFORE トリガーに対しては指定できません。

FOR EACH STATEMENT は、MODE DB2ROW トリガーに対しては指定できません。

MODE DB2SQL
MODE DB2SQL トリガーは、すべての行操作が完了した後で起動されます。
MODE DB2ROW
MODE DB2ROW トリガーは、各行の操作時に起動されます。

MODE DB2ROW は、BEFORE と AFTER 起動時の両方に有効です。


[Top Pageに戻る]

Ads by TOK2