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

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

第68回 「複合トリガー(その2)」(2013.08.05)

2013.08.05

こんにちは。インストラクターの蓑島です。
この原稿は8/2に執筆していますが、この日の東京はオホーツク海の冷たい空気が来ているそうで、珍しく涼しく、過ごしやすいです。

さて、前回は複合トリガーの構文までをご紹介しました。
今日は複合トリガーの応用例をご紹介しましょう。

はじめに、前回の簡単な復習をします。
複合トリガーではなく、普通のトリガー定義をつかっている場合、トリガー間で変数の共有をしようとすれば、パッケージ変数を利用することになります。
しかしその場合、変数の宣言はパッケージで、そして、その変数の利用はそれぞれのトリガーで、という形になり、コードがあちこちに分散してシステムが非常にわかりにくいものになってしまいます。

そんなとき、複合トリガーを使えば、変数宣言も、複数のトリガー定義も、一つの複合トリガーのソースコードにまとめることができますので、大変わかりやすくなる、ということでしたね。

では、今回は応用例です。
が、いきなり実践的な事例の前に、とにかくまず簡単な例で、複合トリガーを使ってみましょう。
その後、実践的な事例をご紹介します。
今回は少し長いので、「簡単な事例」、「実践的な事例」ということで2部構成にしました。
全体が長いようであれば、きりのよいところまで読んでいただき、第2部はお盆休みの間にでも読んでみてください。

★★★★★★★★★★★★★★★★★★★
第1部  簡単な事例
★★★★★★★★★★★★★★★★★★★

ではいつものTEST01表に簡単な複合トリガーを作ります。
共通の宣言セクションで変数を宣言し、BEFORE文トリガーセクションで、その変数に'HELLO'という文字列をセットする。
続くBEFOREの行トリガーで、その変数の値を画面表示してみましょう。実用性はないですが簡単にできます。

では、TEST01表を作ります。いつものように、A列、B列だけの簡単な表です。

1
2
3
4
5
6
CREATE TABLE TEST01
( A NUMBER,
  B VARCHAR2(10))
/
 
表が作成されました。

適当に2行ほどINSERTしてみましょう。

SQL> INSERT INTO TEST01 VALUES (10,'ABC');

1行が作成されました。

SQL> INSERT INTO TEST01 VALUES (20,'DEF');

1行が作成されました。

SQL> COMMIT;

コミットが完了しました。

TEST01表は以下のような状態ですね。

SQL> SELECT * FROM TEST01;

A  B
---------- ----------
10  ABC
20  DEF

SQL>

早速、複合トリガーを作ります。
前回ご紹介した複合トリガーの構文をコピーして、不要な部分は削除して、変数宣言や処理を記述します。
トリガー名は適当ですが、 TRIG01としましょう。記述した部分は小文字にしましたので、どこに手を加えたかすぐにわかりますね。

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
28
29
30
31
32
/*****************************************************************/
-- 複合トリガーの定義
/*****************************************************************/
CREATE OR REPLACE  TRIGGER  trig01
FOR  UPDATE ON test01
/*****************************************************************/
-- 共通の宣言のセクション
/*****************************************************************/
COMPOUND  TRIGGER
    v_message   varchar2(10);    -- 共通の変数宣言
/*****************************************************************/
-- BEFOREの文のセクション
/*****************************************************************/
BEFORE  STATEMENT IS ・・
BEGIN
    v_message := 'HELLO' ;
    dbms_output.put_line( 'BEFOREの文です。変数にHELLOとセットしました' );
END  BEFORE STATEMENT;
/*****************************************************************/
-- BEFOREの行のセクション
/*****************************************************************/
BEFORE  EACH ROW  IS
BEGIN
    dbms_output.put_line( 'BEFOREの行です。変数の値は' || v_message || 'です' );
END  BEFORE  EACH ROW;
/*****************************************************************/
-- 終わりのEND;
/*****************************************************************/
END  trig01;
/
 
トリガーが作成されました。

4行目でトリガー名(trig01)を指定し、
5行目で表名(test01)に対するUPDATEがイベントであることが記述されてます。

10行目の共通の宣言セクションで、変数(v_message)を宣言しています。

16-17行目はBEFOREの文のセクションなので、一番最初に実行されますが、変数(v_message)に'HELLO'という文字列をセットし、「BEFOREの文です。変数にHELLOとセットしました」というメッセージを表示します。

24行目は、BEFOREの行のセクションですが、変数(v_message)の値をメッセージ出力しているわけです。

では早速、TEST01表に対して、UPDATEしてみます。

まず、画面出力を有効にして、

SQL> SET SERVEROUTPUT ON

UPDATEします。以下のUPDATEは、全行(2行)更新します。

SQL> UPDATE TEST01 SET B = B || 'X';
BEFOREの文です。変数にHELLOとセットしました ←BEFOREの文のセクションで出力
BEFOREの行です。変数の値はHELLOです ←BEFOREの行のセクションで出力
BEFOREの行です。変数の値はHELLOです ←BEFOREの行のセクションで出力

2行が更新されました。
BEFOREの文のセクションで変数にセットし、その値がBEFOREの行のセクションで参照できていることがわかりますね。

念のため、データを確認します。UPDATEされたことがわかりますね。

SQL> SELECT * FROM TEST01;

A B
---------- ----------
10 ABCX
20 DEFX

★★★★★★★★★★★★★★★★★★★
第2部  実践的な事例
★★★★★★★★★★★★★★★★★★★

第2部では実践的な事例を一つご紹介します。
それは、「行トリガーで、自分自身の表を参照することはできない」という制限を回避する事例です。場合によっては大変便利です。

バックナンバー第40回「トリガー処理のルール(してはいけないこと)」で、行トリガーでは自分自身の表を参照することができないことを解説しました。
そのような記述でも、コンパイルは正常にできるのですが、実行時にエラーとなります。
そのような状況でも、複合トリガーを使えば、うまく対応できるのです。

例えば、社員表(SCOTTスキーマのEMP表など)の給与列(SAL)を更新(UPDATE)する際に、その更新額(:NEW.SAL)が、社員表の最低給与(MIN(SAL))と、最大給与(MIN(MAX))の範囲内であることをチェックして、範囲外であればエラーにする複合トリガーではない通常のトリガーを作成します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE TRIGGER  trig02
BEFORE  UPDATE OF SAL ON SCOTT.EMP
FOR EACH ROW
DECLARE
    MIN_SAL   NUMBER;   -- 最低給与取得用の変数
    MAX_SAL   NUMBER;   -- 最高給与取得用の変数
BEGIN
    SELECT MIN (SAL), MAX (SAL) INTO MIN_SAL, MAX_SAL FROM SCOTT.EMP;
    IF :NEW.SAL NOT BETWEEN  MIN_SAL AND MAX_SAL  THEN
       RAISE_APPLICATION_ERROR(-20000, '給与範囲エラー' );
    END IF;
END ;
/
 
トリガーが作成されました。

上記のように、コンパイルはOKです。しかし、下記のように、実行時にエラーです。

SQL> UPDATE SCOTT.EMP SET SAL = SAL * 1.1 WHERE EMPNO = 7934;
UPDATE SCOTT.EMP SET SAL = SAL * 1.1 WHERE EMPNO = 7934
*
行1でエラーが発生しました。:
ORA-04091: 表SCOTT.EMPは変更しています。トリガー/関数は見ることができません
ORA-06512: "SCOTT.TRIG02", 行5
ORA-04088: トリガー'SCOTT.TRIG02'の実行中にエラーが発生しました

理由は、SCOTT.EMP表の行トリガーが、SCOTT.EMPを参照しているからです。
(上記トリガー定義の8行目のSELECT文)行トリガーでは、自分自身の表を参照できません。

しかし、文トリガーの場合は、自分自身の表を参照できます。
まず、BEFOREの文トリガーで、SCOTT.EMP表の最低給与(MIN(SAL)),最高給与(MAX(SAL))を変数に取得し、次に行トリガーでその変数を参照するように修正すればいいわけです。
それは、パッケージ変数と普通のトリガー定義でも可能ですが、複合トリガーを使えば、一つのソースコードにすべての記述が可能なので、より分かりやすくなるわけです。

では早速、そのような形にこのトリガーを書き換えます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE OR REPLACE TRIGGER  trig02
FOR  UPDATE OF SAL ON SCOTT.EMP
COMPOUND  TRIGGER
-- ●共通の宣言部
    MIN_SAL   NUMBER;   -- 最低給与取得用の変数
    MAX_SAL   NUMBER;   -- 最高給与取得用の変数
-- ●BEFOREの文のセクション
BEFORE STATEMENT IS
BEGIN
    SELECT MIN (SAL), MAX (SAL) INTO MIN_SAL, MAX_SAL FROM SCOTT.EMP;
END BEFORE STATEMENT;
-- ●BEFOREの行のセクション
BEFORE EACH ROW IS
BEGIN
    IF :NEW.SAL NOT BETWEEN  MIN_SAL AND MAX_SAL  THEN
       RAISE_APPLICATION_ERROR(-20000, '給与範囲エラー' );
    END IF;
END BEFORE EACH ROW;
END trig02;  -- ●トリガーの終わり
/
 
トリガーが作成されました。

では、事前に現在のSCOTT.EMP表の最低、最高額を確認しましょう。

SQL> SELECT MIN(SAL), MAX(SAL) FROM SCOTT.EMP;
MIN(SAL) MAX(SAL)
---------- ----------
800 5000

給与の最低は800であり、最高は5000ですね。

では、MILLERさん(社員番号7934)の給与を最低・最高の範囲内の4000に更新します。

SQL> UPDATE SCOTT.EMP SET SAL = 4000 WHERE EMPNO = 7934;

1行が更新されました。

範囲内なので更新できましたね。では、最低給与800を下回る額の750に更新します。

SQL> UPDATE SCOTT.EMP SET SAL = 750 WHERE EMPNO = 7934;
UPDATE SCOTT.EMP SET SAL = 750 WHERE EMPNO = 7934
*
行1でエラーが発生しました。:
ORA-20000: 給与範囲エラー
ORA-06512: "SCOTT.TRIG02", 行14
ORA-04088: トリガー'SCOTT.TRIG02'の実行中にエラーが発生しました

予定通り、「RAISE_APPLICATION_ERROR(-20000,'給与範囲エラー'); ※トリガー定義の16行目」が実行されて、「ORA-20000: 給与範囲エラー」のエラーになりましたね。複合トリガーのチェックが働いたわけです。

エラーになりましたが、このUPDATE文だけが失敗したことになるので、以前の4000に更新したUPDATE文はまだ取り消しされていません。

SQL> SELECT ENAME, SAL FROM SCOTT.EMP WHERE EMPNO = 7934;

ENAME SAL
---------- ----------
MILLER 4000

給与額は4000のままですね。では、MILLERさんに特別ボーナスで、この状態を確定(COMMIT)してしまいましょう(笑)

SQL> COMMIT;

コミットが完了しました。

いかがですか?
行トリガーでは自分の表を参照できないので、BEFOREの文トリガーで参照して変数にセットし、その変数をBEFOREの行トリガーで利用するわけです。
複合トリガーを使えば、変数宣言も含めてすべての記述が一つのソースコードの中に可能なので大変わかりやすくなるということですね。

では今回はここまでにいたします。
次週の 8/12(月)はお盆休みなので、次回は8/19(月)の予定です。ご期待ください。

先頭へ戻る