Oracle/Triggerで自分自身のテーブルを参照する - tak_ism

2011-10-05 13:25:44 (Wed);

トリガーで自分自身のテーブルを参照する
のなんて簡単に出来るだろうと思っていたら実は出来ないらしいのです。

Oracleのエラーメッセージ

・ORA-04091: 表 *** は変更しています。トリガー/関数は見ることができません
・ORA-04088: トリガー' *** 'の実行中にエラーが発生しました
・その表をコールしないようにトリガー（ または関数） を修正してください。
・トリガー（ または文中で参照されるユーザー定義のPL/ SQL ファンクション） が、
　 トリガーを起動した文によって変更中の表を問合せ（ または変更） しようとしました。

発生しやすい状況

update triggerの動作時に、自分のテーブルを参照したいなぁという時など
（エラーチェックを行う場合など）

e.g.)データを update するときに、予約期間が重複する場合、Triggerでエラーにしたい

予約テーブル
予約№	予約開始日	予約終了日
1	2011年11月10日	2011年11月11日
2	2011年11月13日	2011年11月15日
3	2011年11月16日	2011年11月17日

3のデータの開始日を2011年11月14日にした場合、
2のデータと期間が重複するからエラーにしたい、、などなど。

oracleの仕様なので、スマートに解決するすべはない。
以下に妥協案があるので可能であればそちらで回避

妥協案１：Triggerを自律型トランザクションに変更する

PRAGMA AUTONOMOUS_TRANSACTION;
をtrigger内で宣言する。
自律型トランザクションなので、自分のテーブルの参照が可能。

但し、それによる弊害もあるので注意する事

e.g)
テーブルA⇒テーブルBの順に更新される処理があった場合に、
テーブルBの更新時に自律型トランザクションtriggerが動作するとする。

その場合、テーブルBの自律型トランザクションtriggerでは、
テーブルAで更新したデータを参照できない。
（要するにトランザクションが独立する事になるので）

よくわからない場合は
「PRAGMA AUTONOMOUS_TRANSACTION」や
「自律型トランザクション」などで検索してみてください。

妥協案２：監視テーブルを介して実施する

• テーブル2の更新前トリガーで、テーブル1にデータを作成する。
• テーブル2の更新後トリガーで、テーブル1のデータでチェック処理を行う。

妥協案３：そもそもtriggerではやらない

あきらめるのも大事。

---