ストアドプロシージャとトリガとは ― DB内の処理

ストアドプロシージャとは、一連のSQL文をデータベースに登録しておき、名前を指定して呼び出す仕組みです。アプリが名前を1回呼ぶと、中のSQL文はDBMSの中で続けて実行されます。アプリとDBMSの間の通信を減らしたいときや、注文登録のような処理を複数のアプリで共有したいときに使います。

トリガは、表の行が追加・更新・削除されたときに、DBMSが自動で動かす処理です。どちらもデータベースの中に置く処理ですが、プロシージャは呼んだときに動き、トリガは表の変更をきっかけに動きます。

例には、商品表・注文表・在庫履歴表の3つを使います。商品表は、商品ごとの在庫数を持ちます。注文表には注文を記録し、在庫履歴表には在庫数の変化を記録します。

CREATE TABLE 商品 (
  商品ID CHAR(3) PRIMARY KEY,
  商品名 VARCHAR(20)
    NOT NULL,
  在庫数 INTEGER NOT NULL
    CHECK (在庫数 >= 0)
);
CREATE TABLE 注文 (
  注文ID INTEGER
    GENERATED ALWAYS
    AS IDENTITY PRIMARY KEY,
  商品ID CHAR(3) NOT NULL
    REFERENCES 商品,
  数量 INTEGER NOT NULL
);
CREATE TABLE 在庫履歴 (
  商品ID CHAR(3),
  変更前 INTEGER,
  変更後 INTEGER
);
INSERT INTO 商品 VALUES
  ('A01', 'ノート', 10),
  ('B02', 'ペン', 50);

次の例は、注文を1件登録する手続です。在庫を減らすUPDATE文と、注文を記録するINSERT文をまとめて、注文登録という名前で登録します。書き方はPostgreSQLに合わせています。

CREATE PROCEDURE 注文登録(
  p_商品ID CHAR(3),
  p_数量   INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE 商品
    SET 在庫数 =
      在庫数 - p_数量
    WHERE 商品ID = p_商品ID
      AND 在庫数 >= p_数量;
  IF NOT FOUND THEN
    RAISE EXCEPTION
      '在庫が足りません';
  END IF;
  INSERT INTO 注文
      (商品ID, 数量)
    VALUES (p_商品ID, p_数量);
END;
$$;

p_商品IDとp_数量は、呼び出すときに受け取る値です。この値を引数と呼びます。UPDATE文は、在庫が注文の数量以上ある行だけを減らします。

減らせる行が無ければ、IF NOT FOUNDの部分でエラーを返し、INSERT文には進みません。手続の中身には、SQL文に加えて条件分岐や繰り返しを書けます。例では、PostgreSQLのPL/pgSQLという言語で書いています。

登録した手続は、CALL文で呼び出します。

CALL 注文登録('A01', 2);

実行すると、A01の在庫は10から8になり、注文表に1行が増えます。このCALLを明示的なトランザクションの外で実行すると、CALL全体が1つのトランザクションになります。手続の途中でエラーになると、それまでのUPDATEの変更も取り消されます。

PostgreSQLの手続は、値を返す関数とは別のものです。関数はSELECT文の中で呼べますが、手続はCALL文で単独で呼びます。SELECT文で手続を呼ぶと、エラーになりました。

CALLに渡す値も、利用者の入力を文字列としてSQL文に貼り込めば危険です。その危険は、SQLインジェクションとXSSの違いの記事で説明しています。

アプリとDBMSの間の往復が減る

アプリ側で同じ処理をするなら、UPDATE文とINSERT文を別々に送ります。2つの文を1つのトランザクションにするには、前後にBEGINとCOMMITも送ります。SQL文を1つ送るたびに結果を待つと、ネットワークを4往復します。

プロシージャにすると、往復はCALLの1回になります。往復が減った分だけ、ネットワークを流れる量と待ち時間が減ります。2層と3層の構成ごとの往復の数は、クライアントサーバシステムの記事の図で確かめられます。その図はSQL文3つの例で、BEGINとCOMMITを往復に数えていません。そのため、手続を使わない2層の構成は3往復と表示されます。

処理を1か所にまとめられる

注文の登録を、Webの画面と業務用のアプリの両方から行うことがあります。手続をデータベースに置けば、どちらのアプリも同じCALLを送るだけです。在庫の確かめ方を変えるときも、直すのは手続の1か所で済みます。

表の権限を渡さずに済む

手続を呼ぶには、その手続のEXECUTE権限が必要です。PostgreSQLでは、SECURITY DEFINERを付けた手続は、持ち主の権限で動きます。利用者には手続のEXECUTE権限だけを与えれば、商品表の権限は不要です。

ALTER PROCEDURE
  注文登録(CHAR(3), INTEGER)
  SECURITY DEFINER;
REVOKE EXECUTE ON PROCEDURE
  注文登録(CHAR(3), INTEGER)
  FROM PUBLIC;
GRANT EXECUTE ON PROCEDURE
  注文登録(CHAR(3), INTEGER)
  TO 窓口;

窓口という利用者で試すと、CALLは成功し、商品表への直接のUPDATE文は権限のエラーになりました。利用者は、手続を通してしか在庫を変えられません。PostgreSQLは、手続を作った時点でEXECUTE権限を全員に与えるので、例ではREVOKEで先に取り消しています。

SECURITY DEFINERの手続は持ち主の権限で動くため、PostgreSQLの文書は、手続が参照するスキーマの探し方を安全に設定するよう求めています。権限の与え方と取り消し方は、GRANT文・REVOKE文の記事で説明しています。

手続の中身を書く言語と、手続を呼び出す文は、製品ごとに違います。別の製品に移すときは、手続を書き直すことになります。Oracle DatabaseはPL/SQLという言語を使います。ほかの製品の違いは次のとおりです。

手続の定義は、データベースの中にあります。アプリのソースと同じように変更の履歴を残すには、定義の文をファイルにしてバージョン管理します。そのファイルをデータベースに反映する手順も、別に用意します。

手続を試すには、表とデータを用意したデータベースが必要です。また、重い計算まで手続に入れると、その分の負荷はDBサーバにかかります。

トリガは、表へのINSERT・UPDATE・DELETEをきっかけに、DBMSが自動で動かす処理です。アプリはトリガを呼びません。表が変わると、変えた文がどこから来たかに関係なく動きます。

次の例は、商品表の在庫数が変わるたびに、変更前と変更後の値を在庫履歴表に記録します。PostgreSQLでは、まずトリガで動かす関数を作ります。その関数を、CREATE TRIGGERで商品表に結び付けます。

CREATE FUNCTION 履歴を記録()
  RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO 在庫履歴
    VALUES (OLD.商品ID,
            OLD.在庫数,
            NEW.在庫数);
  RETURN NULL;
END;
$$;

CREATE TRIGGER 在庫変更
  AFTER UPDATE OF 在庫数
  ON 商品
  FOR EACH ROW
  EXECUTE FUNCTION
    履歴を記録();

AFTER UPDATE OF 在庫数は、在庫数の列を更新した後に動かす指定です。FOR EACH ROWは、変わった行1つごとに1回動かす指定です。関数の中のOLDは変更前の行を、NEWは変更後の行を表します。

2行の在庫数を変えるUPDATE文を実行すると、トリガは2回動き、在庫履歴に2行が入りました。商品名だけを変えるUPDATE文では、在庫数の列が対象に無いので、トリガは動きませんでした。

FOR EACH STATEMENTを指定すると、文1つにつき1回だけ動きます。PostgreSQLでは、1行も変えなかったUPDATE文でも、文ごとのトリガは1回動きました。行ごとのトリガは、変わった行が無ければ動きません。

トリガが動くタイミングは、行を書き込む前のBEFOREと、書き込んだ後のAFTERから選びます。行ごとのBEFOREトリガは、書き込む値を直したり、その行の書き込みを止めたりできます。AFTERトリガは、書き込みが終わった結果を見て、別の表に記録するような処理に使います。

次の例は、BEFOREトリガで更新日時の列に現在の時刻を入れます。商品表に、更新日時の列を足してあるとします。BEFOREトリガがNEWの値を書き換えて返すと、その値で行が書き込まれます。

CREATE FUNCTION 日時を入れる()
  RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.更新日時 := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER 更新日時
  BEFORE UPDATE ON 商品
  FOR EACH ROW
  EXECUTE FUNCTION
    日時を入れる();

PostgreSQLの行ごとのBEFOREトリガは、NEWの代わりにNULLを返すと、その行の操作を飛ばします。トリガで書き込みを止めたい場合は、この仕組みかエラーを使います。ビューには、操作の代わりに動くINSTEAD OFのトリガも作れます。

次の図では、注文登録の手続と在庫変更のトリガを登録した状態で、実行する文を選べます。動いた処理の順番と、実行後の3つの表が表示されます。表の値は、PostgreSQLで同じ文を実行して確かめたものです。

CALL 注文登録('A01', 2);

UPDATE 商品
  SET 在庫数 = 在庫数 - 2
  WHERE 商品ID = 'A01';

CALL 注文登録('A01', 20);

トリガの代表的な使い道は、変更の記録です。前の節の在庫履歴のように、どの経路で表を変えても記録が残ります。

関連する表を合わせて更新するときにも使えます。例えば、注文表に行が入ったときに、商品表の在庫数を減らすトリガを作れます。この形なら、注文を登録するアプリは、在庫を減らす文を書かずに済みます。

トリガの処理は、きっかけになった文と同じトランザクションの中で実行されます。トリガの関数の中でエラーを起こすと、きっかけになったUPDATE文の変更も取り消されました。

トリガを増やしすぎると、処理の流れが追いにくくなる

トリガは、アプリのソースを読んでも見えない場所で動きます。UPDATE文1つの裏で別の表が変わるので、データが変わった理由を調べるときに見落としやすくなります。

トリガの中の文が、別の表のトリガをさらに動かすこともあります。この連鎖が重なると、1つの文で何が起きるかを把握しにくくなります。また、大量の行を変える文では、行ごとのトリガが行の数だけ動き、その分の時間がかかります。

在庫数が0未満にならない決まりは、例の商品表のCHECK制約のように制約で書けます。制約で書ける決まりは、トリガを使わずに済みます。制約の書き方は、CREATE TABLEと制約の記事にまとめました。

どちらもDBMSの中で動く処理で、違いは動き出すきっかけです。2つを並べると、次のようになります。

PostgreSQLのトリガは、関数を1つ動かす形で定義します。SELECT文をきっかけに動くトリガは作れません。

• PostgreSQL公式ドキュメント：User-Defined Procedures・CREATE PROCEDURE・CALL
• PostgreSQL公式ドキュメント：CREATE FUNCTION・Privileges
• PostgreSQL公式ドキュメント：CREATE TRIGGER・Overview of Trigger Behavior・Trigger Functions
• Oracle「Overview of PL/SQL」
• Microsoft「CREATE PROCEDURE (Transact-SQL)」
• MySQL「CREATE PROCEDURE and CREATE FUNCTION Statements」