シーケンス とトリガーを使った自動採番機能(オートナンバー)

Access などにあるオートナンバ機能を トリガーSEQUENCE で実現することができる。しかし、この方法はあまり勧められない方法ではある。 監査や作業ログのようなエンドユーザーやアプリケーションのプログラマからは一見、設定する意味や存在価値がないと 思われるようなカラムに対しては設置することで重宝がられるだろう。

あまりお勧めはしない理由は大きく2つ

パフォーマンス

SQL に非常に単純な1カラム増やすことと割が合わないパフォーマンス。 トリガを発生させることで、本来は SQL エンジンだけの動作で済むものが、 SQL エンジン ⇒ PL/SQL エンジン ⇒ トリガーの実行 (DUAL 表へのアクセス(※)) となる。これは発生頻度、件数との相談。
また、このトリガー は ダイレクト・パス・インサート とは共存できない。これは一部のアプリケーションによっては譲歩し難い大きなデメリットになる。

(※) Oracle 11g では PL/SQL から直接シーケンスオブジェクトにアクセスできる。

データ整合性

トリガーの設置はインポートとの相性も考慮しておく必要がある。既存環境に IGNORE=Y のモードで インポート を行うとトリガーが起動する。このときオートナンバ列にリレーションを設置している場合には細心の注意を払う必要がある。

あるテーブルの全てのトリガーを一時的に無効化したい場合には

ALTER TABLE table_name {ENABLE|DISABLE} ALL TRIGGERS ;

のようにトリガーを無効化しておくと起動しなくなる。

このように使用するテーブルやカラム対象と件数を事前に考慮して設置する必要があり、多用は運用時になって困ることになる。

シーケンスとトリガーによる実装方法

AUTONUMBER_TEST テーブルに SEQUENCE を使いオートナンバー列 SEQ_NO をエミュレートする

-- 作成 DDL
-- テストテーブル
CREATE TABLE RIVUS.AUTONUMBER_TEST (
	SEQ_NO		NUMBER	PRIMARY KEY,	-- オートナンバー列
	USR_REM		VARCHAR2(10)
)
/
-- 発番用シーケンス
CREATE SEQUENCE RIVUS.AUTONUMBER_SEQ
/
-- 発番用トリガー
CREATE OR REPLACE TRIGGER RIVUS.AUTONUMBER_NUMBERING
BEFORE INSERT ON AUTONUMBER_TEST
FOR EACH ROW
DECLARE
	eIKENAIYO		EXCEPTION;
	-- (オプション) 例外の付け替え DUP_VAL_ON_INDEX
	PRAGMA EXCEPTION_INIT(eIKENAIYO, -1); 
BEGIN
	IF (:NEW.SEQ_NO IS NOT NULL) THEN
		RAISE eIKENAIYO;
	END IF;
	SELECT AUTONUMBER_SEQ.NEXTVAL INTO :NEW.SEQ_NO FROM DUAL ;
	-- Oracle 11g
	-- NEW.SEQ_NO := AUTONUMBER_SEQ.NEXTVAL ;
END;
/
  • 発番された番号を取得する

発番されたSEQ_NO を RETURNING で INSERT 時に取得する (PL/SQL)

DECLARE
   vCurrNo  NUMBER;
BEGIN
	INSERT INTO AUTONUMBER_TEST (USR_REM) VALUES ('あいう')
	RETURNING SEQ_NO INTO vCurrNo;
--	イケナイ例
--	INSERT INTO AUTONUMBER_TEST (SEQ_NO, USR_REM) VALUES ( 1, 'あいう')
--	RETURNING SEQ_NO INTO vCurrNo;
--
	COMMIT;
	DBMS_OUTPUT.PUT_LINE('採番された番号は ' || vCurrNo);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
	DBMS_OUTPUT.PUT_LINE('それはイケナイ');
	ROLLBACK;
END;
/
...
採番された番号は 1


 
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ 会員制(無料)の公式技術サイト