Oracleのサポートに不可能です....と言われましたができちゃいました。
Oracleで同一テーブルへインサートするインサートトリガー
もう8年も前の話になる。（※以下DBAネタです。)当時、メインでインフラ担当をしつつ、空いた時間で、とあるプロジェクトのにわかOracle DBAを兼任しておりました。そんな僕に課せられたミッションというのが、『あるテーブルへレコードが追加されたら、おまけでもう一個同じレコードが追加されるようにしたい』でした。ぶっちゃけ、アプリケーション側で実装すればいいじゃんとおもったのですが、アプリケーション側でそのテーブルへレコードを追加するメソッドが統一されてなくて、数も不明という状態だったのでDBのトリガーでなんとかできないか？ということでした。
トライアンドエラー
ここで、安請け合いをしてしまったのが運の尽き、DBトリガーで簡単に実現できそうだったので、ざざっと作って試してみると....見知らぬエラーが発生！！
なんだと、同一テーブルへのインサートはできない...だと。
そりゃそうだ、同一テーブルへのINSERTができるINSERTトリガーが実現できるとしたら、条件次第では無限ループ(実際にはネスト制限があるので無限ループは発生しません)が発生しDBサーバーが落ちてしまう。今度はトリガー中で別のテーブルへ一旦書き出ししてその別のテーブルにトリガーを設定し、その中で元のテーブルにインサートしてみる。しかし、やっぱり同じエラー、経由してもダメか。さすが、Oracle様ちゃんと見ていらっしゃる。
まずは、マニュアルを全部読んでおk
大抵の人はだいたい、ここらへんで諦めるみたいなのですが、当時の僕は何か違っていたようです。おもむろにあのOracleの分厚いマニュアルを端から端まで全部読みあさり、結果一つの解を見出しました。Oracleのトリガーには『行トリガー』と『文トリガー』の二種類があって、『行トリガー』には同一テーブルへのアクセスが制限される旨が記載されているが、文トリガーにはありませんでした。そこで、Oracleのサポートに文トリガーであれば同一テーブルへのインサートは可能ではないのか問い合わせを行いました。しかし、答えはノーでした。どうにも、納得がいかなかったので、論より証拠で文トリガーを作ってざざっと試したところ......。なんと今度は同一テーブルへのインサートを実現することができました！！
ただし、まだ何点か課題が残っていました。文トリガーの制限として以下2点の制限を回避する必要がありました。
- 文トリガーは文レベルなので、INSERT INTO SELECTといった1文で複数INSERTするものには対応できない
- 文トリガーは文レベルなので、行トリガーのようにINSERTされる行のデータへのアクセス手段は用意されない。
(1)についてはそのテーブルについて幸い複数INSERTするといったことはなかったので、こちらは仕様として同テーブルについては1文で複数INSERTすることを禁止することで対応しました。
(2)については(1)を前提とし、同テーブルにはシーケンスが利用されていたのを自前でSELECT max(id)文で取得するようにしました。 *1
該当テーブルが履歴系テーブルで、ほぼシーケンスを使ったインサートのみだったのが幸いでした。
諦めたらそこで試合終了だよ
プロトタイプでの実現はこれでできたものの実はここまでが全体の工程の1/4、残り3/4が大変でした。というのも100万人規模のユーザを抱えているサイトの最もレコードが追加されるテーブルでしたから、万が一があってはいけません。mod_rewriteやIEのcss expression等の再帰系プログラミングと同様、再入を防止してちゃんと処理が終了するようにしておかないと無限ループ*2に陥って大変なことになります。そこで、考えられる全パターンの一覧表を作成し、それぞれについてテストを行いました。また、パフォーマンスのテストも入念に行う必要がありました。ここのテストに時間がかかったわけです。
本番環境へ適用した当日はテストをしていたとはいえ、ほんとにドキドキしました。そして、100万人規模のサイトでちゃんとトリガーが動いてくれたときは感無量でした。
この経験から学んだことは、自分の直感が出来ると言っているのなら、人に出来ないと言われて諦める前に、とりあえずは試してみようぜ！！ってことです。
ちなみに1年後、アプリケーション側でリファクタリングが行われまして、めでたくこのトリガーはなくなりました。めでたしめでたし。
