DML操作の監査と入力データの自動整形
Oracleには「トリガー（Trigger）」という便利な仕組みがあります。トリガーを使えば、INSERTやUPDATEなどの操作に対して、自動的に処理を実行することができます。
この記事では、Oracleユーザー USER_A が自分のスキーマ内で運用に活用できるトリガーを2種類紹介します。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
🎯この記事の目的
- DML操作（INSERT/UPDATE/DELETE）の自動監査ログ取得
- BEFORE INSERTトリガーで値の自動整形や補完
- すべて
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;
✅ 表領域
USERSの使用権限（クォータ）を明示的に付与しておかないと、テーブル作成やデータ挿入時にORA-01950エラーが発生します。
🔐USER_Aでログイン
CONNECT user_a/password
① DML操作をログに残すトリガー（AFTER INSERT/UPDATE/DELETE）
🔍目的
empテーブルの変更操作をすべてemp_auditテーブルに記録する- 操作ユーザー、操作種別（INSERT/UPDATE/DELETE）、対象データ、時刻を残す
📘テーブル作成（USER_Aで実行）
-- 操作対象のテーブル
CREATE TABLE emp (
empno NUMBER,
ename VARCHAR2(100)
);
-- 操作履歴を記録する監査テーブル
CREATE TABLE emp_audit (
username VARCHAR2(30),
action_type VARCHAR2(10),
empno NUMBER,
log_time DATE
);
🛠トリガー作成（USER_Aで実行）
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
v_action VARCHAR2(10);
v_empno NUMBER;
BEGIN
IF INSERTING THEN
v_action := 'INSERT';
v_empno := :NEW.empno;
ELSIF UPDATING THEN
v_action := 'UPDATE';
v_empno := :NEW.empno;
ELSIF DELETING THEN
v_action := 'DELETE';
v_empno := :OLD.empno;
END IF;
INSERT INTO emp_audit
VALUES (
USER,
v_action,
v_empno,
SYSDATE
);
END;
/
🔍解説：
AFTER INSERT OR UPDATE OR DELETE ON emp
→empテーブルでDML操作が発生したあとにトリガーが実行されます。FOR EACH ROW
→ 各行の操作に対してトリガーが個別に実行されます。IF INSERTING / UPDATING / DELETING
→ どの操作かを識別して処理内容を分岐します。:NEW/:OLD
→:NEWは挿入または更新後のデータ、:OLDは削除または更新前のデータを参照するプレフィクスです。USER
→ 現在のセッションユーザー名（この場合USER_A）が取得されます。SYSDATE
→ 実行された時刻を取得します。
✅動作確認（USER_Aで実行）
-- データ挿入
INSERT INTO emp VALUES (1001, 'SCOTT');
-- データ更新
UPDATE emp SET ename = 'SCOTT2' WHERE empno = 1001;
-- データ削除
DELETE FROM emp WHERE empno = 1001;
-- ログ確認
SELECT * FROM emp_audit;
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
🔎出力例：
| USERNAME | ACTION_TYPE | EMPNO | LOG_TIME |
|---|---|---|---|
| USER_A | INSERT | 1001 | 2025-06-29 14:01:23 |
| USER_A | UPDATE | 1001 | 2025-06-29 14:02:01 |
| USER_A | DELETE | 1001 | 2025-06-29 14:02:45 |
SQL> CREATE USER user_a IDENTIFIED BY password; ★ユーザー作成
ユーザーが作成されました。
SQL> GRANT CONNECT, RESOURCE TO user_a;
権限付与が成功しました。
SQL> ALTER USER user_a QUOTA UNLIMITED ON users;
ユーザーが変更されました。
SQL> CONNECT user_a/password
接続されました。
SQL> show user
ユーザーは"USER_A"です。
SQL> CREATE TABLE emp (
2 empno NUMBER,
3 ename VARCHAR2(100)
4 );
表が作成されました。
SQL> CREATE TABLE emp_audit (
2 username VARCHAR2(30),
3 action_type VARCHAR2(10),
4 empno NUMBER,
5 log_time DATE
6 );
表が作成されました。
SQL> CREATE OR REPLACE TRIGGER trg_emp_audit ★トリガー作成
2 AFTER INSERT OR UPDATE OR DELETE ON emp
3 FOR EACH ROW
4 DECLARE
5 v_action VARCHAR2(10);
6 v_empno NUMBER;
7 BEGIN
8 IF INSERTING THEN
9 v_action := 'INSERT';
10 v_empno := :NEW.empno;
11 ELSIF UPDATING THEN
12 v_action := 'UPDATE';
13 v_empno := :NEW.empno;
14 ELSIF DELETING THEN
15 v_action := 'DELETE';
16 v_empno := :OLD.empno;
17 END IF;
18 19 INSERT INTO emp_audit
20 VALUES (
21 USER,
22 v_action,
23 v_empno,
24 SYSDATE
25 );
26 END;
27 /
トリガーが作成されました。
SQL> INSERT INTO emp VALUES (1001, 'SCOTT');
1行が作成されました。
SQL> UPDATE emp SET ename = 'SCOTT2' WHERE empno = 1001;
1行が更新されました。
SQL> DELETE FROM emp WHERE empno = 1001;
1行が削除されました。
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
セッションが変更されました。
SQL> set lin 1000
SQL> col username for a20
SQL> col action_type for a20
SQL> SELECT * FROM emp_audit;
USERNAME ACTION_TYPE EMPNO LOG_TIME
-------------------- -------------------- ---------- -------------------
USER_A INSERT 1001 2025 06 29 14:04:34 ★監視されている
USER_A UPDATE 1001 2025 06 29 14:04:44 ★
USER_A DELETE 1001 2025 06 29 14:04:55 ★
② BEFORE INSERTで値を加工・補完
🔍目的
- 顧客名を自動で大文字変換
- 作成日時（created_at）を自動で
SYSDATEに設定
📘テーブル作成（USER_Aで実行）
CREATE TABLE customer (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
created_at DATE
);
🛠トリガー作成（USER_Aで実行）
CREATE OR REPLACE TRIGGER trg_customer_defaults
BEFORE INSERT ON customer
FOR EACH ROW
BEGIN
-- 名前を大文字に変換
:NEW.name := UPPER(:NEW.name);
-- 日付が未設定なら現在時刻を補完
IF :NEW.created_at IS NULL THEN
:NEW.created_at := SYSDATE;
END IF;
END;
/
🔍解説：
BEFORE INSERT ON customer
→customerテーブルにデータを挿入する直前にトリガーが実行されます。:NEW.name := UPPER(:NEW.name);
→ 挿入される顧客名をすべて大文字に変換します。:NEW.created_at := SYSDATE;
→created_atがNULLの場合は、現在の日付時刻を自動で補完します。
✅動作確認（USER_Aで実行）
-- 日付未指定でINSERT
INSERT INTO customer (id, name) VALUES (1, 'tanaka');
-- 確認
SELECT * FROM customer;
🔎出力例：
| ID | NAME | CREATED_AT |
|---|---|---|
| 1 | TANAKA | 2025-06-29 14:05:21 |
🧩構成図（テキスト）
┌────────────┐ ┌───────────────────────┐
│ INSERT文実行 │─────▶│ BEFORE INSERTトリガー │
└────────────┘ └───────────────────────┘
│
┌─────────┴─────────┐
│ 1. 名前を大文字化 │
│ 2. 日付を補完 │
└─────────┬─────────┘
▼
┌────────────────┐
│ データベースに │
│ 正規化された値を │
│ 登録 │
└────────────────┘
SQL> CREATE TABLE customer (
2 id NUMBER PRIMARY KEY,
3 name VARCHAR2(100),
4 created_at DATE
5 );
表が作成されました。
SQL> CREATE OR REPLACE TRIGGER trg_customer_defaults ★トリガー作成
2 BEFORE INSERT ON customer
3 FOR EACH ROW
4 BEGIN
5 :NEW.name := UPPER(:NEW.name);
6 IF :NEW.created_at IS NULL THEN
7 :NEW.created_at := SYSDATE;
8 END IF;
9 END;
10 /
トリガーが作成されました。
SQL> INSERT INTO customer (id, name) VALUES (1, 'tanaka');
1行が作成されました。
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
セッションが変更されました。
SQL> set lin 1000
SQL> col name for a20
SQL> SELECT * FROM customer;
ID NAME CREATED_AT
---------- -------------------- -------------------
1 TANAKA 2025 06 29 14:14:55 ★大文字になっている
✅まとめ
| トリガー名 | 用途 |
|---|---|
trg_emp_audit | emp テーブルの DML 操作を emp_audit に記録 |
trg_customer_defaults | customer テーブルの入力補正（大文字化、日時補完） |
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント