トリガーカスケード制限 #1 Tweet

やっと時間が取れるようになったのでしばらくは純粋にデータベースと戯れることができそうだ。w

ということで、キムラデービーブログ：徹底比較!! PostgreSQL vs MySQLパート2 with 勝手に vs Firebird (13)に面白そうなこと（以下の引用部分）が書いてあったので実際に試してみる事にした。

*2: 　カスケードは、例えばOracleは32個に制限されており、それ以下に設定することも可能です。

　これは例えば、あるトリガーから発せられたSQLによりトリガーが発せらるような場合で、ヘタすると、それが無限ループになってしまう。それを防ぐような形で制限されている(のだと思います。それか、もしかしたら実装上の制限かもしれません)

　勇気ある人は、無限にお互いを呼び合うようなトリガーを書いて、ご自分のプラットフォームで実行させてみてください。そして結果を木村まで知らせてください(^_^;)

環境はOracle11g EE for Linux x86/CentOS5という組み合わせです。詳しい環境は以前の記事をご参考に。

Oracle de XMLDB #6 - Linux x86版再び。
Mac de Ruby on Rails - #2 - Oracleと遊ぶ #1

では実験開始。

今回はある表に定義したINSERTトリガーをカスケードさせ無限に起動する状況を作ってみた。

１）まず、トリガーを定義する以下のような単純な表を作成。

SCOTT> desc master
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 SEQ#                                               NUMBER

２）定義するトリガーはmaster表へのINSERT文で起動しシーケンスから連番を取得し
　　トリガーが定義されているmaster表へ再度INSERT文を発行する。（無限ループするカスケードを実装した）

SCOTT> select text      
  2  from all_source
  3  where name = 'INS_CASCADE_TEST'
  4  order by line;

TEXT
--------------------------------------------------------------------------------
TRIGGER INS_CASCADE_TEST
  after insert on master
begin
  insert into master values(master_seq.nextval);
end;

上記トリガーを起動すれば、Oracleのマニュアル：論理データベースの制限のトリガー・カスケード制限に記載されている最大値に達すればエラーになるはず。無限ループするカスケードを実装したとしても実行途中でエラーとなり停止するはずだ。
マニュアルにはプラットーフォーム依存だが、通常は３２と記載されている。私の記憶違いでなければ、この値はOracle7の頃から変っていない。

３）では実行！

・実行前のシーケンス値の確認！。

SCOTT> select last_number
  2  from user_sequences
  3  where sequence_name = 'MASTER_SEQ';

LAST_NUMBER
-----------
          1

・実行！

SCOTT> insert into master values(-100);
insert into master values(-100)
            *
行1でエラーが発生しました。:
ORA-00036: 再帰的SQLレベルの最大値(50)を超えました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:

予想通りエラーにはなったもののプラットフォーム依存と記載されている通り？、Linux(32bit)環境ではカスケードできる最大値は３２ではなく、４９というコンピュータの世界では半端な値になっているようだ。LinuxではなくWindowsでは３２なのだろうか？？？？

SCOTT> 
SCOTT> select last_number
  2  from user_sequences
  3  where sequence_name = 'MASTER_SEQ';

LAST_NUMBER
-----------
         51

SCOTT> 

次回は、linux(32bit)環境でトリガーカスケード制限が４９であるということを無限ループするトリガーを使わずに確認してみることにする。

カスケードの制限値を超えた場合にどうなるのかなんて確認したことなど一度も無かったので面白い発見だ！ :)