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

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

第40回 「トリガー処理のルール(してはいけないこと)」

2012.11.05

こんにちは。インストラクターの蓑島です。

この連載も40回を迎えました。
毎回少しずつPL/SQLを勉強してきた皆さんは、もうかなりPL/SQLの力がついたはずですから、自信を持ってください。
では今回もがんばりましょう。

今まで、PL/SQLトリガーについて、基本的な説明といくつかの事例をご紹介してきました。

では、トリガーは文法的に正しければどのような処理でも行えるのでしょうか?

やはり、そういうわけにはいかないのです。
たとえ文法的には正しくても(コンパイルが正常に出来ても)、トリガーの処理として許可できない(実行時エラーとなる)処理があるのです。

代表的なものは次の二つです。

1.COMMIT, ROLLBACK, SAVEPOINT
2.そのトリガーの設定されている表に対するSELECT, INSERT, DELETE ,UPDATE 処理
(ただし行トリガーについて)

まず、上記1.については納得できるところと思います。
つまりトリガーは自動的にバックグラウンドで起動する仕組みですから、その中で勝手にトランザクションを確定したり、取り消したりされては困るわけです。

次に2.ですが、行トリガーの場合、そのトリガーの設定されている表(変更表という)に対して、トリガーの中でその変更表をSELECT, INSERT, UPDATE, DELETE してはいけないというルールがあります。
行トリガーは、更新の対象となるその行の上で起動してますので、その行に対しては『:OLD.列名』、『:NEW.列名』で列値を参照できる(前回の内容)のですが、 その表(変更表)に対しては、SQL文でアクセスできないのです。

例えば以下のトリガーの例をご覧ください。

SQL> CREATE OR REPLACE TRIGGER EMP_SAL_CHECK_TRIG
  2  /************************************************************/
  3  -- EMP表に対する行トリガー   ルールに違反しているケース
  4  /************************************************************/
  5  BEFORE
  6     INSERT OR UPDATE OF JOB, SAL ON EMP FOR EACH ROW
  7  DECLARE
  8  /************************************************************/
  9  -- 該当職種の最低、最高給与を格納する変数
 10  /************************************************************/
 11     JOB_MIN_SAL  NUMBER;
 12     JOB_MAX_SAL  NUMBER;
 13  BEGIN
 14  /************************************************************/
 15  -- 対象行の職種(:NEW.JOB)でEMPをSELECTし最低、最高給与取得
 16  /***********************************************************/
 17     SELECT MIN(SAL), MAX(SAL) INTO JOB_MIN_SAL, JOB_MAX_SAL
 18     FROM   EMP
 19     WHERE  JOB = :NEW.JOB;
 20  /************************************************************/
 21  -- 対象行の給与(:NEW.SAL)がその範囲外ならエラーとする
 22  /************************************************************/
 23     IF :NEW.SAL NOT BETWEEN JOB_MIN_SAL AND JOB_MAX_SAL THEN
 24         RAISE_APPLICATION_ERROR(-20000,'職種給与範囲外エラー');
 25     END IF;
 26  END;
 27  /

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

このトリガーは、EMP表(社員表)に対するINSERT、またはJOB列(職種)、SAL列(給与)をUPDATEするときに起動するBEFOREの行トリガーですが、その給与の額が、その職種における社員表内の最低給与、最高給与の範囲外であればエラーにするトリガーです。
文法エラーはありませんので、コンパイルが正常にできますが、しかし、実行時にエラーになります。

以下の例をご覧ください。

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

UPDATEによりトリガーが起動しましたが、予定外のエラーとなってます。

原因はこのトリガーがEMP表に対する行トリガーであるにもかかわらず、EMP表をSELECTしている(17-19行目)からです。

このエラーを回避するためには、行トリガーから自分の表(変更表)を参照しなければいいので、事前に職種ごとの最低、最高給与額を格納した他の表を作っておいて、その表を参照するようにトリガーを修正します。

以下の例をご覧ください。

職種ごとの最低、最高給与を格納する表(JOB_SAL)を作成します。

SQL> CREATE TABLE JOB_SAL
  2  AS
  3  SELECT JOB, MIN(SAL) AS MIN_SAL, MAX(SAL) AS MAX_SAL
  4  FROM EMP
  5  GROUP BY JOB
  6  /

表が作成されました。

このようなデータです。
SQL> SELECT * FROM JOB_SAL;

JOB             MIN_SAL      MAX_SAL
------------------------------------------------
CLERK                 800       1500
SALESMAN        1250       1600
PRESIDENT       5000       5000
MANAGER          2450       2975
ANALYST            3000       3000

先ほどのトリガーを修正します。17-19行目のSELECT文に注目してください。

SQL> CREATE OR REPLACE TRIGGER EMP_SAL_CHECK_TRIG
  2  /************************************************************/
  3  -- EMP表に対する行トリガー
  4  /************************************************************/
  5  BEFORE
  6     INSERT OR UPDATE OF JOB, SAL ON EMP FOR EACH ROW
  7  DECLARE
  8  /************************************************************/
  9  -- 該当職種の最低、最高給与を格納する変数
 10  /************************************************************/
 11     JOB_MIN_SAL  NUMBER;
 12     JOB_MAX_SAL  NUMBER;
 13  BEGIN
 14  /************************************************************/
 15  -- 対象行の職種(:NEW.JOB)でJOB_SAL表をSELECTし最低、最高給与取得
 16  /***********************************************************/
 17     SELECT MIN_SAL, MAX_SAL INTO JOB_MIN_SAL, JOB_MAX_SAL
 18     FROM   JOB_SAL
 19     WHERE  JOB = :NEW.JOB;
 20  /************************************************************/
 21  -- 対象行の給与(:NEW.SAL)がその範囲外ならエラーとする
 22  /************************************************************/
 23     IF :NEW.SAL NOT BETWEEN JOB_MIN_SAL AND JOB_MAX_SAL THEN
 24         RAISE_APPLICATION_ERROR(-20000,'職種給与範囲外エラー');
 25     END IF;
 26  END;
 27  /

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

修正したのは、17-19行目のSELECT文です。
対象行の職種(:NEW.JOB)で最低、最高給与を取得するために、EMP表ではなく、JOB_SAL表を問い合わすように修正したわけです。
これでこのトリガーは意図したとおりに働きます。

では確認してみます。たとえば、職種CLERKの給与の最低、最高は以下の通りです。

SQL> SELECT * FROM JOB_SAL WHERE JOB = 'CLERK';

JOB          MIN_SAL    MAX_SAL
------------------------------------------------
CLERK            800       1500

職種CLERKの給与は、800から1500ですね。

また、社員番号7900 JAMESさんは、職種がCLERKで給与は950です。

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE EMPNO = 7900;

ENAME      JOB              SAL
------------------------------------------------
JAMES      CLERK            950

ではこのJAMESさんの給与を範囲外の1501にUPDATEします。

SQL> UPDATE EMP SET SAL = 1501 WHERE EMPNO = 7900;
UPDATE EMP SET SAL = 1501 WHERE EMPNO = 7900
*
行1でエラーが発生しました。:
ORA-20000: 職種給与範囲外エラー
ORA-06512: "SCOTT.EMP_SAL_CHECK_TRIG", 行18
ORA-04088: トリガー'SCOTT.EMP_SAL_CHECK_TRIG'の実行中にエラーが発生しました

このように範囲外の時は予定通りトリガーにより発生させたエラー(ORA-20000: 職種給与範囲外エラー)となりました。

では、範囲内の1500にUPDATEします。

SQL> UPDATE EMP SET SAL = 1500 WHERE EMPNO = 7900;

1行が更新されました。

範囲内であれば、予定通りエラーなく更新できます。

このように、給与の更新などで範囲をチェックする行トリガーが自分の表を参照するとエラーになりますが、他の表を参照するのであれば問題なく処理できる様子が確認できたと思います。

「行トリガーでは自分の表はSQL操作できない」というルールに気をつけてください。

それでは今回はここまでにします。また次回、ご期待ください。

先頭へ戻る