読者です 読者をやめる 読者になる 読者になる

小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


【Oracle】トリガーによるInsert直前のデータバリデーション

はじめに

Oracleデータベースを使ったアプリケーションの開発でデータベースの誤更新やデータ破壊を防ぐ方法について考えてみました。

不良データを入れさせない

データベースの誤更新やデータ破壊を防ぐには、不良データを入れさせなければ良いのです。そのためには、データバリデーションをしっかり行うことで防ぐことができます。

Oracleの現場を効率化する100の技
鈴木 健吾 玉置 雄大 塩原 浩太 小林 修 大森 慎司 内村 友亮
技術評論社
売り上げランキング: 26,114

テーブル設計において、フラグ値やコード値などはデータ型と範囲の決まったデータが使われます。このようなデータは、比較的チェックがしやすいのでデータバリデーションの仕組みを考えることができます。
逆に、日本語の氏名や住所は規則性がなく、明らかな誤りを判断するのが困難で、データバリデーションの対象として不向きです。

どこで食い止めるか

アプリケーション層↔データアクセス層↔RDBMSという構造になっている場合、データバリデーションをどこで実施するのが良いでしょうか。
今回は、アプリケーションやデータアクセス層ではなく、最後の砦であるRDBMSで行うデータバリエーションについて考えてみます。

check制約を用いたバリデーション

カラムに対してcheck制約を付加することで、指定した値以外のデータの挿入をブロックすることができます。
整合性制約 - オラクル・Oracleをマスターするための基本と仕組み はてなブックマーク - 整合性制約 - オラクル・Oracleをマスターするための基本と仕組み

テーブルのレコードに条件を設定し、条件に一致しているものを許可する。
 例) IS_VALIDATE に NULL(=UNKNOWN) , 0(=FALSE), 1(=TRUE)を許可する。
CREATE TABLE (
  IS_VALIDATE NUMBER(1)
  CONSTRAINTS constraint_name CHECK (IS_VALIDATE IN ('1', '0'))
) ;

ただし、check制約にはユーザー定義ファンクションのコールができない
制限があり、複雑なバリデーションチェックを行うことはできません。

check制約の制限事項

◦副問合せおよびスカラー副問合せ式
◦決定的でないファンクションへのコール(CURRENT_DATE、CURRENT_TIMESTAMP、DBTIMEZONE、LOCALTIMESTAMP、SESSIONTIMEZONE、SYSDATE、SYSTIMESTAMP、UID、USERおよびUSERENV)
◦ユーザー定義ファンクションへのコール
◦REF列の参照解除(DEREFファンクションを使用する場合など)
◦ネストした表の列または属性
◦疑似列CURRVAL、NEXTVAL、LEVELまたはROWNUM
◦完全に指定されていない日付定数
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/clauses.html#5339

トリガーを用いたバリデーション

Oracleにはトリガーという機能があり、InsertやUpdate文の直前(Before)、直後(after)に任意の処理を行わせることができます。
Beforeトリガーでデータのバリデーションを行い、不良データであれば、エラーを発行し、insertを取り消せないかと考えました。

たとえば性別が男以外のレコード挿入をブロックするトリガー。
検出するとアプリケーション例外を発生させます。

CREATE OR REPLACE TRIGGER TRIGGER01 BEFORE
  INSERT ON TABLE01 FOR EACH ROW BEGIN IF (INSERTING) THEN IF (:NEW.SEIBETSU != '男') THEN
    RAISE_APPLICATION_ERROR(-20000, 'トリガが誤入力データを検出しました');
END IF;
END IF;
END;
/

f:id:replication:20150516112844p:plain

おわりに

check制約を使ったバリデーションもトリガーを使ったバリデーションもRDBMSの機能を用いたものです。
アプリケーション修正を行わずにバリデーション機能を追加できる反面、トリガーに複雑なバリデーション機能を実装するのはオススメしません。トリガー自体が表にでない黒子的な役割のため、ドキュメントにしっかり書いておかないと他の人が気づかなさそうです。