表の操作ログをトリガーで取得したい、と相談を受けました。ちょっと方法について考えてみました。
準備
SQLワークショップのユーティリティのサンプル・データセットからEMP/DEPTをインストールします。表EMPを操作するアプリケーションを作成することにより、動作の確認を行います。
SQLワークショップのユーティリティのクイックSQLより表EMPの操作ログを保存する表EMP_LOGを作成します。クイックSQLの定義は以下になります。
# semantics: default
emp_log
optime date /default sysdate
op vc8
oplog vc80
empno num
ename vc50
job vc50
mgr num
hiredate date
sal num
comm num
deptno num
SQLの生成、SQLスクリプトを保存、レビューおよび実行を順次実行して、表EMP_LOGを作成します。アプリケーションの作成は行いません。
テストに使用するアプリケーションを作成します。アプリケーション作成ウィザードを起動し、空のアプリケーションを作成します。名前は表EMPの操作とします。アプリケーションの作成を実行します。
フォームを選択します。
ページ名は任意です。ここではレポート・ページ名をレポートEMP、フォーム・ページ名をフォームEMPとしています。フォーム・ページ・モードはモーダル・ダイアログにします。フォームのページに作成されるページ・アイテムの名前にはページ番号が含まれるので、フォーム・ページ番号は3にしてください。次へ進みます。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。次へ進みます。
データ・ソースの表/ビューの名前にEMP(表)を指定します。次へ進みます。
フォームが扱う主キー型として、データベースで管理(ROWID)を選択し、作成をクリックします。
以上で表EMPの対話モード・レポートと編集フォームのページが作成されました。
同様の手順で対話グリッドのページを作成します。ページの作成で編集可能対話グリッドを選択します。ページ名をグリッドEMPとし、レポート・ソースの表/ビューの名前にEMP(表)、主キー型としてROWIDを選択します。作成をクリックします。
これで対話グリッドのページも作成されました。
これから、作成したアプリケーションを使った表EMPの操作を、ログとして記録する実装を行なっていきます。
フォームの操作ログの記録
Oracle APEXのアプリケーションにプロセスを追加して、操作のログを取得します。
フォームのページ（ページ番号３番）をページ・デザイナにて開きます。右ペインにプロセス・ビューを開き、プロセスの作成を実行します。作成するプロセスのコードは以下になります。
declare
l_msg varchar2(80);
begin
l_msg := :APP_USER || ':' || :APP_SESSION;
insert into emp_log(
op, oplog, empno
, ename, job, mgr, hiredate, sal, comm, deptno
)
values
(
:APEX$ROW_STATUS, l_msg, :P3_EMPNO
, :P3_ENAME, :P3_JOB, :P3_MGR, :P3_HIREDATE, :P3_SAL, :P3_COMM, :P3_DEPTNO
);
end;
プロセスのタイプとしてフォーム - 行の自動処理(DML)を選択します。フォーム・リージョンはフォームEMPです。設定のターゲット・タイプとしてPL/SQL Codeを選択し、上記のコードを記載します。実際に編集している表の操作ではなく、別表に書き込むだけなので、（対象が元表である）失われた更新の防止はOFF、行のロックはNoとします。
これでフォームを使った表EMPのINSERT、UPDATE、DELETEの操作が表EMP_LOGに記載されます。
テスト用アプリケーションを実行し表EMPの操作を行なった後、表EMP_LOGを確認してみます。SQLコマンドから以下のSQLを実行します。
select * from emp_log order by optime desc;
対話グリッドの操作ログの記録
対話グリッドのページ（ページ番号４番）をページ・デザイナにて開きます。右ペインにプロセス・ビューを開き、プロセスの作成を実行します。作成するプロセスのコードは以下になります。ほとんどフォームと同じです。
declare
l_msg varchar2(80);
begin
l_msg := :APP_USER || ':' || :APP_SESSION;
insert into emp_log(
op, oplog, empno
, ename, job, mgr, hiredate, sal, comm, deptno
)
values
(
:APEX$ROW_STATUS, l_msg, :EMPNO
, :ENAME, :JOB, :MGR, :HIREDATE, :SAL, :COMM, :DEPTNO
);
end;
プロセスのプロパティの設定についても、フォームのときと同じです。
これで、対話グリッドの操作ログを取得するプロセスも作成できました。
テスト用アプリケーションから表EMPを操作し、その操作ログを確認します。
トリガーによる記録
APEXのアプリケーションではなく、データベース・トリガーを使って操作ログを取得してみます。
INSERTトリガーは以下になります。
create or replace trigger tgr_emp_ins
before insert on emp
for each row
declare
l_msg varchar2(80);
begin
l_msg := sys_context('APEX$SESSION','APP_USER') || ':' || sys_context('APEX$SESSION','APP_SESSION');
insert into emp_log(
op, oplog, empno
, ename, job, mgr, hiredate, sal, comm, deptno
)
values
(
'INSERT', l_msg, :new.empno
, :new.ename, :new.job, :new.mgr, :new.hiredate, :new.sal, :new.comm, :new.deptno
);
end;
UPDATEトリガーです。
create or replace trigger tgr_emp_upd
before update on emp
for each row
declare
l_msg varchar2(80);
begin
l_msg := sys_context('APEX$SESSION','APP_USER') || ':' || sys_context('APEX$SESSION','APP_SESSION');
insert into emp_log(
op, oplog, empno
, ename, job, mgr, hiredate, sal, comm, deptno
)
values
(
'UPDATE', l_msg, :new.empno
, :new.ename, :new.job, :new.mgr, :new.hiredate, :new.sal, :new.comm, :new.deptno
);
end;
DELETEトリガーです。
create or replace trigger tgr_emp_del
before delete on emp
for each row
declare
l_msg varchar2(80);
begin
l_msg := sys_context('APEX$SESSION','APP_USER') || ':' || sys_context('APEX$SESSION','APP_SESSION');
insert into emp_log(
op, oplog, empno
, ename, job, mgr, hiredate, sal, comm, deptno
)
values
(
'DELETE', l_msg, :old.empno
, :old.ename, :old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm, :old.deptno
);
end;
上記のトリガーを設定した後、テスト用アプリケーションから表EMPを操作してみます。
その後、表EMP_LOGの内容を確認します。トリガーのログでは操作をINSERT, UPDATE, DELETEとして記載しています。Oracle APEXのプロセスではC, U, Dとしています。１つの操作について、それぞれのログが記載されていることが確認できます。
統合監査による記録
プロセスやトリガーを設定せずに、統合監査のポリシーを定義して表EMPで行われた操作を記録することができます。
ユーザーADMINにてデータベース・アクションに接続し、以下のSQLにて統合監査ポリシーapex_emp_opの作成と有効化を行います。
create audit policy apex_emp_op
actions
all on apexdev.emp
when '1=1'
evaluate per statement;
audit policy apex_emp_op;
これで表EMPへの操作が記録されます。APEXのプロセスやトリガーの実装とは異なり、SELECT文の実行も監査証跡の取得対象にすることが可能です。
テスト用のアプリケーションを使って表EMPの操作を行なった後、ビューUNIFIED_AUDIT_TRAILを検索し、監査証跡を確認します。以下のSELECT文を実行します。
select
action_name, sql_text, sql_binds, current_user, client_identifier
from unified_audit_trail
where 1=1
and object_schema = 'APEXDEV'
and object_name = 'EMP'
and unified_audit_policies = 'APEX_EMP_OP'
order by event_timestamp desc;
表EMPの監査証跡が取得されていることが確認できます。
自律トランザクションについて
Oracle APEXのプロセスまたはトリガーによる操作ログの取得では、エラーが発生することにより操作のログが取得されない場合が起こり得ます。ログの書き込みを自律トランザクション (新しくトランザクションを開始し、ログを記載した時点で終了する) にて実行することにより、それぞれの障害を分離することができます。
Oracle APEXのプロセスとしての操作ログを取得する場合は、以下のようなログを表に書き込むプロシージャを作成し、そのプロシージャが自律トランザクションで動作するように記述します。APEXのページ・アイテムはデータを文字列として扱っていることを考慮して、引数の型をすべてVARCHAR2にしています。
create or replace procedure log_emp_op(
p_row_status in varchar2
, p_app_user in varchar2
, p_app_session in varchar2
, p_empno in varchar2
, p_ename in varchar2
, p_job in varchar2
, p_mgr in varchar2
, p_hiredate in varchar2
, p_sal in varchar2
, p_comm in varchar2
, p_deptno in varchar2
)
is
l_msg varchar2(80);
pragma autonomous_transaction;
begin
l_msg := p_app_user || ':' || p_app_session;
insert into emp_log(
op, oplog, empno
, ename, job, mgr, hiredate, sal, comm, deptno
)
values
(
p_row_status, l_msg, p_empno
, p_ename, p_job, p_mgr, p_hiredate, p_sal, p_comm, p_deptno
);
commit;
end log_emp_op;
操作ログを取得するプロセスのソースは以下に置き換わります。
log_emp_op(
p_row_status => :APEX$ROW_STATUS
, p_app_user => :APP_USER
, p_app_session => :APP_SESSION
, p_empno => :P3_EMPNO
, p_ename => :P3_ENAME
, p_job => :P3_JOB
, p_mgr => :P3_MGR
, p_hiredate => :P3_HIREDATE
, p_sal => :P3_SAL
, p_comm => :P3_COMM
, p_deptno => :P3_DEPTNO
);
対話グリッドのプロセスのソースは以下になります。
log_emp_op(
p_row_status => :APEX$ROW_STATUS
, p_app_user => :APP_USER
, p_app_session => :APP_SESSION
, p_empno => :EMPNO
, p_ename => :ENAME
, p_job => :JOB
, p_mgr => :MGR
, p_hiredate => :HIREDATE
, p_sal => :SAL
, p_comm => :COMM
, p_deptno => :DEPTNO
);
トリガーを自律トランザクションにするには、pragma autonomous_transactionの指定とcommit文を追加します。INSERTトリガーの例です。他のトリガーも同様に変更します。
create or replace trigger tgr_emp_ins
before insert on emp
for each row
declare
l_msg varchar2(80);
pragma autonomous_transaction;
begin
l_msg := sys_context('APEX$SESSION','APP_USER') || ':' || sys_context('APEX$SESSION','APP_SESSION');
insert into emp_log(
op, oplog, empno
, ename, job, mgr, hiredate, sal, comm, deptno
)
values
(
'INSERT', l_msg, :new.empno
, :new.ename, :new.job, :new.mgr, :new.hiredate, :new.sal, :new.comm, :new.deptno
);
commit;
end;
今回作成したアプリケーションのエクスポートは以下に置きました。トリガーのソースもサポート・オブジェクトとして含んでいます。
https://github.com/ujnak/apexapps/blob/master/exports/log-emp-op.sql
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
