ITコーディネータのシュウです。

今年は、例年になく暑い5月でしたね。26日には北海道で５月としての全国の観測史上最高気温となる３９・５度を観測するなど、異常な暑さでした。今からこの分だと、今年の夏はどうなるか？と心配してしまいます。

5月の中頃（5/19）はまだそれほどではなかったので、知人のご家族と一緒にバーベキューに行ってきましたが、天気も良く快適でした。
写真は埼玉県の羽生スカイスポーツ公園というところで、公園の名前にもあるように、スカイスポーツへの関心をもってもらおうという目的のもと整備された公園で、毎週土日祝日にはグライダーの飛行活動も行っていて、そこのクラブに入会すれば、実際にグライダーに乗ってスカイスポーツを楽しむことも出来るようです。

当日は、母の日も近かったので、日頃苦労している妻に感謝しようということで、準備は全てお父さんと子供たちで行って、妻には食べて楽しんでもらおうという企画で行いました。サプライズの花のプレゼントも用意して、妻もとても喜んでくれました。
たまにはごまをすっておかないと！

＜本日の題材＞
データベーストリガー2（Oracle）

ORACLEでのデータベーストリガーについて、前回取り上げましたが、前回は代表的なものとして、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動する「DMLトリガー」について取り上げてみました。
今回は、DDL操作(CREATE, ALTER, DROPなど)により起動する「DDLトリガー」について、試してみたいと思います。

例）
今回は、Oracleのデータベースについて、CREATE、DROP、ALTERという操作を行ったときに、どのユーザーが、どのオブジェクトに対して、いつ操作を行ったかをログに記録するというトリガーを作成してみます。
このとき、データベースに対してのトリガーを作成するためには、ADMINISTER DATABASE TRIGGER権限が必要です。
今回は、DBAロールを持つ、「test_admin」というユーザーでトリガーを作成します。

test_adminでログインした後、まず、処理のログを登録する「database_ddl_log」テーブルを作成します。

CREATE TABLE database_ddl_log (
user_name VARCHAR2(30),
activity VARCHAR2(20),
obj_name NVARCHAR2(30),
obj_type VARCHAR2(20),
event_date DATE
);

次に、トリガーを作成します。

CREATE OR REPLACE TRIGGER db_ddl_record
AFTER CREATE OR DROP OR ALTER ON DATABASE
DECLARE
   V_SYSEVENT   VARCHAR2(20);
   V_OBJ_OWNER   VARCHAR2(30);
   V_OBJ_NAME   VARCHAR2(30);
   V_OBJ_TYPE   VARCHAR2(20);
BEGIN
   V_SYSEVENT   := ora_sysevent;         -- イベントの取得
   V_OBJ_OWNER := ora_dict_obj_owner;   -- オブジェクトの所有者の取得
   V_OBJ_NAME   := ora_dict_obj_name;   -- オブジェクト名の取得
   V_OBJ_TYPE   := ora_dict_obj_type;   -- オブジェクトタイプの取得

INSERT INTO database_ddl_log
(user_name, activity, obj_name, obj_type, event_date)
VALUES (V_OBJ_OWNER, V_SYSEVENT, V_OBJ_NAME, V_OBJ_TYPE, SYSDATE);
END;
/

2行目に、AFTER CREATE OR DROP OR ALTER ON DATABASE と記載していますように、DATABASE全体において、CREATE、DROP、ALTER 文が実行された後に、起動するトリガーになります。そして、9行目から12行目までは、ora_ で始まるイベント属性関数を参照することで、必要な情報を一旦変数に格納し、最後にテーブルに登録します。

それでは、「BLOG_TEST」ユーザーでログインして、(1)新しくテーブルを作成、(2)定義の変更、(3)テーブルの削除をしてみます。

(1)   テーブルの作成
CREATE TABLE test_tab(
id INT
,name NVARCHAR2(30)
,CONSTRAINT PK_test_tab PRIMARY KEY (id));

(2)   テーブル定義の変更
ALTER TABLE test_tab
MODIFY name NVARCHAR2(50);

(3)   テーブルの削除
DROP TABLE test_tab;

それでは、上記の操作がログとして残っていることを確認します。
「test_admin」ユーザーで接続して、「database_ddl_log」テーブルを確認してみます。

SET LIN 120
COL user_name FORMAT A15
COL activity FORMAT A15
COL obj_name FORMAT A20
COL obj_type FORMAT A10
COL event_date FORMAT A20

SELECT
user_name, activity, obj_name, obj_type
, TO_CHAR(event_date, 'YYYY/MM/DD HH24:MI:SS') event_date
FROM database_ddl_log
ORDER BY event_date;

ログテーブルを抽出したところ、先ほどの処理（テーブル作成、テーブル定義変更、テーブル削除）が、ログの履歴として登録されていることが確認できました。

今日は以上まで

にほんブログ村