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

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

第42回 「導出列のメンテナンスにトリガーを使う」

2012.11.19

こんにちは。インストラクターの蓑島です。一段と寒くなりましたね。

数週にわたってトリガーの勉強をしてきましたが、前回までで重要な点はほぼ網羅しました。
あとはアイディア次第でさまざまなことに応用できます。
今回は応用の例として『導出列のメンテナンスにトリガーを使う』という事例を紹介します。

導出列とは、他のデータから導き出すことのできる値の列のことです。
例えば、部門表、社員表とあるときに、部門表にその部門の社員の給与合計列があるとすればその列は導出列です。
なぜなら該当部門の社員表の給与列を集計すれば導き出すことができるからです。

通常、データベース設計では正規化という考え方があり、導出列は望ましいものではありません。
しかし、場合によってはパフォーマンスを考慮して、あえて導出列を設けることもあります。

問題は、導出列を設けた場合、元のデータが更新されたら、導出列もそれに合わせて更新する必要があるということです。
つまり先の例で言えば、部門の給与合計が変わるような社員表の変動があった場合に、部門表の給与合計列もそれに合わせて更新する必要があるということです。
そのような処理をトリガーで自動化しておけば、導出列の値は常に元になるデータとリアルタイムに同期した状態を維持できます。

そのようなトリガーの実装をしてみましょう。

部門表(DEPT)に給与合計列(GSAL)を設けます。

SQL> ALTER TABLE DEPT ADD ( GSAL NUMBER);

表が変更されました。

表の定義を確認します。
SQL> DESC DEPT
 名前                                      NULL?   型
----------------------------------------------------------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)
 GSAL                                               NUMBER        ←給与合計列

DEPT表に給与合計列(GSAL)が追加されました。

次にGSAL列に、EMP表の部門ごとの給与合計をセットします。

SQL> UPDATE DEPT
  2  SET  GSAL = (SELECT SUM(SAL) FROM EMP
  3               WHERE DEPTNO = DEPT.DEPTNO);

4行が更新されました。

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

    DEPTNO DNAME               LOC                    GSAL
------------------------------------------------------------------------
        10 ACCOUNTING       NEW YORK          7450
        20 RESEARCH             DALLAS              12375
        30 SALES                    CHICAGO             9400
        40 OPERATIONS          BOSTON

GSAL列に値がセットされました。
なお部門40の社員は存在しないのでGSAL列はNULLです。

GSAL列の値は現在の社員(EMP)表から給与の合計をセットしたものなのでEMP表の給与とリアルタイムに同期はとれません。

そこで、リアルタイムに同期をとるためにトリガーを作成します。
詳細な場合分けが必要なので長い記述になります。コメントを付けわかりやすくしました。

SQL> CREATE OR REPLACE TRIGGER EMP_SAL_SUM
  2  /*****************************************************/
  3  -- 社員表が変動したら部門表の給与合計(GSAL)を更新する行トリガー
  4  /*****************************************************/
  5  AFTER
  6  INSERT OR DELETE OR UPDATE OF SAL, DEPTNO
  7  ON EMP
  8  FOR EACH ROW
  9  BEGIN
 10  /*****************************************************/
 11  -- EMP表へのINSERTの時は、その行の:NEW.SALを該当部門の
 12  --GSALに足しこむ
 13  /*****************************************************/
 14    IF INSERTING THEN
 15       UPDATE DEPT
 16       SET GSAL = NVL(GSAL,0) + :NEW.SAL
 17       WHERE DEPTNO = :NEW.DEPTNO;
 18  /*****************************************************/
 19  --EMP表からDELETEの時は、その行の:OLD.SALを該当部門の
 20  --GSALから引く
 21  /*****************************************************/
 22    ELSIF DELETING THEN
 23       UPDATE DEPT
 24       SET GSAL = NVL(GSAL,0) - :OLD.SAL
 25       WHERE DEPTNO = :OLD.DEPTNO;
 26    ELSE
 27  /*****************************************************/
 28  --EMP表へのUPDATEの場合で部門番号がUPDATEされてないときは、
 29  --給与のNEWとOLDの差をGSALに加算する
 30  /*****************************************************/
 31       IF :NEW.DEPTNO = :OLD.DEPTNO THEN
 32          UPDATE DEPT
 33          SET GSAL = NVL(GSAL,0) + :NEW.SAL - :OLD.SAL
 34          WHERE DEPTNO = :NEW.DEPTNO;
 35  /*****************************************************/
 36  --EMP表の部門番号がUPDATEされているときは
 37  --元の部門から:OLD.SALを引き新しい部門に:NEW.SALを加算する
 38  /*****************************************************/
 39       ELSE
 40          UPDATE DEPT
 41          SET GSAL = NVL(GSAL,0) - :OLD.SAL
 42          WHERE DEPTNO = :OLD.DEPTNO;
 43          UPDATE DEPT
 44          SET GSAL = NVL(GSAL,0) + :NEW.SAL
 45          WHERE DEPTNO = :NEW.DEPTNO;
 46       END IF;
 47     END IF;
 48  END;
 49  /

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

早速トリガーの確認をしてみます。

私の環境では以下のようなデータです。

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME                  LOC                 GSAL
--------------------------------------------------------------------------
        10 ACCOUNTING       NEW YORK          7450
        20 RESEARCH             DALLAS              12375
        30 SALES                    CHICAGO             9400
        40 OPERATIONS         BOSTON

部門10の給与合計は7450ですね。

また、社員番号7782のCLARKさんの所属部門は10です。

SQL> SELECT EMPNO, ENAME ,SAL ,DEPTNO FROM EMP WHERE EMPNO = 7782;

     EMPNO ENAME          SAL     DEPTNO
----------------------------------------------------
      7782 CLARK            2450        10

では、社員番号7782のCLARKさんの給与を50増やせば、トリガーにより部門10の給与合計も50増えるはずです。

やってみます。

SQL> UPDATE EMP SET SAL = SAL + 50 WHERE EMPNO = 7782;

1行が更新されました。

部門表を問い合わせます。

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME                  LOC                 GSAL
-----------------------------------------------------------------------
        10 ACCOUNTING        NEW YORK          7500 ←50増えている
        20 RESEARCH               DALLAS             12375
        30 SALES                      CHICAGO             9400
        40 OPERATIONS           BOSTON

現在のEMP表の給与を部門ごとに集計します。

SQL> SELECT DEPTNO, SUM(SAL) FROM EMP
  2  GROUP BY DEPTNO
  3  ORDER BY DEPTNO;

    DEPTNO   SUM(SAL)
--------------------------------
        10         7500
        20        12375
        30         9400

部門表の給与合計列とリアルタイムに同期が取れてますね。

トリガーが有効であれば、部門表の給与合計列は常にリアルタイムに同期がとれるわけです。

このようにトリガーを使えば導出列を自動的にメンテナンスできます。
しかし、このようなトリガーがあると、社員表を更新するとき、その1行1行でトリガーが起動するので負荷が高くなってしまうデメリットもあります。

メリット、デメリットをよく勘案しトリガーを実装すべきか決める必要があります。

それでは今回はここまでとします。次回もご期待ください。

先頭へ戻る