こんにちは。インストラクターの蓑島です。
残暑は短く、もうすっかり秋ですね。すごしやすい季節ですから、夏バテ気味だった方も、規則正しい生活リズムで体調を整えて、さわやかにすごしましょう。
前回は、ログオントリガーについて解説しましたので、今回は、ログオフトリガーについて解説します。

ログオフトリガーは名前の通り、データーベースからログオフするときに起動するトリガーです。
いわばセッションの最後の処理ですね。

ログオントリガーと、ログオフトリガーはともに同じカテゴリーに属するトリガーと言えますが、以下の2点で大きな違いがあります。

１．ログオントリガーは、SYSユーザーでは起動しないが、ログオフトリガーは、SYSユーザーでも起動する。

２．ログオントリガーで、未処理例外(エラーのこと）が発生するとログオンができないが、ログオフトリガーで未処理例外が発生してもログオフ処理は可能である。

上の二つの違いは、前回を読んでいる方は、なんとなくわかるような気がしますよね？

まず、１．の部分ですが、ログオントリガーでSYSユーザーの場合に起動しない理由は、トリガーがエラーになることで、SYSユーザーでログオンが出来なくなる事態が発生しないように配慮されているからです。
しかしログオフトリガーの場合は、次の２．の説明にあるように、未処理例外でエラーとなっても、ログオフは可能であり、特別な配慮は不要なので、SYSユーザーでも起動する訳です。

また、ログオフトリガーで、未処理例外でエラーとなってもログオフが可能ということは、プログラム的なチェックによりログオフを防止する目的には使えないということを意味します。

一般にトリガーは、未処理例外などでエラーとなると、トリガー起動のきっかけとなったイベントのSQL文までエラーで取り消します。
そのため条件に反する時など、イベントのSQL文をエラーにして防止したい目的で使用できますが、ログオフトリガーは例外であり、その目的には使えません。

もちろんログオフトリガーがエラーとなれば、ログオフトリガー内で行ったトランザクションは取り消されますが、ログオフそのものは妨げられないわけです。
ログオンの場合はセキュリティ的な観点で不正なログオンはエラーにしたいニーズがありますが、ログオフの場合は、そういったニーズはない訳ですね。

ですから、ログオフトリガーの一般的な用途としては、プログラム的なチェックを行うことではなく、純粋にそのセッションを終了するにあたっての最後の処理を行うことである、と言えます。

では、具体的な事例を通して確認していきましょう。

SCOTT.EMPへのUPDATEを行トリガーを使ってパッケージの変数にカウントし、最後にログオフトリガーで、その行数をログ的な表に記録するというものです。
ログオフトリガーはセッションの最後に起動しますので、そのセッションで行った操作のサマリ的な情報を表に記録する用途などに使えるという趣旨です。
ログオフトリガーだけでなく、パッケージや行トリガーも使いますので、全体を順番に見ていきましょう。

SQL> SHOW USER
ユーザーは"SCOTT"です。

まず、SCOTTスキーマでPAC1というパッケージを作成しました。
このパッケージはCNT_EMP_UPDという変数を持っていますが、この変数をSCOTT.EMP表のUPDATEの行数のカウンターにする予定です。
パッケージの変数は特段の指定をしない限りはセッションの間ずっと保持されますので、セッション最後のログオフトリガーでその値を表に記録する訳です。

ところで、後ほど作成するログオフトリガーは、どのユーザがログオフしても起動するトリガーにしたいと考えています。
DDLトリガーやログオン、ログオフトリガーの場合、どのユーザでも起動するトリガー( ON DATABASE のトリガー)を作成する場合は、作成者に「ADMINISTER DATABASE TRIGGER」システム権限が必要なので、今回はSYSTEMユーザで作成します。
またそのトリガーの中で、SCOTT.PAC1パッケージの変数を参照する予定なので、SYSTEMユーザにそのパッケージを利用する権限が必要です。
そこで、SYSTEMユーザにその権限を与えます。
パッケージを利用するオブジェクト権限は常に「EXECUTE」権限です。たとえパッケージの変数を参照するだけでもEXECUTE権限です。

SYSTEMユーザにその権限を与えます。

これで、SYSTEMユーザでSCOTT.PAC1パッケージが利用可能となったので、後ほどSYSTEMユーザでログオフトリガーを作成します。 ではその前に、SCOTTユーザのセッションで、もう一つ別なトリガーを作ります。 すなわち、SCOTT.EMPのUPDATEの行数をカウントするトリガーです。

表の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"です。

では最後にSYSTEMユーザでログオフトリガーを作成します。この部分が今回のメインテーマです。

まず、ログオフトリガーの指定は、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表を更新してみます。

SCOTTユーザで接続しました。

SCOTT.EMP表を3行更新しました。

さらに1行更新しました。
これで、SCOTTユーザでSCOTT.EMP表を合計 4行更新しました。

EXITによりログオフされるので、ログオフトリガーにより、SCOTTユーザで合計4行UPDATEしたことがLOG_TABLE表に記録されたはずです。
次にHRユーザに SCOTT.EMPをUPDATEできるオブジェクト権限を与えます。

ではもう一度、SQL*Plusなどを起動して、SCOTTユーザでログインし、HRユーザに権限を与えます。 SQL> CONNECT SCOTT/tiger SCOTTユーザで接続しました。

これによりHRユーザはSCOTT.EMP表のUPDATEが可能です。 ではHRユーザで接続して、何行かSCOTT.EMPをUPDATEして、ログオフします。

これでHRユーザにより、SCOTT.EMP表が5行UPDATEされて、最後のEXIT処理によるログオフトリガーでその行数がLOG_TABLE表に記録されます。 では、最後にSYSTEMユーザで、LOG_TABLE表を問い合わせてみます。 SQL>

ご覧のように、SCOTTユーザと、HRユーザがログオフした日時と、EMP表を更新した行数として、4行、5行と記録されていますね。

ここで一つ気をつけていただきたいのは、SCOTT.EMPへのUPDATEをROLLBACKしても、パッケージ変数の値は元にもどらないので、ROLLBACKがあった場合は、LOG_TABLE表に記録される行数は正確ではなくなります。 もし、正確性が大事ならこの方法は使えません。
その場合、おそらくUPDATEによる行トリガーで、パッケージ変数ではなく、LOG的な表の列への足し込み処理(更新)をすることになるので、更新する1行1行について、追加のREDOデータ、UNDOデータを生成することになり、パフォーマンスに多少の影響がでると思います。しかし、今回の方法であれば、行トリガーが足し込んでいるのは、セッションメモリー内の変数なので、トランザクションとは無関係であり、負荷も少ないと言えます。一長一短なので要件を考慮して決める必要があります。

それでは今回のログオフトリガー、いかがでしたか？
ログオントリガーとともに、ログオン・ログオフの履歴を記録したり、また、今回のように、各セッションで行ったワークロードの量をDMLトリガーでパッケージ変数に記録し、最終的にログオフトリガーでその情報を表に反映する、といった使い方がログオフトリガーの用途としてあげられると思います。

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