こんにちは。インストラクターの蓑島です。早くもお盆の時期が終わってしまいましたね。
お盆休みで旅行に行ったり、帰省して郷里の友人と飲んだりした方も多いと思います。
まだちょっと仕事モードでないかもしれませんが、今週からスイッチを切り替えていきましょう。

前回は「複合トリガー」について解説しました。
複合トリガーは簡単に言えば４種類のDMLトリガー（BEFOREの文、BEFOREの行、AFTERの行、AFTERの文）を１つにまとめたものですね。では、そもそもDMLトリガーとはなんであったかというと、DML文(INSERT, UPDATE, DELETE)で自動起動するPL/SQLプログラムのことです。最も一般的なトリガーですね。
しかし、それ以外の種類のトリガーもいろいろとあります。
例えば 「DDL文」で起動するトリガーや、データベースに「ログイン」や「ログオフ」するときに起動するトリガー、何かの「エラーの時」に起動するトリガーや、データベースそのものを「起動」、「停止」するときに起動するトリガーなど、さまざまなトリガーがあります。
そういったDMLトリガー以外のトリガーのいくつかを今回から数回にわたって解説したいと思います。

ということで、今回はタイトルにあるように「DDLトリガー」について解説します。

「DDLトリガー」は、DDL文、すなわち「CREATE」、「ALTER」、「DROP」によって自動起動するトリガーです。
すなわち、CREATE文や、ALTER文、DROP文を実行したときに自動実行します。
そのとき、CREATEや、ALTER、DROPする対象は表とは限りません。
ビューでも、索引でも、とにかく CREATE, ALTER, DROPすると、DDLトリガーが定義されていれば自動起動します。

また、DDLトリガーは、DMLトリガーと違って、特定のオブジェクトを対象にして定義できません。
DDLトリガーでなく、DMLトリガーであれば、必ず、「ON 表名」という記述があり、特定の表（場合によってはビュー)に対して定義されますが、DDLトリガーは特定のオブジェクトに対して定義するものではありません。
なぜなら、まだCREATEされていないオブジェクトや、DROPされたオブジェクトのように、存在しないオブジェクトを指定してもコンパイルができないからです。
ですから、DDLトリガーは特定のオブジェクトに対して定義することはできないわけです。

では、早速、DDLトリガーの構文を解説します。構文は簡単に書けば以下のような構文です。

1 CREATE OR REPLACE TRIGGER トリガー名
2 BEFORE または AFTER CREATE OR ALTER OR DROP
3 ON SCHEMA または DATABASE
4 無名PL/SQLブロック

まず、2行目の 「BEFORE または AFTER」については文字通り「BEFORE」、「AFTER」のどれかを記述します。
DDL文の操作前に起動する場合は「BEFORE」、DDL操作の後で起動する場合は「AFTER」です。
同じく、2行目の「CREATE OR ALTER OR DROP」についてはDDLイベントの指定ですが、もちろん関係のないDDLイベントは省略しても結構です。
例えば、CREATEとDROPだけを対象にしたければ、「CREATE OR DROP」と記述します。

3行目の「ON」の次に、「SCHEMA または DATABASE」ということで、「SCHEMA」または「DATABASE」のどれかを記述します。
「SCHEMA」と指定すれば、このトリガーの所有者（スキーマユーザ）のみが対象となります。
「DATABASE」と指定すればすべてのユーザが対象になります。
先ほど説明したように、DDLトリガーではなく、DMLトリガーの場合は「ON」の次に記述するのは、特定の表名でしたね。
しかし、DDLトリガーは、特定のオブジェクトに定義できず、その代わり、「SCHEMA」または「DATABASE」のどちらかを指定するわけです。

4行目の「無名PL/SQLブロック」については、トリガーが処理する内容を記述します。
無名PL/SQLブロックですから変数などを宣言するときは、DECLAREキーワードから記述してください。
また処理する内容によっては、イベントの種類(CREATE, ALTER, DROP)、DDL操作の対象となるオブジェクトの名前などが必要な場合があります。そういったときは、システムで定義されたさまざまなイベント属性関数があり、それらのイベント属性関数を参照することで必要な情報を取得できます。

では早速、具体的な例をご紹介します。
これから行うデモは、DROP文を実行するときに起動するトリガーであり、DROPの対象となっているのが、SCOTTスキーマのEMP表である場合にエラーとするものです。つまり、一言で言えば、SCOTT.EMP表を削除しようとするとエラーにするトリガーです。

では解説します。

まず、2行目の「BEFORE DROP」の記述ですが、このトリガーはDROP文を実行する直前に起動するDDLトリガーであることを示します。
そして3行目の「ON DATABASE」の記述ですが、これはすべてのユーザで起動するトリガーであることを示します。
もしこの部分が、「ON SCHEMA」だったとしたら、このトリガーのスキーマユーザ(この例ではスキーマが明示されていないので、このトリガーの作成者がスキーマユーザ）以外のユーザがDROP文を実行した場合は、このトリガーが起動しないことになります。その場合、実行するユーザによっては、SCOTT.EMP表の削除を防止できない場合がありますから注意してください。
なお、「ON DATABASE」のトリガーを作成するためには、作成者に「ADMINISTER DATABASE TRIGGER」システム権限が必要ですから、データベース管理者が作成することが一般的です。

次に11行目から18行目にかけて変数への代入を行っていますが、代入文(:=)の右辺の小文字で記述してある ora_xxxx_xxx がイベント属性関数です。
イベント属性関数の値を変数に格納しているわけです。ご覧のように、イベントの種類('CREATE', 'ALTER', 'DROP')や、オブジェクトの所有者、名前、タイプなどさまざまな情報が取得できます。
デモの内容はイベント属性関数の代表的なものですが、これら以外にもさまざまなイベント属性関数があります。
詳しくはマニュアル「Oracle Database アプリケーション開発者ガイド - 基礎編」を参照してください。

そして、20行目から25行目にかけて取得した変数値を使って、SCOTT.EMP表のDROPである場合を判断し、ユーザ定義のシステムエラーとしているわけです。
「RAISE_APPLICATION_ERROR(-20000,'SCOTT.EMP表は削除できません');」
一般にトリガーの中でエラーが発生すれば、イベントの文まで取り消しされます。これは、2行目が「BEFORE」でなく、「AFTER」であったとしても同じことです。ですから2行目が「AFTER」でもSCOTT.EMP表のDROPは削除できないことになります。
しかし、トリガーの中で何かをチェックしてエラーにするようなロジックの場合、一般的には「BEFORE」が自然です。

ではせっかくトリガーを作りましたので、早速、SCOTT.EMP表の削除(DROP)が失敗することを確認しましょう。

SQL> DROP TABLE SCOTT.EMP;
DROP TABLE SCOTT.EMP
*
行1でエラーが発生しました。:
ORA-00604: 再帰SQLレベル1でエラーが発生しました。
ORA-20000: SCOTT.EMP表は削除できません
ORA-06512: 行21

ご覧のように、SCOTT.EMP表の削除は、ORA-20000エラー(トリガーの中で発生させたエラー）で失敗しますね。
これでこのトリガーが有効である限りはどのユーザもSCOTT.EMP表を削除できないことになります。

いかがでしたか？簡単でわかりやすい例ですね。

それでは今回はここまでにいたします。
次回もDDLトリガーの簡単な応用例をご紹介しようと考えています。
ご期待ください。