システム・トリガーを試す（３）（Oracle Database 19c)

　ユーザーが発行したCREATE文やDROP文の実行時にPL/SQLプログラムを実行したい場合にはDDLトリガーを作成します。利用用途としては、特定のDDLを禁止したり、ログに残すことで監査を強化する等が考えられます。

DDLトリガー

DDLトリガーはCREATE TRIGGER文に対象となるDDL文（CREATE / DROP / AUDIT / GRANT / TRUNCATE / その他）を指定するか、どのDDLでも実行されるようにする場合は「BEFORE DDL」や「AFTER DDL」を指定します。下記の例では以下の条件でトリガーが発行されます。

項目	説明	設定値
名前	トリガーの名前	DDL_TRIGGER1
発行時点	DDL実行前	BEFORE
ユーザー	SCOTTのみ	SCOTT.SCHEMA
DDLの種類	すべてのDDL	DDL

CREATE OR REPLACE TRIGGER ddl_trigger1 BEFORE DDL ON SCOTT.SCHEMA
DECLARE
    evt VARCHAR2(128);
    own VARCHAR2(128);
    nam VARCHAR2(128);
    typ VARCHAR2(128);
    err NUMBER;
BEGIN
    evt := ora_sysevent;
    own := ora_dict_obj_owner;
    nam := ora_dict_obj_name;
    typ := ora_dict_obj_type;
    err := ora_server_error(1);

    IF (evt = 'DROP') THEN
        RAISE_APPLICATION_ERROR(-20000, 'Cannot execute DROP statement.');
    END IF;

    DBMS_SYSTEM.KSDWRT(2, 'Before DDL: Event: ' || evt || ', Owner: ' || own || ', Name: ' || nam || ', Type: ' || typ || ', Error: ' || to_char(err));
END;
/

例ではトリガーをSYSユーザーで定義しています。SCOTTユーザーがDROP文を実行するとエラー「ORA-20000: Cannot execute DROP statement.」が発生し、オブジェクトの削除ができない仕様になっています。それ以外のDDLが実行された場合はアラートログに情報を出力します。
　BEFOREトリガーのため、オブジェクトの存在やセキュリティ・チェックの前に実行されることになります。ON SCOTT.SCHEMAが指定されていますので、SCOTTユーザーとして接続された場合に限り実行されます。

$ sqlplus scott/tiger

SQL*Plus: Release 19.0.0.0.0 - Production on 水 4月 14 10:09:55 2021
Version 19.8.0.0.0

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
に接続されました。
SQL>
SQL> CREATE TABLE drop1(c1 NUMBER, c2 VARCHAR2(10));

表が作成されました。

SQL> DROP TABLE drop1;
DROP TABLE drop1
*
行1でエラーが発生しました。:
ORA-04088: トリガー'SYS.DDL_TRIGGER1'の実行中にエラーが発生しました ORA-00604:
再帰SQLレベル1でエラーが発生しました。 ORA-20000: Cannot execute DROP statement
ORA-06512: 行15

トリガー・ファンクション

前述のトリガーでは情報を取得するためにいくつかの関数や変数が利用されていました。これらはイベント属性ファンクション と呼ばれ、トリガー内でトリガー発行の元になったオブジェクトやユーザーの情報を入手できます。主なファンクションは以下の通りです。

属性	データ型	説明	例
ora_sysevent	VARCHAR2(20)	イベント名	CREATE, DROP等
ora_dict_obj_owner	VARCHAR2(128)	イベント対象オブジェクトの所有者	SCOTT
ora_dict_obj_name	VARCHAR2(128)	イベント対象オブジェクト名	EMP
ora_server_error	NUMBER	エラー・スタック上の指定した位置にあるエラー・コード
ora_server_error_depth	PLS_INTEGER	エラー・スタック上のエラー・メッセージ数
ora_login_user	VARCHAR2(128)	ログイン・ユーザー名	SCOTT
ora_instance_num	NUMBER	インスタンス番号
ora_client_ip_address	VARCHAR2	LOGONイベントでのクライアントのIPアドレス
ora_sql_txt(OUT ora_name_list_t)	PLS_INTEGER	トリガーを起動する文のSQLテキスト

すべてのトリガー・ファンクションが常に利用できるわけではありません。例えばBEFORE DDLトリガーの場合、構文エラーのチェック後、セキュリティチェック前に発行されるため、ora_server_errorにはエラーが格納されません。