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

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

第41回 「BEFOREの行トリガーでは、『:NEW.列名』に値を代入できる」

2012.11.12

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

かなり寒くなってきましたね。そろそろコートを着用しようと思います。

表のDMLトリガーについて前回まで基本的なところを述べてきましたが、もう一つ重要な機能がありました。
それは、BEFOREの行トリガーによる列値の更新です。
行トリガーは、対象となるその行の上で起動しているわけですが、BEFOREの行トリガーの場合、『:NEW.列名』への代入処理により、その行のその列の値を強制的にセットできるという機能です。
(AFTERの行トリガーで同じ記述をするとコンパイルエラーです)

そのようなトリガーの実践的な例をご紹介します。

例えば、表に「登録日」列や「最終更新日」列があり、INSERTで「登録日」列に現在の日時(SYSDATE)をセットしたり、UPDATEで「最終更新日」列に現在の日時(SYSDATE)をセットするといった処理があるとします。
つまり、表の行がいつ登録され、いつ更新されたのかを管理するための情報です。
この情報登録をトリガーにより自動化することができるのです。

以下は実装例です。

まず、登録日列、最終更新日列をもつ表「TEST01」を作ります。

SQL> CREATE  TABLE TEST01
  2  (  A          NUMBER,
  3    登録日      DATE,
  4    最終更新日  DATE)
  5 /

この表に対して次のトリガーを作成します。

  1  CREATE OR REPLACE TRIGGER TEST01_TRIG
  2  /******************************************************************/
  3  -- 登録日(INSERT)または、最終更新日(UPDATE)をセットするトリガー
  4  /******************************************************************/
  5  BEFORE  INSERT OR UPDATE
  6  ON  TEST01
  7  FOR EACH ROW                --<------行トリガーの指定
  8  BEGIN
  9    IF INSERTING THEN                  -- INSERTの時
 10       :NEW.登録日 := SYSDATE;
 11    ELSE                               -- UPDATEの時
 12       :NEW.最終更新日 := SYSDATE;
 13    END IF;
 14* END;
SQL> /

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

このトリガー「TEST01_TRIG」は、TEST01表へのINSERTまたはUPDATEで起動するBEFORE行トリガーです。
9行目の「INSERTING」という記述は、このトリガーがINSERTで起動しているときにTRUEになります。
「UPDATING」や「DELETING」も可能です。
見てお分かりのように、INSERTの時は『:NEW.登録日』に、それ以外の時、つまりUPDATEの時は『:NEW.最終更新日』にSYSDATEを代入しているわけです。

それでは早速確認してみます。

まず、A列の値を 1, 2として2行INSERTします。
SQL> INSERT INTO TEST01(A) VALUES (1);

1行が作成されました。

SQL> INSERT INTO TEST01(A) VALUES (2);

1行が作成されました。

時分秒も表示されるように、DATE型の日付書式を変更しておきます。
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YY/MM/DD HH24:MI:SS';

セッションが変更されました。

TEST01表を問い合わせます。
SQL> SELECT * FROM TEST01;

         A 登録日            最終更新日
--------------------------------------------
         1 12/11/08 21:12:10
         2 12/11/08 21:12:19

それぞれの行に登録日がセットされていることがわかります。
この原稿を書いている日時がわかりますね(笑)

では、次に A=1 の行に対して、UPDATEしてみます。

SQL> UPDATE TEST01 SET A = 1 WHERE A = 1;

1行が更新されました。

そして問い合わせをすると、 A = 1 の行の最終更新日がセットされたことがわかります。

SQL> SELECT * FROM TEST01;

         A 登録日            最終更新日
--------------------------------------------
         1 12/11/08 21:12:10 12/11/08 21:12:51
         2 12/11/08 21:12:19

このように、その行がいつINSERTされ、いつUPDATEされたかが自動的にその行に記録されています。

ではここで、登録日列や最終更新日列を更新の対象としたときはどうなるのか?
と思われたかもしれません。その場合も、常にその時点のSYSDATEに更新されます。
たとえば、 A = 2の行に対して、最終更新日列を 2999/12/31に更新してみますが、それでも最終更新日列はあくまでも、更新時点のSYSDATEの値になります。

次をご覧ください。

SQL> UPDATE TEST01
2 SET 最終更新日 = TO_DATE('2999/12/31','YYYY/MM/DD')
3 WHERE A = 2;

1行が更新されました。

SQL> SELECT * FROM TEST01;

         A 登録日            最終更新日
--------------------------------------------
         1 12/11/08 21:12:10 12/11/08 21:12:51
         2 12/11/08 21:12:19 12/11/08 21:13:23 ←この行の最終更新日に注目

このように A = 2 の行の最終更新日列を2999/12/31の日付にUPDATEしても、それを更新時点のSYSDATEに書き換えていることがわかると思います。

このように、BEFOREの行トリガーで、『:NEW.列名』に代入すると、たとえその列に違う値を指定してDML操作が行われても、それを強制的に書き換えます。

今回の学習ポイントは【BEFOREの行トリガーでは、『:NEW.列名』に値を代入できる】でした。

様々なことに応用できそうですね。

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

先頭へ戻る