Oracle トリガーイベント時にDDL操作情報を取得する

久々にOracle Databaseの記事を。
Oracle Databaseのトリガーを使っていて、トリガー発生の原因となったイベントに関する情報を取得したいということがあった。例えば、CREATE TABLE文などのDDL発行を起因するシステム・トリガー内で、作成しようとしたテーブル名を知りたい場合、など。トリガー内では以下のような特殊な関数を使用することで、トリガー内で対象オブジェクト名やスキーマなどの各種情報が取得できることが分かったので、結果をまとめておく。（トリガーは使うべきではないよ、という議論はここではおいておく。。）

特殊な関数たち

システム・トリガー内部で以下のような関数を使うと、それぞれの値が取得できる。代表的な使えそうなものを抜粋する。

ORA_SYSEVENT       : トリガー発生イベント
ORA_DICT_OBJ_OWNER : トリガー発生対象オブジェクトの所有者
ORA_DICT_OBJ_NAME  : トリガー発生対象オブジェクト名
ORA_DICT_OBJ_TYPE  : トリガー発生対象オブジェクトタイプ
ORA_LOGIN_USER     : トリガー発生DDL実行ユーザ

実際にシステム・トリガーを動かしてみる

まず、システム・トリガーを作成する。CREATE文に対応して、各種情報を標準出力するようなトリガーを例として作成した。

system_trigger

create or replace trigger system_trigger
after create on database
begin
 DBMS_OUTPUT.put_line('event =' || ORA_SYSEVENT);
 DBMS_OUTPUT.put_line('obj_owner =' || ORA_DICT_OBJ_OWNER);
 DBMS_OUTPUT.put_line('obj_name =' || ORA_DICT_OBJ_NAME);
 DBMS_OUTPUT.put_line('obj_type =' || ORA_DICT_OBJ_TYPE);
 DBMS_OUTPUT.put_line('login_user =' || ORA_LOGIN_USER);
end;

さっそくテーブルを作成してみる。ログインユーザとオブジェクトオーナを分けて取得できるようなので、scottスキーマにtesttblという名前のテーブルをCREATEしてみる。

scott.testtbl

create table scott.testtbl(id number)
event =CREATE
obj_owner =SCOTT
obj_name =TESTTBL
obj_type =TABLE
login_user =SYS
表が作成されました(171 msec.)

結果から、実行したDDL文の情報が取得できていることが分かる！

ところでDMLトリガーの場合は

通常のDMLトリガーの場合はどうなるのだろうか。作成時にスキーマもテーブルも指定するので毎回固定のものがとれるのだろうか？

先ほどのシステム・トリガーと同じロジックでscott.testtblに対するDMLトリガーを作成する。

create or replace trigger dml_trigger
after insert or update or delete
on scott.testtbl
begin
 DBMS_OUTPUT.put_line('event =' || ORA_SYSEVENT);
 DBMS_OUTPUT.put_line('obj_owner =' || ORA_DICT_OBJ_OWNER);
 DBMS_OUTPUT.put_line('obj_name =' || ORA_DICT_OBJ_NAME);
 DBMS_OUTPUT.put_line('obj_type =' || ORA_DICT_OBJ_TYPE);
 DBMS_OUTPUT.put_line('login_user =' || ORA_LOGIN_USER);
end;

トリガーをセットしたテーブルにINSERTしてみる。

insert into scott.testtbl(id) values (2)
event =
obj_owner =
obj_name =
obj_type =
login_user =SYS
1行挿入されました(94 msec.)

ORA_LOGIN_USERしか取得できていない！やはり取得できる情報はトリガーの種類ごとに違うよう。調査すればDMLトリガー用の情報、例えば対象データ行に関する情報とかもとれるのかもしれないが、ここでは未検証。

いったんここまで。以上。