目次


DB2 UDB でトリガーにSQL プロシージャー言語を使用

Comments

まず第一に、トリガーとは表に関連付けられたオブジェクトであり、INSERT、UPDATE、あるいは DELETE によって自動的に発生するアクションを定義します。たとえば次のような場合に、トリガーを使うと便利です。

  1. 挿入時、挿入操作が実際に発生する前に、データの妥当性を検証して操作する。
  2. 更新時、新しい値を既存の値と比較して、その正確性を検査する。状態情報を保持する列が表にあり、有効な状態遷移を定義したい場合には、特に便利です。
  3. 削除時、監査記録目的でロギング情報を別の表へ自動的に挿入する。

トリガーは、データベース・レベルでビジネス・ルールの徹底を一元化するために使用でき、すべてのアプリケーションおよびユーザーは自らデータの妥当性を調べる作業から解放されます。また、ビジネス・ルールに変更があってもデータベース・レベルで一元化されるので、該当するアプリケーションのすべてに伝播する必要はありません。

サンプル

次のサンプルでは、仮想の部品会社において注文データや顧客情報を保持するデータベース用に表やトリガーを作成します。次のビジネス・ルールが徹底されます。

  1. 部品会社が注文を受け取ると、注文の合計額とその顧客の未払い合計額は、その顧客に提供された信用限度額を超えることはできない。
  2. 注文はいくつかの状態を持つことができる (PENDING、CANCELLED、SHIPPED、DELIVERED、COMPLETED)。次の状態遷移だけが有効である。
     
    PENDING -> SHIPPED -> DELIVERED -> COMPLETED
    PENDING -> CANCELLED
  3. 注文の削除は、キャンセルされた場合のみ許される。また、監査目的で、削除された注文は別の表にロギングされる

サンプルでは、次のような表を定義して表します。このサンプルに必要なすべてのデータベース・オブジェクトを作成するスクリプトをダウンロードするには、ここをクリックします。(スクリプトの実行方法については、ファイルを開いてください。)

 
	create table customer_t (
	cust_id INT NOT NULL PRIMARY KEY,
	company_name VARCHAR(100),
	credit DECIMAL(10,2))

create table product_t (
	product_id INT NOT NULL PRIMARY KEY,
	product_name VARCHAR(100))

create table orders_t (
	order_id INT NOT NULL PRIMARY KEY,
	cust_id INT NOT NULL,
	product_id INT NOT NULL,
	quantity INT NOT NULL,
	price DECIMAL(10,2) NOT NULL,
	status CHAR(9) NOT NULL,
  FOREIGN KEY (cust_id) REFERENCES customer_t,
  FOREIGN KEY (product_id) REFERENCES product_t)

create table delete_log_t (
	  Text varchar(1000))

そして、ユニーク ID を生成するシーケンス・オブジェクト (これも DB2 UDB 7.2 の新機能) を次のように定義します。

 
create sequence cust_seq
create sequence prod_seq
create sequence ord_seq

注: シーケンス・オブジェクトの詳細については、DB2 7.2/7.1 FixPak 3 のリリース情報を参照してください。

次に、次のようなデータを挿入します。

 
insert into customer_t values
(NEXTVAL FOR cust_seq, 'Nancys Widgets', 100)
insert into product_t values
(NEXTVAL FOR prod_seq, 'Blue Widgets')

最後に、上記で定義されたビジネス・ロジックを徹底するトリガーを作成し、そのトリガーを説明します。

挿入トリガー

最初に作成するトリガーは、ビジネス・ルール #1 を徹底します。

「部品会社が注文を受け取ると、注文の合計額とその顧客の未払い合計額は、その顧客に提供された信用限度額を超えることはできない。」

 
1 : CREATE TRIGGER verify_credit
2 : NO CASCADE BEFORE INSERT ON orders_t
3 : REFERENCING NEW AS n
4 : FOR EACH ROW MODE DB2SQL
5 : BEGIN ATOMIC
6 :   DECLARE current_due DECIMAL(10,2) DEFAULT 0;
7 :   DECLARE credit_line DECIMAL(10,2);
9 :   /*
	  * 顧客の信用限度額を取得する
	  */
10:   SET credit_line = (SELECT credit
		  FROM customer_t c
		  WHERE c.cust_id=n.cust_id);
11:     -- 現在の未払い請求額を合計する
12:   FOR ord_cursor AS
13:     SELECT quantity, price
		  FROM orders_t ord
		  WHERE ord.cust_id=n.cust_id AND
			status not IN ('COMPLETED','CANCELLED') DO
14:     SET current_due = current_due +
			(ord_cursor.price * ord_cursor.quantity);
15:   END FOR;
16:   IF (current_due + n.price * n.quantity) > credit_line THEN
17:     SIGNAL SQLSTATE '80000' ('Order Exceeds credit line');
18:   END IF;
19: END

1 行目の CREATE TRIGGER ステートメントは、veryfy_credit という名前でトリガー・オブジェクトを作成することを意味します。

NO CASCADE BEFORE INSERT ON orders_t とは、トリガー・アクションは表にデータが実際に挿入される前に発生し、トリガーのアクションによって他のトリガーがアクティブにされることはないことを意味します。すべての BEFORE トリガーには、NO CASCADE キーワードが必要です。

REFERENCING NEW AS n は、挿入される新規データの列を参照する際に必要な修飾子として n を識別します。

FOR EACH ROW は、行が挿入されるたびにこのトリガーがアクティブにされることを意味します。他のオプションは FOR EACH STATEMENT (AFTER トリガー用のみ) で、SQL ステートメントごとにアクティブにされます。言い換えると、INSERT ステートメントが別の表から 10 行を選択することによって値を挿入する場合、FOR EACH ROW を使用するとトリガーは 10 回アクティブにされ、FOR EACH STATEMENTを使用すると 1 回だけ実行されます。MODE DB2SQL は指定すべき文節にすぎません。

5 行目の BEGIN ATOMIC と 19 行目の END は、トリガーの本体を定義します。BEGIN ATOMIC なので、トリガーの中で定義されたアクションは、全部か皆無かのいずれかのアクションです。このトリガーの実行中にエラーが発生すると、すべてのアクションはロールバックされて、データ保全性が維持されます。

6 行目と 7 行目の DECLARE <variablename> <type> [DEFAULT <value>] は、ビジネス・ルールの処理にトリガーが使う必要のあるローカル変数を定義します。

9 行目と 11 行目は、DB2 SQL プロシージャー言語で許されるコメント様式を 2 つ示しています。複数行のコメントには /* および */ を、1 行のコメントには「- -」を使用できます。

10 行目で、顧客表から顧客の信用限度額を SELECT します。この照会の述部は WHERE ord.cust_id=n.cust_id で、返されたとしても 1 行だけであることを保障します (2 行以上返される場合は SQL エラーになります)。述部の「n.cust_id」部分は、このトリガーをアクティブにした INSERT ステートメントによって供給された列の値を指します。

次に 12 行目で、注文に対するすべての未払い記録を選択し、ord_cursor という読み取り専用カーソルを定義します。カーソルは、ステータスが COMPLETED (支払受領済み) や CANCELLED (注文は出荷されていない) 以外の各注文の値段と数量を選択します。結果として抽出されるデータの各行を使用して、未払い注文の合計額を計算して、現時点の未払い合計額を算出します。

最後に、16 行目で、トリガーは現在の未払い額と新規注文の合計額を、顧客の信用限度額と比較します。信用限度額が低ければ、アプリケーション・エラーとなり、アプリケーションは (SIGNAL ステートメントを用いた) カスタム・ステータス SQLSTATE 80000 と (アプリケーションが取り出すことのできる) エラー・メッセージ「Order Exceeds credit line (注文が信用限度額を超えた)」が発行されます。挿入は却下され、変更内容はすべてロールバックされます。エラーによって SQLException が発生します。これは呼び出し側のアプリケーションで処理できます。

注: エラー・メッセージは現在は 70 文字に制限されています。メッセージがこの上限を超えると、警告なく切り捨てられます。

上の例では、1 つの挿入ごとに 1 つだけ注文が作成されると想定しています。アプリケーションが単一の INSERT ステートメント内で複数行を挿入したとすると、このトリガーを AFTER トリガーにする必要があります。それは、イベントは次のような順序で発生するためです。

  1. ユーザーまたはアプリケーションが INSERT ステートメントを発行する。
  2. データが実際に挿入される前に、INSERT トリガーがアクティブになり、実行して完了する。
  3. トリガーがエラーもなく正常終了すると、行が挿入される。

BEFORE トリガーはデータが挿入される前に実行を終えるので、単一の INSERT ステートメントが複数行を挿入する場合、ユーザーやアプリケーションが挿入を試みる行をFOR ループで認識されることはありません。

このトリガーの実行をスピードアップするために、いくつか最適化を実施できます。トリガーは、主にいくつかの新しいトリガー機能の使い方を示すためにコーディングされたのであって、パフォーマンス改善のためではありません。詳細は後述の「パフォーマンスのヒント」を参照してください。

更新トリガー

更新トリガーは挿入トリガーと非常によく似ていますが、新規および既存の値への参照をアクセスできることが違います。前述のビジネス・ルールから有効な状態遷移を定義して、これを全アプリケーションで徹底するためにトリガーを使用します。

有効な状態遷移:
PENDING -> SHIPPED -> DELIVERED -> COMPLETED
PENDING -> CANCELLED

状態遷移の徹底には、次のようなトリガーを使えます。

 
1 : CREATE TRIGGER verify_state
2 : NO CASCADE BEFORE UPDATE ON orders_t
3 : REFERENCING OLD AS o NEW AS n
4 : FOR EACH ROW MODE DB2SQL
5 : BEGIN ATOMIC
6 :   IF o.status='PENDING' and n.status IN ('SHIPPED','CANCELLED') THEN
7 :     -- valid state
8 :   ELSEIF o.status='SHIPPED' and
9 :       n.status ='DELIVERED') THEN
10:     -- valid state
11:   ELSEIF o.status='DELIVERED' and
12:       n.status = 'COMPLETED' THEN
13:     -- valid state
14:   ELSE
15:     SIGNAL SQLSTATE '80001' ('Invalid State Transition');
16:   END IF;
17: END

この場合、トリガーを called verify_state と呼びます。これはorders_t 表に対する一切の更新よりも前にアクティブにするトリガーです。もう 1 つの違いは、「o」で修飾して既存の (古い) 値を参照し、「n」を使用して新規値を参照することです。

5 行目から 16行目で状態遷移を検証する方法は、単刀直入です。遷移が予期されないものであれば、エラーとみなして、メッセージ「Invalid State Transition (無効な状態遷移)」を付けてアプリケーション・エラーを発生させ、操作は却下されます。もちろん、ロジックは次のようにも記述できます。

 
IF NOT((o.status='PENDING' and n.status IN ('SHIPPED','CANCELLED')) OR
(o.status='SHIPPED' and n.status = 'DELIVERED' OR
(o.status='DELIVERED' and n.status = 'COMPLETED')) THEN
SIGNAL SQLSTATE '80001' ('Invalid State Transition')
END IF;

....しかし、わかりやすくするため、また IF/THEN/ELSE 構成体の全構文を示すために拡張しました。

削除トリガー

最後のビジネス・ルールについて、DB2 UDB 7.2 より以前からすでに提供されていた簡単なトリガー様式を、次の 2 つの部分に分けて説明します。

3a)「注文の削除は、キャンセルされた場合のみ許される」
 3b)「監査目的で、削除された注文は別の表にロギングされる」

まず、3a を徹底するトリガーです。

 
1 : CREATE TRIGGER restrict_delete
2 : NO CASCADE BEFORE DELETE ON orders_t
3 : REFERENCING OLD AS o
4 : FOR EACH ROW MODE DB2SQL
5 : WHEN (o.status <> 'CANCELLED')
6 :   SIGNAL SQLSTATE '80003' ('Cannot Delete an order that has not been cancelled')

AFTER トリガー

ルール 3b については、AFTER トリガーを使用して、orders_t 表からのすべての削除操作をログに記録します。

 
1 : CREATE TRIGGER log_delete
2 : AFTER DELETE ON orders_t
3 : REFERENCING OLD AS o
4 : FOR EACH ROW MODE DB2SQL
5 :   INSERT INTO delete_log_t VALUES (
		'Order #' || CHAR (o.order_id) ||
		'Was deleted on ' || CHAR(CURRENT TIMESTAMP));

削除トリガーと上記 2 つのトリガーとの間の主な違いは、BEGIN ATOMIC および END がないことです。BEGIN ATOMIC および END はトリガーで単一の SQL ステートメントだけを実行したい場合には不要です。上記のトリガーは、明らかに監査用にそれほど有用な情報をログ記録しませんが、1 つの表での INSERT がトリガーを使用して別の表への INSERT を引き起こす様子を説明しています。削除が発生するたび、および WHEN 文節で定義された条件が true の場合に、トリガーがアクティブにされます。(上記の AFTER トリガーのように) WHEN 文節を完全に省略すると、トリガーは常にアクティブにされます。

ルールのテスト

ビジネス・ルール 1 をテストするには、Nancy の部品注文を 2 つ挿入します。この顧客の信用限度額は$100 にすぎないので、最初の注文は OK ですが、2 番目の注文では額を超えて失敗してしまいます。(注文額はどちらも $90)。

 
Insert into orders_t values (nextval for ord_seq, 1, 1, 9, 10.0, 'PENDING')
Insert into orders_t values (nextval for ord_seq, 1, 1, 9, 10.0, 'PENDING')

ビジネス・ルール 2 は、前のアクションの上に作成することによってテストできます。(上記) 注文の状態遷移が有効であると仮定すると、「PENDING」の注文は「SHIPPED」になります。いったん注文部品が出荷されると、キャンセルはできません。次の最初のUpdate は成功しますが 2 番目は失敗します。

 
Update orders_t set status='SHIPPED' where order_id=1
Update orders_t set status='CANCELLED' where order_id=1

ビジネス・ルール 3 をテストするには、今処理したばかりの注文の削除を単純に試みてみます。次の delete ステートメントは、ステータスが「CANCELLED」ではないので失敗します。BEFOREトリガーが失敗したので、(削除アクションをログに記録する) AFTER DELETE トリガーがアクティブにされることはありません。

 
Delete from orders_t where order_id=1

削除のロギングをテストするには、「Nancy の部品」の信用限度額内の注文を追加し、注文をキャンセルして、それを削除します。

 
Insert into orders_t values (nextval for ord_seq, 1, 1, 1, 10.0, 'PENDING')
Update orders_t set status='CANCELLED' where order_id=(prevval for ord_seq)
Delete from orders_t where order_id=(prevval for ord_seq)
Select * from delete_log_t

パフォーマンスのヒント

  • BEFORE トリガーは、ユーザーが指定した値の変更、あるいは主キーなどの新規値の生成に使用すべきです。AFTER トリガーで遷移表からの行を変更しようとすると、ますます複雑です。
  • DB2 は強力なリレーショナル・エンジンです。しかし、現時点では、他の SQL ステートメントのようにはプロシージャー・ロジック(制御)ステートメントを最適化しません。

    たとえば、verify_credit trigger は次のように書き換えが可能です。

     
      1 : CREATE TRIGGER verify_credit
      2 : NO CASCADE BEFORE INSERT ON orders_t
      3 : REFERENCING NEW AS n
      4 : FOR EACH STATEMENT MODE DB2SQL
      5 : WHEN ((SELECT SUM(price * quantity) FROM orders_t
      6 :       WHERE cust_id = n.cust_id
      7 :         AND status NOT IN ('COMPLETED', 'CANCELLED'))
      8 :      + n.price * n.quality
      9 :     > (SELECT credit FROM customer_t WHERE cust_id=n.cust_id))
      10: SIGNAL SQLSTATE '80000' ('Order Exceeds credit line')
  • 更新については、新しい値を古い値と比較する必要のある場合、FOR EACH STATEMENT トリガーの代わりに FOR EACH ROW トリガーを使用します。FOR EACH ROW トリガーを使用した方がパフォーマンスに優れます。それは、トリガー操作を完了するために遷移表に保管された複数の新旧の値を扱わなくても、比較に必要な値があるためです。

その他のヒント

  • WHILE ループがサポートされます。構文は『SQL 解説書』に記述されています。
  • GET DIAGNOSTICS = ROW_COUNT は、トリガーの本体の中で呼び出された最新の update、delete、あるいはinsert ステートメントで、何行が影響されたかの判断に使用できます。
  • SELECT .... INTO 構文はサポートされません。複数列から複数の変数を選択するには、SET (x,y) = (SELECT x_col, y_col FROM mytable) を使用します。
  • 再帰的なトリガーは避けます。再帰トリガーは、本体に同じ種類のステートメントを持つことによって、それ自体をアクティブにするトリガーです。たとえば、mytable 表に DELETE トリガーを定義して、トリガーの本体にも mytable 表での DELETE ステートメントを含んでいるとすると、それは再帰トリガーであり、慎重にコーディングしないと問題につながることがあります。再帰を使う必要のある場合、1 つの繰り返しに限定してください。
  • 1 つの表に複数のトリガーが定義される場合 (つまり、2 つの BEFORE INSERT トリガーが定義される場合)、作成された順番に実行されます。もちろん、BEFORE トリガーは、作成順序には関係なく常に AFTER トリガーより前にアクティブにされます。また、表にその他の制約 (主キー/外部キー制約、ユニーク制約、および検査制約など) が存在する場合は、制約は BEFORE トリガーの後、AFTER トリガーの前に検査されます。

まとめ

データベースを使用するすべてのアプリケーションに一元的なビジネス・ルールを徹底するには、トリガーは便利です。DB2 V7.2 または DB2 V7.1 + FikPak 3 で提供された新機能を使えば、トリガーはこれまでになく複雑なビジネス・ルールをカプセル化できます。新機能により、コードの構造が読みやすくなるため、コード保守が簡単になります。

謝辞

この記事のテクニカル・レビューをしていただいた Serge Rielau、Richard Swagerman、Drew Bradstock の各氏に対し、謝意を表します。


ダウンロード可能なリソース


関連トピック


コメント

コメントを登録するにはサインインあるいは登録してください。

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=60
Zone=Information Management
ArticleID=326361
ArticleTitle=DB2 UDB でトリガーにSQL プロシージャー言語を使用
publish-date=10242001