e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第38回 「文トリガー、行トリガーの構文」

2012.10.22

こんにちは。インストラクターの蓑島です。
日に日に研修終了時の空が夕間暮れていくのがわかります。
すぐに冬が来て今年も暮れていくのですね。1年は本当に短いものです。

前回は、DMLトリガーの特長について説明しました。
DMLトリガーには、文トリガーと行トリガーがあり、文トリガーはDML文ごとに、行トリガーはDML対象の行ごとに起動するのでした。

今回はDMLトリガーの構文について解説します。

まず『文トリガー』の場合の必要最低限の構文です。
説明の便宜上、行番号をつけてます。

<<文トリガーの構文>>
1 CREATE OR REPLACE TRIGGER トリガー名
2 タイミング
3 イベント
4 ON 表名
5 無名PL/SQLブロック

解説です。
・2行目のタイミングには「BEFORE」もしくは「AFTER」を指定します。
 BEFOREであればDML処理の前に起動し、AFTERであればDML処理の後に起動します。

・3行目のイベントには、トリガーの起動するDML文の種類、 すなわち「INSERT」「UPDATE」「DELETE」を指定します。
 もし、INSERT、UPDATE、DELETEのいずれの操作でも起動するような場合は、 「INSERT OR DELETE OR UPDATE」のように指定してください。
 このように複数のイベントの場合は「OR」でつなげます。
 また、特に「UPDATE」の場合は、「UPDATE OF 列名1, 列名2,・・・」というように列名の指定も可能です。

・4行目には、このトリガーを設定する表の名前を指定します。

・5行目には、無名PL/SQLブロックを記述します。

PL/SQLブロックなので実際には複数行からなる大きな記述です。
ここにトリガーの処理する内容を記述します。
無名PL/SQLブロックなので、もし変数などを宣言する必要があれば、「DECLARE」キーワードで宣言部から開始します。
宣言するものがなければ、「BEGIN」キーワードで実行部から記述します。
第36回「データベーストリガーの概要」にトリガーの作成例がありますので後でご覧ください。

次に『行トリガー』の構文です。
構文は文トリガーとほとんど同じですが1箇所だけ違う点がありますよ!

<<行トリガーの構文>>
1 CREATE OR REPLACE TRIGGER トリガー名
2 タイミング
3 イベント
4 ON 表名
5 FOR EACH ROW       ←これが指定されていると行トリガー
6 無名PL/SQLブロック

違いは「FOR EACH ROW」というキーワードですね。
「ON 表名」の後ろに「FOR EACH ROW」というキーワードがあれば行トリガーであり、なければ、文トリガーです。

行トリガーと文トリガーは、構文の違いはわずかですが、トリガーの処理する内容に大きな違いがあります。
すなわち、行トリガーの場合は、DML操作の対象行の上で起動しており、『その行の列値が参照できる』という大きな特徴を持っていますので、DML操作の対象行の列値を使った処理を行う場合は、必ず行トリガーで対応することになります。

では行トリガーでDML操作の対象行の列値を参照する方法を紹介しましょう。
無名PL/SQLブロック内で以下のようにします。

   :OLD.列名      ←DML操作前の列値
   :NEW.列名      ←DML操作後の列値

簡単です!頭にコロン(:)をつける点に注意してください。

例えば、注文表と在庫表があり、注文表に注文データをINSERTすると在庫表の該当製品の在庫量をその分だけ減らすトリガーの例をご紹介します。

まず在庫表です。

SQL> SELECT * FROM 在庫;

製品名         在庫数
--------------------
テレビ             10
ラジオ             10

テレビ、ラジオそれぞれ在庫数は10台ということですね。

そして注文表です。

SQL> CREATE TABLE 注文
  2  ( 製品名  VARCHAR2(10),
  3    注文数  NUMBER(5));

注文表は製品名と注文数の2列だけの非常にシンプルな表です。

ここで、注文表にテレビの注文数が3台というデータをINSERTすると、在庫表のテレビの在庫数が、3台減って7台となるような行トリガーを作成しました。

<<行トリガーの作成>>
SQL> CREATE OR REPLACE TRIGGER 注文_在庫_TRIG
  2  AFTER INSERT ON 注文 FOR EACH ROW
  3  BEGIN
  4     UPDATE 在庫
  5     SET    在庫数 = 在庫数 - :NEW.注文数
  6     WHERE  製品名 = :NEW.製品名;
  7  END;
  8 /

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

早速、確認してみましょう。
テレビ3台の注文をINSERTします。

SQL> INSERT INTO 注文 VALUES ('テレビ', 3);

1行が作成されました。

注文表にテレビ3台の注文行があります。

SQL> SELECT * FROM 注文;

製品名         注文数
--------------------
テレビ              3

在庫表を確認します。テレビの在庫が3台減りました。

SQL> SELECT * FROM 在庫;

製品名         在庫数
--------------------
テレビ              7 ←この行です。在庫が3台減りました。
ラジオ             10

ね?
テレビ3台の注文行の上で上記のトリガーが起動し、在庫表をUPDATEした訳です。
そこでは、 :NEW.製品名は、'テレビ'であり、 :NEW.注文数は 3 です。
したがって、トリガーの中で以下と同等のUPDATEが実行されたわけなのです。

        UPDATE 在庫
        SET 在庫数 = 在庫数 - 3
        WHERE 製品名 = 'テレビ'

これにより在庫表のテレビの行の在庫数が3台減りました。

いかがでしょうか?
行トリガーを利用する場面が想像できたのではと思います。
今回は一般的な利用例をご紹介したところで終わります。
また次回、ご期待ください。

先頭へ戻る