DB/Oracle/PLSQL/トリガ

Top/DB/Oracle/PLSQL/トリガ

目次†

PL/SQL、便利なトリガのお話。

↑

トリガとは†

データベーストリガ。実体はただのストアドプロシージャ。

表やビューの更新などが発生したときに、自動的に実行されるもの。表に対応付けてデータベースに格納しておく。またはシステムイベント（DB の起動時、停止時など）やユーザイベント（ログオン、ログオフなど）の際にも起動できる。

たとえば、在庫表のある商品の残数が 100 個から 50 個になったとき、自動でトリガが起動して数値をチェック、残数が値以下であれば発注表にデータを追加する、などの処理が可能。

プロシージャなだけあってだいたい何でもできるけど、Oracle のもともとの機能で代替できるならその方が早い。たとえばチェック制約とか。Oracle の機能で対応できずに、自動的（暗黙的）に何らかの処理を行わせたいときに用いるとよい。っぽい。

↑

基本的な構文†

結局はストアドプロシージャなので構文は似てる。オプションがいろいろあるけど。

  • トリガ 
    create or replace
-- トリガを作る。パラメータはない
trigger trg_hoge
  -- タイミング（後述）とイベント（後述）を表に対してセット
  <timing> <event> on <tablename>
  -- タイプ（後述）の指定
  <type>
-- この下に無名ブロックをくっつける
declare
    ...
begin
    ...
exception
    ...
end;
/
↑

タイミングの指定†

そのトリガがどのタイミングで起動するのかを指定する。

  • タイミング
    タイミング説明
    before表に対する操作の実行前にトリガを起動する
    after表に対する操作の実行後にトリガを起動する
↑

イベント†

表に対するどのような操作で起動するのかを指定する。以下のそれぞれを必要なだけ OR で列挙する。

  • イベントの指定
    イベント説明
    insertINSERT 時に起動する
    updateUPDATE 時に起動する
    update of <columnname>特定の列への UPDATE 時に起動する
    deleteDELETE 時に起動する
  • イベント指定の例 
    create or replace trigger trg_hoge
  -- HOGE 表に対する INSERT か UPDATE か DELETE で起動する
  before insert or update or delete on hoge
  -- HOGE 表に対する INSERT か FUGA 列への UPDATE で起動する
  before insert or update of fuga on hoge
↑

タイプ†

トリガのタイプ。どの範囲で動くのか指定するイメージ。

  • タイプ
    タイプ指定方法説明
    文トリガ何も指定しない影響を受ける行の数とは関係なく、ひとつの SQL 文の実行時に無条件に起動するトリガ。実行前に起動するか実行後に起動するかは前述のタイミングで指定する。
    行トリガfor each row一行に対して一回ずつ起動するトリガ。たとえば一回の UPDATE で 5 行が更新される場合は 5 回起動する。
↑

実行文の記述†

↑

:new と :old†

更新前と更新後の行のデータが格納されたバインド変数。ここから値をとりだすことで、ある行の更新前後の値を取得できる。

  • 値ベース監査にも使える 
    create or replace trigger trg_hoge
  befor update on tbl_hoge for each row
declare
    v_diff number;
begin
    v_diff := :new.foo - :old.foo;
    dbms_output.put_line('old value : ' || :old.foo);
    dbms_output.put_line('new value : ' || :old.foo);
    dbms_output.put_line('diff      : ' || :old.foo);
end;
/
Last-modified: 2011-11-09 (水) 15:04:38