[Oracle] トリガーで同じ表の別のレコードを削除しようとしてORA-04091エラー

あるテーブルからあるレコードが削除された時、同じテーブルから関連する別のレコードを自動的に削除したい．．．

いかにもトリガーで実現できそうな機能ですが、実際に実装してみようと試みると思うように行かないようです。

例えば、以下のようなテーブルからレコードを削除した際に、

CREATE TABLE TB1
(
    ID      NUMBER,
    TYPE    CHAR(1),
    NAME    VARCHAR2(100)
);

   ID TYPE  NAME
----- ----- ----------
    1 A     1-A
    1 B     1-B
    1 C     1-C
    1 D     1-D
    2 A     2-A
    2 B     2-B
    2 C     2-C
    3 A     3-A
    3 B     3-B

同じテーブルから関連する別のレコードを自動的に削除しようと以下のようなトリガーを作成し、

CREATE OR REPLACE TRIGGER TB1_DELETE1
AFTER DELETE 
ON TB1
FOR EACH ROW 
DECLARE
BEGIN
    DELETE FROM TB1 WHERE ID = :OLD.ID;
END;

実際にあるデータを削除して見ると、

DELETE FROM TB1 WHERE NAME = '1-A';

以下のようなエラーが発生します。

ORA-04091: 表SCOTT.TB1は変更しています。トリガー/関数は見ることができません
ORA-06512: "SCOTT.TB1_DELETE1", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE1'の実行中にエラーが発生しました

これは変更表のトリガー制限に抵触することが原因。

つまり、INSERT、UPDATE、DELETE 文で現在変更されている表はトリガーの中で操作することができないということです。

だったら、コンパイル時にエラーにしてくれよと言いたくなるところですが、それはさておきこの制限は何とか回避することはできないものでしょうか？

「Oracle Database アプリケーション開発者ガイド- 基礎編」を見ると、

変更表を更新する必要がある場合、一時表、PL/SQL 表またはパッケージ変数を使用してこれらの制限を回避することもできます。

というようなことが書いてあります。

そこで、以下のような一時表を作成し、

CREATE GLOBAL TEMPORARY TABLE TMP_TB1
(
    ID    NUMBER
)
ON COMMIT DELETE ROWS;

さきほど作成したトリガーでは直接データを DELETE せず、データの削除に必要な情報（ここでは ID ）を一時表に INSERT し、

CREATE OR REPLACE TRIGGER TB1_DELETE1
AFTER DELETE 
ON TB1
FOR EACH ROW 
DECLARE
BEGIN
    INSERT INTO TMP_TB1 VALUES (:OLD.ID);
END;

別途以下のような文トリガーを作成して、一時表に保存したデータの削除に必要な情報を元にデータを DELETE するように変更してみると、

CREATE OR REPLACE TRIGGER TB1_DELETE2
AFTER DELETE 
ON TB1
DECLARE
BEGIN
    DELETE FROM TB1 WHERE ID IN ( SELECT ID FROM TMP_TB1 );
END;

今度は以下のようなエラーが発生します。

ORA-00036: 再帰的SQLレベルの最大値(50)を超えました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました
ORA-06512: "SCOTT.TB1_DELETE2", 行3
ORA-04088: トリガー'SCOTT.TB1_DELETE2'の実行中にエラーが発生しました

データ操作を追っていくと再帰的に実行されなさそうに思えるのですが、実際には再帰トリガーと認識されてしまうようです。

以下のように WHEN 句でトリガーの起動条件を絞り込んでみても変わりません。

CREATE OR REPLACE TRIGGER TB1_DELETE1
AFTER DELETE 
ON TB1
FOR EACH ROW WHEN (OLD.TYPE = 'A')
DECLARE
BEGIN
    INSERT INTO TMP_TB1 VALUES (:OLD.ID);
END;

どうやらこのような操作をトリガーで実装するのは諦めた方がよさそうです^^;

Archive Redo Blog

DBエンジニアのあれこれ備忘録

[Oracle] トリガーで同じ表の別のレコードを削除しようとしてORA-04091エラー