目次へ

14.3. トリガーを用いた例

ここまで、トリガーの基本的な使い方について説明してきました。ここでは、トリガーの利用例を具体的に説明します。

ここで取り上げる例について説明します。次のような「顧客表」と「更新記録表」を処理対象のテーブルとします。顧客表にデータを記録するたびに、自動的に更新記録表にもデータが追加されるというものです。

顧客表 (CUSTOMER)
RDBMS 顧客番号
c_num
名前
name
住所
addr
ORACLE NUMBER CHAR(20) CHAR(40)
PostgreSQL INTEGER CHAR(20) CHAR(40)
更新記録表 (RECORD_LOG)
RDBMS 更新
日時
update_time
更新
種別
method
更新前
顧客番号
old_c_num
更新前
名前
old_name
更新前
住所
old_addr
更新後
顧客番号
new_c_num
更新後
名前
new_name
更新後
住所
new_addr
ORACLE DATE CHAR
(10)
NUMBER CHAR
(20)
CHAR
(40)
NUMBER CHAR
(20)
CHAR
(40)
Postgre SQL DATETIME CHAR
(10)
INTEGER CHAR
(20)
CHAR
(40)
INTEGER CHAR
(20)
CHAR
(40)

ORACLE の場合は次のように記述します。

<<ORACLE>>
 /* トリガーの定義 */
  CREATE TRIGGER RECORD_LOG_TRIG
    AFTER INSERT OR UPDATE OR DELETE
    ON CUSTOMER
    FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        INSERT INTO RECORD_LOG(
          update_time, method,
          new_c_num, new_name, new_addr)
        VALUES(SYSDATE,'INSERT',
          :NEW.c_num, :NEW.name, :NEW.addr) ;
      ELSIF UPDATING THEN
        INSERT INTO RECORD_LOG(
          update_time, method,
          old_c_num, old_name, old_addr,
          new_c_num, new_name, new_addr)
        VALUES(SYSDATE,'UPDATE',
          :OLD.c_num, :OLD.name, :OLD.addr,
          :NEW.c_num, :NEW.name, :NEW.addr) ;
      ELSIF DELETING THEN
        INSERT INTO RECORD_LOG(
          update_time, method,
          old_c_num, old_name, old_addr)
        VALUES(SYSDATE,'DELETE',
          :OLD.c_num, :OLD.name, :OLD.addr) ;
      END IF ;
    END ;

 /* CUSTOMER テーブルへのデータ操作 */
  INSERT INTO CUSTOMER
    VALUES(1001, 'NONAKA', 'KYOTO') ;
  INSERT INTO CUSTOMER
    VALUES(1002, 'MORI', 'ISHIKAWA') ;
  INSERT INTO CUSTOMER
    VALUES(1003, 'KAMEI', 'HIROSHIMA') ;
  INSERT INTO CUSTOMER
    VALUES(1004, 'KOIZUMI', 'TOKYO') ;
  DELETE FROM CUSTOMER WHERE name = 'MORI' ;
  UPDATE CUSTOMER SET addr = 'KANAGAWA'
    WHERE name = 'KOIZUMI' ;

 /* RECORD_LOG の確認 */
  SELECT * FROM RECORD_LOG ;

実行結果
 update_time  method old_c_num  old_name   old_addr new_c_num  new_name   new_addr
 ──────────── ────── ────────── ────────── ──────── ────────── ────────── ────────
 2001-04-01   INSERT NULL       NULL       NULL     1001       NONAKA     KYOTO
 2001-04-01   INSERT NULL       NULL       NULL     1002       MORI       ISHIKAWA
 2001-04-01   INSERT NULL       NULL       NULL     1003       KAMEI      HIROSHIMA
 2001-04-01   INSERT NULL       NULL       NULL     1004       KOIZUMI    TOKYO
 2001-04-01   DELETE 1002       MORI       ISHIKAWA NULL       NULL       NULL
 2001-04-01   UPDATE 1004       KOIZUMI    TOKYO    NULL       NULL       KANAGAWA

この記述に対して少し説明します。トリガーの定義の中にある INSERTING、UPDATING 及び DELETING は、トリガーがどのデータ操作文により起動したのかを示すものです。たとえば、INSERT 文で呼び出されたトリガーが起動された場合は INSERTING は TRUE を返します。そうでない場合は FALSE を返します。

:NEW.列名と :OLD.列名は、データ操作が行われる前の値と、その後の値を示しています。INSERT 文が行われた場合、もともとデータはなかったわけですから、:OLD.列名の値は NULL になります。また、:NEW.列名には新しく挿入されたデータの値が入ります。UPDATE 文では、変更される前の値は :OLD.列名、変更後の値は :NEW.列名に格納されます。DELETE 文では新しいデータはありませんので、:NEW.列名は NULL になり、DELETE される前のデータは :OLD.列名に格納されます。

次に PostgreSQL で同じことをする場合の例です。

<<PostgreSQL>>
 
 /* 関数の定義 */
  CREATE FUNCTION UPDATE_FUNC() RETURNS OPAQUE
    AS '
    BEGIN
      IF TG_OP = ''INSERT'' THEN
        INSERT INTO RECORD_LOG(
          update_time, method,
          new_c_num, new_name, new_addr)
        VALUES(''now'',''INSERT'',
          new.c_num, new.name, new.addr) ;
        RETURN new ;
      ELSE
        IF TG_OP = ''UPDATE'' THEN
          INSERT INTO RECORD_LOG(
            update_time, method,
            old_c_num, old_name, old_addr,
            new_c_num, new_name, new_addr)
          VALUES(''now'',''UPDATE'',
            old.c_num, old.name, old.addr,
            new.c_num, new.name, new.addr) ;
          RETURN new ;
        ELSE
          INSERT INTO RECORD_LOG(
            update_time, method,
            old_c_num, old_name, old_addr)
          VALUES(''now'',''DELETE'',
            old.c_num, old.name, old.addr) ;
          RETURN old ;
        END IF ;
      END IF ;
    END ;
    '
    LANGUAGE 'plpgsql' ;

 /*トリガーの定義 */
  CREATE TRIGGER RECORD_LOG_TRIG
    AFTER INSERT OR UPDATE OR DELETE
    ON CUSTOMER
    FOR EACH ROW
    EXECUTE PROCEDURE UPDATE_FUNC() ;

 /* CUSTOMER テーブルへのデータ操作 */
  INSERT INTO CUSTOMER
    VALUES(1001,'NONAKA','KYOTO') ;
  INSERT INTO CUSTOMER
    VALUES(1002,'MORI','ISHIKAWA') ;
  INSERT INTO CUSTOMER
    VALUES(1003,'KAMEI','HIROSHIMA') ;
  INSERT INTO CUSTOMER
    VALUES(1004,'KOIZUMI','TOKYO') ;
  DELETE FROM CUSTOMER WHERE name = 'MORI' ;
  UPDATE CUSTOMER SET addr = 'KANAGAWA'
    WHERE name = 'KOIZUMI' ;

 /* RECORD_LOG の確認 */
  SELECT * FROM RECORD_LOG ;

実行結果
 update_time  method old_c_num  old_name   old_addr new_c_num  new_name   new_addr
 ──────────── ────── ────────── ────────── ──────── ────────── ────────── ────────
 2001-04-01   INSERT NULL       NULL       NULL     1001       NONAKA     KYOTO
 2001-04-01   INSERT NULL       NULL       NULL     1002       MORI       ISHIKAWA
 2001-04-01   INSERT NULL       NULL       NULL     1003       KAMEI      HIROSHIMA
 2001-04-01   INSERT NULL       NULL       NULL     1004       KOIZUMI    TOKYO
 2001-04-01   DELETE 1002       MORI       ISHIKAWA NULL       NULL       NULL
 2001-04-01   UPDATE 1004       KOIZUMI    TOKYO    1004       KOIZUMI    KANAGAWA

PostgreSQL の場合は、予め関数を定義しておかなければなりません。関数中の TG_OP という変数は、特別なシステム変数で、テーブルに対して行われたデータ操作が何であるのかが格納されます。INSERT 文が実行された場合は 'INSERT'、UPDATE 文の場合は 'UPDATE'、DELETE 文の場合は 'DELETE' が格納されます。

また、ORACLE における :OLD.列名と:NEW.列名は、PostgreSQL では old.列名及び new.列名という風に記述します。

実習課題 1

以下の要求を満たすトリガーを定義しなさい。

  • データの入力時にパーセンテージで入力される数値を、テーブルにはその数値を 100 で割った値を入力したい。
  • テーブル及び必要となる関数は適宜定義すること。
  • このトリガーの定義は PorstgreSQL 上で行うものとする。

解答例はこちら

実習課題 2

以下の要求を満たすトリガーを定義しなさい。

  • 販売管理データベースのテーブル accept_order にデータが追加されるたびに、別テーブルにそのデータから求められる支払い金額を書き込む。
  • 別テーブルに書き込まれるデータは、受注番号と支払い金額とする。
  • このトリガーの定義は ORACLE 上で行うものとする。

解答例はこちら

↑このページの先頭へ

こちらもチェック!

PR
  • XMLDB.jp
  • シナジーマーケティング研究開発グループブログ
Loading