Oracleトリガー活用術②

DDL操作の検出とエラーログの自動記録

前回の記事では、Oracleユーザー USER_A がスキーマ内で利用できるDMLトリガーとBEFORE INSERTトリガーを紹介しました。
今回はその続編として、USER_A が作成可能なスキーマレベルの以下の2つのトリガーを紹介します：

• AFTER DDL ON SCHEMA を使ったDDL操作監査
• AFTER SERVERERROR ON SCHEMA を使ったエラーログの記録

---

🎯この記事の目的

• ユーザー自身のDDL操作（CREATE/DROPなど）を検出してログに記録する
• SQLエラー（ORA-エラー）を検出してエラーテーブルに自動記録する
• すべて USER_A スキーマ内で構築・確認できる内容に限定

---

🧰事前準備：USER_Aの作成（DBAで実行）

CREATE USER user_a IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE TO user_a;

✅ RESOURCEロールにはトリガー作成に必要な権限（CREATE TRIGGER、CREATE TABLEなど）が含まれます。

---

🔐USER_Aでログイン

CONNECT user_a/password

---

① DDL操作を検出するトリガー（AFTER DDL ON SCHEMA）

🔍目的

• USER_A が自分のスキーマ内で CREATE, DROP, ALTER などを実行したときに、その内容を自動でログに残す

---

📘ログ記録用テーブル（USER_Aで実行）

CREATE TABLE ddl_log (
  event_type  VARCHAR2(20),
  object_type VARCHAR2(20),
  object_name VARCHAR2(100),
  exec_time   DATE
);

---

🛠トリガー作成（USER_Aで実行）

CREATE OR REPLACE TRIGGER trg_schema_ddl_log
AFTER DDL ON SCHEMA
BEGIN
  INSERT INTO ddl_log
  VALUES (
    ora_sysevent,
    ora_dict_obj_type,
    ora_dict_obj_name,
    SYSDATE
  );
END;
/

🔍解説：

関数名	説明
ora_sysevent	発生したDDL操作の種類（CREATE, DROPなど）
ora_dict_obj_type	対象のオブジェクト種別（TABLE, INDEXなど）
ora_dict_obj_name	対象のオブジェクト名

---

✅動作確認（USER_Aで実行）

-- 任意のDDLを実行
CREATE TABLE test_table (id NUMBER);
DROP TABLE test_table;

-- ログを確認
SELECT * FROM ddl_log;

🔎出力例：

EVENT_TYPE	OBJECT_TYPE	OBJECT_NAME	EXEC_TIME
CREATE	TABLE	TEST_TABLE	2025-06-29 15:12:34
DROP	TABLE	TEST_TABLE	2025-06-29 15:13:10

SQL> CREATE USER user_a IDENTIFIED BY password
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp
  4  QUOTA UNLIMITED ON users;

ユーザーが作成されました。

SQL> GRANT CONNECT, RESOURCE TO user_a;

権限付与が成功しました。

SQL> CONNECT user_a/password
接続されました。
SQL> CREATE TABLE ddl_log (
  2    event_type  VARCHAR2(20),
  3    object_type VARCHAR2(20),
  4    object_name VARCHAR2(100),
  5    exec_time   DATE
  6  );

表が作成されました。

SQL> CREATE OR REPLACE TRIGGER trg_schema_ddl_log
  2  AFTER DDL ON SCHEMA
  3  BEGIN
  4    INSERT INTO ddl_log
  5    VALUES (
  6      ora_sysevent,
  7      ora_dict_obj_type,
  8      ora_dict_obj_name,
  9      SYSDATE
 10    );
 11  END;
 12  /

トリガーが作成されました。

SQL> CREATE TABLE test_table (id NUMBER);

表が作成されました。

SQL> DROP TABLE test_table;

表が削除されました。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

セッションが変更されました。

SQL> set lin 1000
SQL> col event_type for a20
SQL> col object_type for a20
SQL> col object_name for a20
SQL> SELECT * FROM ddl_log;

EVENT_TYPE           OBJECT_TYPE          OBJECT_NAME          EXEC_TIME
-------------------- -------------------- -------------------- -------------------
CREATE               TABLE                TEST_TABLE           2025/06/29 14:41:42
DROP                 TABLE                TEST_TABLE           2025/06/29 14:41:50

---

② SQLエラーを記録するトリガー（AFTER SERVERERROR ON SCHEMA）

🔍目的

• USER_A がセッション中に発生させた ORA-エラーをトリガーで検出し、自動的にログとして記録する

---

📘ログ記録用テーブル（USER_Aで実行）

CREATE TABLE error_log (
  error_code  NUMBER,
  error_time  DATE
);

---

🛠トリガー作成（USER_Aで実行）

CREATE OR REPLACE TRIGGER trg_error_log
AFTER SERVERERROR ON SCHEMA
BEGIN
  INSERT INTO error_log
  VALUES (
    DBMS_STANDARD.SERVER_ERROR(1),
    SYSDATE
  );
END;
/

🔍解説：

内容	解説
AFTER SERVERERROR	SQLエラー（ORA-xxxx）が発生した直後に実行される
DBMS_STANDARD.SERVER_ERROR(1)	直近のエラーコードを取得（例：ORA-01476など）

---

✅動作確認（USER_Aで実行）

-- 故意にゼロ除算エラーを発生させる
BEGIN
  DECLARE v NUMBER := 1 / 0;
  dbms_output.put_line(v);
END;
/

-- エラーログ確認
SELECT * FROM error_log;

🔎出力例：

ERROR_CODE	ERROR_TIME
6550	2025-06-29 15:16:45

SQL> CREATE TABLE error_log (
  2    error_code  NUMBER,
  3    error_time  DATE
  4  );

表が作成されました。

SQL> CREATE OR REPLACE TRIGGER trg_error_log
  2  AFTER SERVERERROR ON SCHEMA
  3  BEGIN
  4    INSERT INTO error_log
  5    VALUES (
  6      DBMS_STANDARD.SERVER_ERROR(1),
  7      SYSDATE
  8    );
  9  END;
 10  /

トリガーが作成されました。

SQL> BEGIN
  2    DECLARE v NUMBER := 1 / 0;
  3    dbms_output.put_line(v);
  4  END;
  5  /
  dbms_output.put_line(v);
             *
行3でエラーが発生しました。:
ORA-06550: 行3、列14:
PLS-00103: 記号"."が見つかりました。 次のうちの1つが入るとき:
   constant exception
<an identifier>
<a double-quoted delimited-identifier> table columns long
double ref char time timestamp interval date binary national
character nchar
記号"<an identifier>" は続行のために"."に代わりました。 ORA-06550:
行4、列1:
PLS-00103: 記号"END"が見つかりました。 次のうちの1つが入るとき:
   begin function
pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior


SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

セッションが変更されました。

SQL> set lin 1000
SQL> SELECT * FROM error_log;

ERROR_CODE ERROR_TIME
---------- -------------------
      6550 2025/06/29 14:46:01

---

✅まとめ

トリガー名	用途
trg_schema_ddl_log	CREATE / DROP / ALTERなどのDDL操作を監査ログに記録
trg_error_log	SQLエラー（ORA-エラー）発生時にコードを自動記録

[参考]
14.12 CREATE TRIGGER文

スキーマとオブジェクト権限についての基本解説

Oracleデータベースにおけるスキーマとオブジェクト権限は、データベースの管理と操作において非常に重要な概念です。初心者にとってこれらの用語が難しく感じられることもありますが、理解すればデータベースの効率的な運用に役立ちます。本記事では、...

oracle-master00000.com

2024.10.01