e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第58回 「外部キー制約エラーでもAFTERの行トリガーは起動する」

2013.05.20

こんにちは。インストラクターの蓑島です。先週は梅雨前のさわやかな天候が続いていましたね。今日はあいにくの雨ですが、明日からまた回復するようです。このまますごしやすい天気が続いてくれればと思います。

さて、前回、自律型トランザクションとデータベーストリガーを利用した監査の応用例を紹介しました。そして、補足があるということで今回に続いた訳ですね。

その補足というのは、今回の表題にもなっている「外部キー制約エラーでもAFTERの行トリガーは起動する」ということです。今回紹介する内容は自律型トランザクションとは直接の関連はありませんので、注意してください。

前回までのトリガーの説明で、一つのDML文(例 UPDATE)で複数行が更新されるときに、まず最初の1行目の上でBEFOREの行トリガーが起動し、それが成功すればその行が更新され、それが成功すればその行の上でAFTERの行トリガーが起動し、それが成功すれば次の更新対象の行に移動し同じことを繰り返す、という説明をしました。そしてどの段階でもエラーとなれば、それまで起動していたトリガーの行った処理はすべて取り消しになります。(ただし自律型トランザクションの処理は取り消しになりません)

したがって、一般に行の更新時にエラーが起きれば、そこでDML文はエラーで終わり、それまで起動していたすべてのトリガーの処理が取り消しになります。(もちろん自律型トランザクションの処理は確定ずみなので、取り消されません)しかし、例外的に、外部キー制約エラーの場合は、エラーの行があっても、最後の更新対象行までAFTERの行トリガーが起動することになっています。しかし最後の行までトリガーが起動しても、その時点で外部キー制約違反の状態が解消されていなければ、結局は一連のトリガーが行っていた処理は自律型トランザクションの処理を除き、すべて取り消しされ、DMLのステータスはエラーで終了します。

つまり、逆に言うと、外部キー制約エラーが起きる状況でもAFTERの行トリガーの中でそれに対応すればDML文は正常に処理できるわけです。

ではそのような事例を実際に行ってみましょう。

では、外部キー制約なので、親表と子表を用意します。

/************************/
-- 親表(OYA)の作成
/************************/
SQL> CREATE TABLE OYA
  2  ( ID   NUMBER CONSTRAINT OYA_PK PRIMARY KEY,
  3    C1   VARCHAR2(10))
  4  /

表が作成されました。

/************************/
-- 子表(KO)の作成
/************************/
SQL> CREATE TABLE KO
  2  ( ID NUMBER CONSTRAINT KO_PK PRIMARY KEY,
  3    C1 VARCHAR2(10),
  4    OYA_ID   NUMBER CONSTRAINT KO_FK REFERENCES OYA(ID))  ←外部キー
  5  /

これで親表(OYA)と子表(KO)ができました。早速、行をいくつかINSERTします。

SQL> INSERT INTO OYA VALUES (1,'AB');

1行が作成されました。

SQL> INSERT INTO OYA VALUES (2,'CD');

1行が作成されました。

SQL> INSERT INTO KO VALUES (10,'ab',1);

1行が作成されました。

SQL> INSERT INTO KO VALUES (20,'cd',1);

1行が作成されました。

SQL> INSERT INTO KO VALUES (30,'ef',2);

1行が作成されました。

現在、親表(OYA)と子表(KO)は以下のようなデータとなってます。

SQL> SELECT * FROM OYA;

        ID            C1
------------- -------------
         1            AB
         2            CD

SQL> SELECT * FROM KO;

        ID              C1        OYA_ID
------------- ------------- -------------
        10              ab            1
        20              cd            1
        30              ef             2

では早速、外部キーに違反する更新がエラーとなることを確認します。

SQL> UPDATE KO SET OYA_ID = 99 WHERE ID = 10;
UPDATE KO SET OYA_ID = 99 WHERE ID = 10
*
行1でエラーが発生しました。:
ORA-02291: 整合性制約(SCOTT.KO_FK)に違反しました - 親キーがありません

子表の外部キーを親表の親キーに存在しない値(99)にUPDATEしようとすると、外部キー制約(整合性制約)に違反してエラーになりました。これは当然ですね。では、ここで子表に新規の行をINSERTしたり、子表の外部キー(OYA_ID列)を更新するとき、その外部キーの値が親表の親キーに存在しない場合に、そのキーで親表に新規の親レコードを登録(INSERT)するトリガーを作ってみます。

/***************************************************************************/
-- 親キーに存在しない値に外部キーを更新すると、新規の親キーをINSERTするトリガー
/***************************************************************************/
SQL> CREATE OR REPLACE TRIGGER KO_BEFORE_ROW_TRIG
  2  AFTER INSERT OR UPDATE OF OYA_ID
  3  ON KO
  4  FOR EACH ROW
  5  DECLARE
  6     DUMMY  CHAR(1);
  7  BEGIN
  8     SELECT 'X' INTO DUMMY FROM OYA WHERE ID = :NEW.OYA_ID;
  9  EXCEPTION
 10     WHEN NO_DATA_FOUND THEN
 11      INSERT INTO OYA VALUES (:NEW.OYA_ID,'BY TRIGGER');
 12  END;
 13  /

トリガーが作成されました。

上記のトリガーの処理を簡単に解説します。まずこのトリガーは子表(KO)にINSERT、または外部キー(OYA_ID列)のUPDATEの時に起動するAFTERの行トリガーです。(2行目~4行目)
そして、INSERTやUPDATEする外部キーの値(:NEW.OYA_ID)が、OYA表の親キーに存在するかどうかを8行目のSELECT INTO文で調べています。存在すればELECT INTO 文は1行結果を返すので、エラーにはならずこのトリガーは何も処理しません。もしこのSELECT INTO 文が0件であれば、つまり、親キーが親表に存在しなければ、エラー(NO_DATA_FOUND例外)となるので、11行目のINSERT文を実行し、親表に新規の親キーで親レコードを登録するわけです。その行は、わかりやすく C1列の値を'BY TRIGGER'としています。
※SELECT INTO 文のエラーについてはバックナンバー「第6回 SELECT INTO文の例外処理」を参照してください。

では先ほど、エラーだったUPDATE文を再び実行します。

SQL> UPDATE KO SET OYA_ID = 99 WHERE ID = 10;

1行が更新されました。

今度はエラーになりませんね。データも見てみましょう。

SQL> SELECT * FROM OYA;

        ID             C1
------------- -------------
         1             AB
         2             CD
        99            BY TRIGGER    ←子表のトリガーにより追加された親レコード

SQL> SELECT * FROM KO;

        ID            C1          OYA_ID
------------- ------------- -------------
        10            ab             99    ← UPDATEされた外部キー
        20            cd             1
        30            ef              2

期待通りの動きですね。これで子表の外部キーの値を存在しない親キーの値に更新しても、新規の親レコードが自動的に登録されるので、エラーになりません。

では、次に親表側の更新で外部キーエラーになる場合についても対応してみたいと思います。まず、子レコードのある親表の親レコードの親キーを更新しようとすると外部キー制約のエラーとなります。これは当然ですね。やってみます。

SQL> UPDATE OYA SET ID = 88 WHERE ID = 1;
UPDATE OYA SET ID = 88 WHERE ID = 1
*
行1でエラーが発生しました。:
ORA-02292: 整合性制約(SCOTT.KO_FK)に違反しました - 子レコードがあります

確かに外部キー制約(整合性制約)のエラーになります。

では、子レコードのある親レコードの親キーを更新するときに該当する子レコードの外部キーの値を同じ値に更新するトリガーを作ります。以下のようなトリガーです。

/***************************************************************************/
-- 子レコードのある親キーを更新すると子レコードの外部キーも更新するトリガー
/***************************************************************************/
SQL> CREATE OR REPLACE TRIGGER OYA_AFTER_ROW_TRIG
  2  AFTER  UPDATE OF ID
  3  ON  OYA
  4  FOR EACH ROW
  5  BEGIN
  6     UPDATE KO
  7     SET  OYA_ID = :NEW.ID
  8     WHERE  OYA_ID = :OLD.ID;
  9  END;
 10  /

トリガーが作成されました。

上記のトリガーの処理を簡単に解説します。まずこのトリガーは親表(OYA)の親キー(OYA_ID列)をUPDATEする時に起動するAFTERの行トリガーです。(2行目~4行目) そして、元の親キーの値(:OLD.ID)を外部キーにもつ子レコードの外部キー列を新しい親キーの値(:NEW.ID)に更新するUPDATE文を実行する訳です。(6~8行目)。たいへんシンプルですね。
なお、子レコードのない親キーを更新する場合はこのUPDATEは0件となりますが、問題ではありません。UPDATEなどDML文は SELECT INTO 文と違って処理行数によってエラーになることはないからです。

では先ほど、エラーになった親表のUPDATEを再び実行します。これでうまくいくと思われますが、しかしまだエラーとなります。

SQL> UPDATE OYA SET ID = 88 WHERE ID = 1;
UPDATE OYA SET ID = 88 WHERE ID = 1
       *
行1でエラーが発生しました。:
ORA-04091: 表SCOTT.OYAは変更しています。トリガー/関数は見ることができません
ORA-06512: "SCOTT.KO_BEFORE_ROW_TRIG", 行2
ORA-04088: トリガー'SCOTT.KO_BEFORE_ROW_TRIG'の実行中にエラーが発生しました
ORA-06512: "SCOTT.OYA_AFTER_ROW_TRIG", 行2
ORA-04088: トリガー'SCOTT.OYA_AFTER_ROW_TRIG'の実行中にエラーが発生しました

なぜトリガーで対応したのにエラーになったかというと、上記の事例は、バックナンバー第40回 「トリガー処理のルール(してはいけないこと)」で説明した「行トリガーでは自分の表はSQL操作できない」というルールに違反しているからです。

しかし、先ほど作成した親表のトリガーの中でUPDATEしたのは子表であって親表ではありません。よって表面的には自分の表をSQL操作していないように思われます。しかし、親表のトリガーが子表をUPDATEすることで今回の最初に作った子表のトリガーが起動しその中で親表を参照しているので間接的に自分の表を参照しているのです。つまりトリガー起動の連鎖により、ぐるっとまわってもとの親表を参照することになるのでトリガーのルールに違反するわけです。以下のような図式です。

親表の更新 ⇒ 親表の行トリガー起動 ⇒ 子表の更新 ⇒ 子表の行トリガー起動 ⇒ 親表を参照

このように親表の行トリガーが間接的ですが親表を参照するのでエラーとなります。

今回の事例では親表と子表のそれぞれの行トリガーが相互に相手の表を更新するので、このエラーを回避することは困難です。(更新ではなく参照(SELECT)だけならパッケージ変数などを利用することでこのエラーを回避できます。別の機会に解説します)

やむを得ないので、子表のトリガーを無効にします。

SQL> ALTER TRIGGER KO_BEFORE_ROW_TRIG DISABLE;

トリガーが変更されました。

そして、もう一度、親レコードの親キーを更新します。

SQL> UPDATE OYA SET ID = 88 WHERE ID = 1;

1行が更新されました。

今度はエラーにならずに更新できました。データも参照してみます。

SQL> SELECT * FROM OYA;

        ID             C1
------------- -------------
        88            AB               ←親キーを更新すると・・
         2             CD
        99            BY TRIGGER

SQL> SELECT * FROM KO;

        ID            C1          OYA_ID
------------- ------------- -------------
        10            ab             99
        20            cd             88     ←子表の外部キーも更新された
        30            ef              2

期待通りにトリガーが働いていることがわかります。

このように外部キー制約エラーでも例外的にAFTERの行トリガーは起動することになっています。それを利用してトリガーを使った参照性整合性を実装できるわけです。

親キーを変更した場合の対応と、外部キーを変更する場合の対応と、どちらもそれぞれの表のトリガーで対応できればいいのですが、トリガー処理のルールに反するので、それはなかなか難しいと思います。

では今回はここまでにいたします。次回からは違ったテーマ(バルクバインド)を予定しています。ご期待ください。

先頭へ戻る