14.3. トリガーを用いた例

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

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

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 で同じことをする場合の例です。

 
 /* 関数の定義 */
  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 上で行うものとする。

解答例はこちら