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

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

第74回 「ビューを通してのデータ更新(INSTEAD OF トリガーを使って)」

2013.10.21

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

前回から、ビューを通してのデータ更新をテーマに解説しております。
一般にビューを通したデータ更新(INSERT, UPDATE, DELETE)は、そのビューが単純なSELECT文で定義したビューであれば可能です。
すなわち、表のデータを集約したり加工したりせずにそのままのデータを表示するようなビューであれば、そのビューを通してデータの更新が一般的に可能なのです。 そのような場合、データベース側で、ビューに対するDML文(INSERT, UPDATE, DELETE)を自動的に表に対するDML文に変換することによって、データを更新するわけです。

しかし、複雑な定義のビューに対するDML文は、データベース側で表に対するDML文に変換することができないため、エラーとなります。
しかし、データベース側で表に対するDML文に変換できないのであれば、開発者側で用意したプログラムによって、表に対するDML文に変換することで、ビューに対するDMLを可能にできます。それが「INSTEAD OF トリガー」です。

「INSTEAD OF」は、「~の代わりに」という意味ですね。
いわばビューに対するDML文の代わりに、表に対するDMLを実行するトリガーといった意味のトリガーです。

ではこれから「INSTEAD OF トリガー」を作成するにあたっての考え方を解説します。

「INSTEAD OF トリガー」は表ではなくビューに対して定義します。
表に対するトリガーでありません。しかし、そのビューがあたかも存在する表であるかのように見立てて考えてください。
そして「INSTEAD OF トリガー」はその表に対する「行トリガー」と考えてください。
つまり、その表(本当はビューですが・・)の更新対象の1行1行について起動します。
そして行トリガーなので、その行の列値が参照できます。
すなわち、「:OLD.列名」で更新前の値、「:NEW.列名」で更新後の値が参照できるわけです。
ですから、普通の表の行トリガーとまったく同じですね。

そして、その「:OLD.列名」や、「:NEW.列名」の値を使って、なにか「他の表」を更新(INSERT, UPDATE, DELETE)できるわけです。
その「他の表」がすなわち、ビュー定義のもとになっている表のことです。
それは一つの表でも複数の表でも構いません。それらの表に対する更新を行うことができるわけです。

このような考え方で、ビューに対するDMLがあったときに「INSTEAD OF トリガー」により、表に対するDMLを実行するわけです。

では具体的な例でデモします。構文解説も一緒に行います。
以下のようなビューがあります。
社員表(EMP表)と、部門表(DEPT表)を結合し、部門ごとの給与合計を問い合わすビューです。表を結合して、集約している複雑な定義のビューです。

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE VIEW V_GSAL
AS
SELECT D.DEPTNO, D.DNAME, SUM (E.SAL) AS  GSAL
FROM  EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME
/
 
ビューが作成されました。

実際に問い合わせしてみます。

1
2
3
4
5
6
7
SELECT * FROM V_GSAL;
 
     DEPTNO DNAME                              GSAL
---------- ---------------------------- ----------
         10 ACCOUNTING                         8750
         20 RESEARCH                          10875
         30 SALES                              9400

このビューはデータの集約(GROUP BY)を行っているのであらゆるDML文は実行できません。 しかし、このビューに対する以下のようなDMLが可能であれば、つじつまが合います。

1.このビューの部門名列(DNAME列)を更新すると、部門表(DEPT表)の部門名列(DNAME列)が更新される。
2.このビューの給与合計列(GSAL列)を更新すると、社員表(EMP表)の該当部門のすべての社員の給与列(SAL列)が同じ割合だけ更新される。
3.このビューの行をDELETEすると、社員表の該当部門の行と、部門表の該当部門の行がDELETEされる。

ではこのようなことを可能にするINSTEAD OF トリガーを実際に作成してみます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE TRIGGER TRIG_V_G_SAL
INSTEAD OF DELETE OR UPDATE
ON  V_GSAL
FOR EACH ROW
BEGIN
   IF DELETING THEN
      DELETE FROM EMP WHERE DEPTNO = :OLD.DEPTNO;
      DELETE FROM DEPT WHERE DEPTNO = :OLD.DEPTNO;
   ELSE
      IF   UPDATING( 'GSAL' THEN
           UPDATE  EMP
           SET    SAL = SAL * (:NEW.GSAL / :OLD.GSAL)
           WHERE  DEPTNO = :NEW.DEPTNO;
      END IF;
      IF   UPDATING( 'DNAME' THEN
           UPDATE DEPT
           SET    DNAME = :NEW.DNAME
           WHERE  DEPTNO = :NEW.DEPTNO;
      END IF;
      IF  UPDATING( 'DEPTNO' ) THEN
          RAISE_APPLICATION_ERROR(-20000, 'DEPTNO列は更新できません' );
      END IF;
   END IF;
END ;
/
 
トリガーが作成されました。

では解説します。

まず、上記の2行目の「INSTEAD OF」というキーワードが、このトリガーが「INSTEAD OF トリガー」であることを示しています。
普通の表のトリガーだと、この部分はBEFORE またはAFTERですが、INSTEAD OF トリガーは、BEFORE,AFTERの指定はせずに、その代りに、INSTEAD OF という指定をします。

また、INSTEAD OF の後ろはイベントになるDML文の種類を指定します。ここでは、DELETE文とUPDATE文を対象にします。
普通の表のトリガーの場合は、UPDATEの後ろに、「OF 列名,列名・・」と列リストを指定できますが、INSTEAD OF トリガーは列リストの指定ができないので、場合により、実行部でどの列のUPDATEなのかを判断する必要があります。(10行目、15行目,20行目の UPDATIN('列名') )

3行目の「ON V_GSAL」のV_GSALは表名ではなく、もちろん、ビュー名です。

4行目の「FOR EACH ROW」は行トリガーの指定ですが、INSTEAD OF トリガーは常に行トリガーなので、「FOR EACH ROW」を省略しても、行トリガーとして動作しますが、常に、「FOR EACH ROW」と記述するのがわかりやすいです。

5行目から最後の行まで、無名PL/SQLブロックです。
上記の例では「BEGIN」からはじまってますが、宣言部が必要であれば、「DECLARE」から記述します。

6行目から8行目まで、DELETEの場合は、社員表(EMP表),部門表(DEPT表)の順番に該当する部門の行をDELETEします。
※現実にはかなり危険な処理ですから実装するときはよく検討してください。

10行目から14行目まで、給与合計列(GSAL列)が更新される場合は社員表(EMP表)の該当する部門(WHERE DEPTNO = :NEW.DEPTNO)の社員に対して、給与列(SAL列)を同じ割合だけ更新しています( SET SAL = SAL * (:NEW.GSAL / :OLD.GSA) これにより例えば、給与合計列を2倍すると、該当する部門の社員の給与も2倍になります。

15行目から19行目で、部門名(DNAME列)をUPDATEすると、部門表(DEPT表)のDNAME列を同じ値に更新します。

20行目から22行目まで、DEPTNO列に対する更新は想定していないので、エラーにする処理です。
もしこの記述をしないと、DEPTNO列に対するUPDATEがあったとき、「n行が更新されました。」のように、正常終了となりますが、該当するロジックが記述されていないので、実際にはなにも処理されないことになります。

このように、UPDATEで起動するINSTEAD OF トリガーではトリガーロジックで対応していない列に対するUPDATEがあったときに、何も処理しないにもかかわらず、正常に処理した状態になるので注意してください。それが不都合であれば、この例のようにRAISE_APPLICATION_ERRORでエラーにするとよいでしょう。

ではビューに対するDMLを実行して結果を確認します。

1
2
3
4
5
6
UPDATE V_GSAL
SET  DNAME = 'accounting' ,
      GSAL = GSAL * 2
WHERE  DEPTNO = 10;
 
1行が更新されました。

ここでは、DEPTNO=10の行に対して、部門名列(DNAME列)を'accounting'に、給与合計列(GSAL列)を2倍に更新したわけです。

1
2
3
4
5
6
7
SELECT * FROM V_GSAL;
 
     DEPTNO DNAME                              GSAL
---------- ---------------------------- ----------
         10 accounting                        17500  ←2倍になっている、DNAMEも更新されている
         20 RESEARCH                          10875
         30 SALES                              9400

たしかに、そのように更新されていますね。
しかし、ビューはデータをもたないので、実際にはDEPT表、EMP表が更新されたわけです。

では、DEPT表を問い合わせてみましょう。

1
2
3
4
5
6
7
8
SELECT * FROM DEPT;
 
     DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
         10 accounting                   NEW YORK
         20 RESEARCH                     DALLAS
         30 SALES                        CHICAGO
         40 OPERATIONS                   BOSTON

たしかに、DEPNOT=10の行の部門名(DEPTNO列)が'accounting'に更新されています。
EMP表も問い合わせてみましょう。

1
2
3
4
5
6
SELECT SUM (SAL) FROM EMP
WHERE  DEPTNO = 10;
 
   SUM (SAL)
----------
      17500

たしかにEMP表のDEPNOT=10の給与合計はビューで更新した 17500になっていますね。

では、V_GSALビューのDEPTNO=10の行をDELETEします。

1
2
3
DELETE FROM V_GSAL WHERE DEPTNO = 10;
 
1行が削除されました。

メッセージは「1行が削除されました。」ですが、実際には、EMP表、DEPTE表からDEPTNO=10のすべての行がDELETEされたわけです。 誤った削除ならかなり危険な処理ですね。

最後に、DEPTNO列の更新はエラーになることを確認します。

1
2
3
4
5
6
7
SQL> UPDATE V_GSAL SET DEPTNO = 11 WHERE DEPTNO = 10;
UPDATE V_GSAL SET DEPTNO = 11 WHERE DEPTNO = 10
        *
行1でエラーが発生しました。:
ORA-20000: DEPTNO列は更新できません
ORA-06512: "SCOTT.TRIG_V_G_SAL" , 行17
ORA-04088: トリガー 'SCOTT.TRIG_V_G_SAL' の実行中にエラーが発生しました

きちんと、21行目で記述した、「RAISE_APPLICATION_ERROR(-20000,'DEPTNO列は更新できません');」のエラーとなっていますね。

では、いろいろとデータを更新しましたので、最後にROLLBACKでトランザクションを取り消します。

1
2
3
ROLLBACK ;
 
ロールバックが完了しました。

これでトリガーはまだ存在していますが、データは元に戻りました。

いかがですか?使いかたによっては便利そうですね。でも実装が少し複雑です。
特にUPDATEで起動するINSTEAD OF トリガーの場合は、該当しない列の更新操作の場合、実際には何も処理していないので注意してください。
ですからそのような場合は上記のデモのようにエラーにする処理をすべきです。

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

先頭へ戻る