2011年9月2日金曜日

[APEX]オートナンバー機能を実現するにはシーケンスを作成してからトリガを作成すること



  • 概要
APEX Oracle11gシーケンスオブジェクトを作成してからトリガを作成すること
APEXでテーブル定義をエクスポートしたが、中途半端にしかエクスポートされてないんだなとはまった話

  • 経緯
今、コメントを登録するためのアプリケーションをAPEXで作っています。

WEBインターフェースから必要な項目を入力するとOracleのテーブルにインサートされます。
対象のテーブル「COMMENT」テーブルにはIDとして自動で連番を振るようにしています。
つまり、オートナンバー機能を持たせています。

開発用(10g)のAPEX(3.x)から運用用(11gXE)のAPEX(4.x)にテーブル定義をエクスポートしてみたが、
トリガはエクスポートされるものの、シーケンスオブジェクトはエクスポートされねえんだな。

ずいぶんはまった。

シーケンスオブジェクトがないのにトリガを作成しようとすると
以下のエラーになります。

Compilation failed, line 3 (18:35:12) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PL/SQL: ORA-02289: sequence does not existCompilation failed, line 3 (18:35:12) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PL/SQL: SQL Statement ignored


  • 対応策
シーケンスオブジェクトを作成する

create sequence "<シーケンス名>"
start with <開始番号>
increment by <カウントアップ数>
maxvalue <最大数>
minvalue <最小数>
nocache <事前にメモリにキャッシュしておくか>
nocycle <繰り返し有無>
noorder <設定どおりの順序でナンバリングするか>
/


create sequence "TEST_SEQ"
start with 1
increment by 1
maxvalue 100
minvalue 1
nocache
nocycle
noorder
/


トリガを作成する

create or replace TRIGGER  "<トリガ名>"
BEFORE INSERT ON "<トリガを作成するテーブル名>"

FOR EACH ROW

BEGIN
 IF :NEW.<列名> IS NULL THEN
   SELECT <シーケンスオブジェクト名>.NEXTVAL INTO :NEW.<列名> FROM DUAL;
 END IF;
END;


create or replace TRIGGER  "TEST_ID"
BEFORE INSERT ON "TEST_TABLE"

FOR EACH ROW

BEGIN
 IF :
NEW.ID IS NULL THEN
   SELECT TEST_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
 END IF;
END;


11gから直接シーケンスオブジェクトにアクセス(値を挿入)できるようだが
うまくいかなかったのであきらめることにした。

動かなかった
原因はおいおい考えるとして…間違っているSQLをメモ。

NEW."ID" := TEST_SEQ.NEXTVAL



参考にしました。ありがとうございました
http://www.shift-the-oracle.com/trigger/autonumber-field.html