シーケンス とトリガーを使った自動採番機能（オートナンバー）
Access などにあるオートナンバ機能を トリガー と SEQUENCE で実現することができる。しかし、この方法はあまり勧められない方法ではある。
監査や作業ログのようなエンドユーザーやアプリケーションのプログラマからは一見、設定する意味や存在価値がないと
思われるようなカラムに対しては設置することで重宝がられるだろう。
あまりお勧めはしない理由は大きく２つ
パフォーマンス
SQL に非常に単純な１カラム増やすことと割が合わないパフォーマンス。
トリガを発生させることで、本来は 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
