第72回 「ログオフトリガー ~パッケージ変数、行トリガーと組み合わせてセッションのUPDATE量を記録するなど~」
2013.09.30
こんにちは。インストラクターの蓑島です。
残暑は短く、もうすっかり秋ですね。すごしやすい季節ですから、夏バテ気味だった方も、規則正しい生活リズムで体調を整えて、さわやかにすごしましょう。
前回は、ログオントリガーについて解説しましたので、今回は、ログオフトリガーについて解説します。
ログオフトリガーは名前の通り、データーベースからログオフするときに起動するトリガーです。
いわばセッションの最後の処理ですね。
ログオントリガーと、ログオフトリガーはともに同じカテゴリーに属するトリガーと言えますが、以下の2点で大きな違いがあります。
1.ログオントリガーは、SYSユーザーでは起動しないが、ログオフトリガーは、SYSユーザーでも起動する。
2.ログオントリガーで、未処理例外(エラーのこと)が発生するとログオンができないが、ログオフトリガーで未処理例外が発生してもログオフ処理は可能である。
上の二つの違いは、前回を読んでいる方は、なんとなくわかるような気がしますよね?
まず、1.の部分ですが、ログオントリガーでSYSユーザーの場合に起動しない理由は、トリガーがエラーになることで、SYSユーザーでログオンが出来なくなる事態が発生しないように配慮されているからです。
しかしログオフトリガーの場合は、次の2.の説明にあるように、未処理例外でエラーとなっても、ログオフは可能であり、特別な配慮は不要なので、SYSユーザーでも起動する訳です。
また、ログオフトリガーで、未処理例外でエラーとなってもログオフが可能ということは、プログラム的なチェックによりログオフを防止する目的には使えないということを意味します。
一般にトリガーは、未処理例外などでエラーとなると、トリガー起動のきっかけとなったイベントのSQL文までエラーで取り消します。
そのため条件に反する時など、イベントのSQL文をエラーにして防止したい目的で使用できますが、ログオフトリガーは例外であり、その目的には使えません。
もちろんログオフトリガーがエラーとなれば、ログオフトリガー内で行ったトランザクションは取り消されますが、ログオフそのものは妨げられないわけです。
ログオンの場合はセキュリティ的な観点で不正なログオンはエラーにしたいニーズがありますが、ログオフの場合は、そういったニーズはない訳ですね。
ですから、ログオフトリガーの一般的な用途としては、プログラム的なチェックを行うことではなく、純粋にそのセッションを終了するにあたっての最後の処理を行うことである、と言えます。
では、具体的な事例を通して確認していきましょう。
SCOTT.EMPへのUPDATEを行トリガーを使ってパッケージの変数にカウントし、最後にログオフトリガーで、その行数をログ的な表に記録するというものです。
ログオフトリガーはセッションの最後に起動しますので、そのセッションで行った操作のサマリ的な情報を表に記録する用途などに使えるという趣旨です。
ログオフトリガーだけでなく、パッケージや行トリガーも使いますので、全体を順番に見ていきましょう。
SQL> SHOW USER
ユーザーは"SCOTT"です。
1
2
3
4
5
6
7
|
CREATE OR REPLACE PACKAGE SCOTT.PAC1 IS CNT_EMP_UPD NUMBER; END ; / パッケージが作成されました。 |
まず、SCOTTスキーマでPAC1というパッケージを作成しました。
このパッケージはCNT_EMP_UPDという変数を持っていますが、この変数をSCOTT.EMP表のUPDATEの行数のカウンターにする予定です。
パッケージの変数は特段の指定をしない限りはセッションの間ずっと保持されますので、セッション最後のログオフトリガーでその値を表に記録する訳です。
ところで、後ほど作成するログオフトリガーは、どのユーザがログオフしても起動するトリガーにしたいと考えています。
DDLトリガーやログオン、ログオフトリガーの場合、どのユーザでも起動するトリガー( ON DATABASE のトリガー)を作成する場合は、作成者に「ADMINISTER DATABASE TRIGGER」システム権限が必要なので、今回はSYSTEMユーザで作成します。
またそのトリガーの中で、SCOTT.PAC1パッケージの変数を参照する予定なので、SYSTEMユーザにそのパッケージを利用する権限が必要です。
そこで、SYSTEMユーザにその権限を与えます。
パッケージを利用するオブジェクト権限は常に「EXECUTE」権限です。たとえパッケージの変数を参照するだけでもEXECUTE権限です。
SYSTEMユーザにその権限を与えます。
1
2
3
|
GRANT EXECUTE ON SCOTT.PAC1 TO SYSTEM; 権限付与が成功しました。 |
これで、SYSTEMユーザでSCOTT.PAC1パッケージが利用可能となったので、後ほどSYSTEMユーザでログオフトリガーを作成します。 ではその前に、SCOTTユーザのセッションで、もう一つ別なトリガーを作ります。 すなわち、SCOTT.EMPのUPDATEの行数をカウントするトリガーです。
1
2
3
4
5
6
7
8
9
10
|
CREATE OR REPLACE TRIGGER TRIG_CNT_EMP_UPD AFTER UPDATE ON EMP FOR EACH ROW BEGIN PAC1.CNT_EMP_UPD := NVL(PAC1.CNT_EMP_UPD,0) + 1; END ; / トリガーが作成されました。 |
表のDMLトリガーは、AFTER, BEFOREの区別と、文トリガー、行トリガーの区別がありますが、ここで考えていることはUPDATEの行数をカウンターに足し込むことです。
その場合、文トリガーでは無理と言えます。
文トリガーはSQL文ごとに1回しか起動しませんし、該当のUPDATE文が何行を更新するものか判断できないからです。
0行の場合ですら文トリガーは起動します。
しかし、行トリガーであれば、実際にUPDATEされる1行ごとに起動するので、行トリガーでカウンターを1ずつ足し込めば、結果的にUPDATEの行数分だけカウントアップできます。
ということで、行トリガーとしています。
(4行目の FOR EACH ROW が行トリガーの指定) また、BEFOREかAFTERについてはこの場合どちらでもかまいません。
5行目で、NVL関数を使っているのは、パッケージ変数PAC1.CNT_EMP_UPDが初期値を定義していないので、初回に参照されるとき、NULLだからですね。
NULLと演算すると必ず結果がNULLになるので、NVL関数でNULLを0に置き換えます。
トリガーは、定義者の権限(上のトリガーの場合はSCOTTユーザの権限)で起動しますので、どのユーザがSCOTT.EMP表を更新するかに関係なく、上記のトリガーで権限の問題は発生しません。
では最後にSYSTEMユーザで、SCOTT.PAC1パッケージを参照するログオフトリガーを作成しますが、その前にログ的な表を作成します。
この表では、ユーザ名と、ログオフした日時、そのセッションのSCOTT.EMP表へのUPDATE行数を記録するものです。
SQL> CONNECT SYSTEM/oracle
接続されました。
SQL> SHOW USER
ユーザーは"SYSTEM"です。
1
2
3
4
5
6
7
|
CREATE TABLE LOG_TABLE ( USER_NAME VARCHAR2(100), -- ユーザ名 LOG_OFF_TIME DATE , -- ログオフした日時 CNT_EMP_UPD NUMBER) -- SCOTT.EMPのUPDATE行数 / 表が作成されました。 |
では最後にSYSTEMユーザでログオフトリガーを作成します。この部分が今回のメインテーマです。
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE OR REPLACE TRIGGER SYSTEM.LOGOOFF_EMP_UPD_SAMARY BEFORE LOGOFF ON DATABASE BEGIN IF SCOTT.PAC1.CNT_EMP_UPD IS NOT NULL THEN INSERT INTO SYSTEM.LOG_TABLE(USER_NAME, LOG_OFF_TIME, CNT_EMP_UPD) VALUES ( USER ,SYSDATE,SCOTT.PAC1.CNT_EMP_UPD); END IF; END ; / トリガーが作成されました。 |
まず、ログオフトリガーの指定は、2行目の「BEFORE LOGOFF」です。
ログオントリガーであれば、「AFTER LOGON」ですが、今回はログオフトリガーなので、「BEFORE LOGOFF」です。
また3行目が「ON DATABASE」なので、どのユーザがログオフしても起動します。
ちなみに、3行目を「ON SCHEMA」にするとスキーマユーザであるSYSTEMユーザがログオフする場合にのみ起動するトリガーとなる訳ですね。
4行目からの「BEGIN」以下はトリガーの処理を記述する部分であり、トリガー本体といいます。
トリガー本体は無名PL/SQLブロックなので、もし変数宣言などが必要であれば、「BEGIN」ではなく「DECLARE」から記述します。
トリガー本体で、パッケージ変数(PAC1.CNT_EMP_UPD)がNULLでないならば、その変数(UPDATEの行数)の値や、ユーザ名(USER)、ログオフ時の日時(SYSDATE)をLOG_TABLE表にINSERTします。
もしパッケージ変数がNULLならそのセッションではSCOTT.EMP表をUPDATEしていなかったことになるので、なにも処理をしません。
では、必要なトリガーやパッケージを作成したので、早速、検証してみましょう。
まず、SCOTTユーザでEMP表を更新してみます。
1
2
|
CONNECT SCOTT/tiger 接続されました。 |
SCOTTユーザで接続しました。
1
2
3
|
UPDATE EMP SET SAL = SAL + 1 WHERE DEPTNO = 10; 3行が更新されました。 |
SCOTT.EMP表を3行更新しました。
1
2
3
|
UPDATE EMP SET SAL = SAL * 1.1 WHERE EMPNO = 7934; 1行が更新されました。 |
さらに1行更新しました。
これで、SCOTTユーザでSCOTT.EMP表を合計 4行更新しました。
1
2
3
4
5
|
COMMIT ; コミットが完了しました。 EXIT; --ログオフトリガー起動 |
EXITによりログオフされるので、ログオフトリガーにより、SCOTTユーザで合計4行UPDATEしたことがLOG_TABLE表に記録されたはずです。
次にHRユーザに SCOTT.EMPをUPDATEできるオブジェクト権限を与えます。
ではもう一度、SQL*Plusなどを起動して、SCOTTユーザでログインし、HRユーザに権限を与えます。 SQL> CONNECT SCOTT/tiger SCOTTユーザで接続しました。
1
2
3
|
GRANT UPDATE ON SCOTT.EMP TO HR; 権限付与が成功しました。 |
これによりHRユーザはSCOTT.EMP表のUPDATEが可能です。 ではHRユーザで接続して、何行かSCOTT.EMPをUPDATEして、ログオフします。
1
2
3
4
5
6
7
8
9
10
11
12
|
CONNECT HR/hr 接続されました。 UPDATE SCOTT.EMP SET ENAME = LOWER (ENAME) WHERE DEPTNO = 20; 5行が更新されました。 COMMIT ; コミットが完了しました。 EXIT; --ログオフトリガー起動 |
これでHRユーザにより、SCOTT.EMP表が5行UPDATEされて、最後のEXIT処理によるログオフトリガーでその行数がLOG_TABLE表に記録されます。 では、最後にSYSTEMユーザで、LOG_TABLE表を問い合わせてみます。 SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
CONNECT SYSTEM/oracle 接続されました。 ALTER SESSION SET NLS_DATE_FORMAT= 'YY/MM/DD HH24:MI:SS' ; セッションが変更されました。 COL USER_NAME FORMAT A20 SELECT * FROM LOG_TABLE; USER_NAME LOG_OFF_TIME CNT_EMP_UPD -------------------- ----------------- ----------- SCOTT 13/09/29 21:47:56 4 HR 13/09/29 21:48:35 5 |
ご覧のように、SCOTTユーザと、HRユーザがログオフした日時と、EMP表を更新した行数として、4行、5行と記録されていますね。
ここで一つ気をつけていただきたいのは、SCOTT.EMPへのUPDATEをROLLBACKしても、パッケージ変数の値は元にもどらないので、ROLLBACKがあった場合は、LOG_TABLE表に記録される行数は正確ではなくなります。 もし、正確性が大事ならこの方法は使えません。
その場合、おそらくUPDATEによる行トリガーで、パッケージ変数ではなく、LOG的な表の列への足し込み処理(更新)をすることになるので、更新する1行1行について、追加のREDOデータ、UNDOデータを生成することになり、パフォーマンスに多少の影響がでると思います。しかし、今回の方法であれば、行トリガーが足し込んでいるのは、セッションメモリー内の変数なので、トランザクションとは無関係であり、負荷も少ないと言えます。一長一短なので要件を考慮して決める必要があります。
それでは今回のログオフトリガー、いかがでしたか?
ログオントリガーとともに、ログオン・ログオフの履歴を記録したり、また、今回のように、各セッションで行ったワークロードの量をDMLトリガーでパッケージ変数に記録し、最終的にログオフトリガーでその情報を表に反映する、といった使い方がログオフトリガーの用途としてあげられると思います。
それでは今回はここまでです。また、次回、ご期待ください。