MariaDB講座 / ストアドプロシージャ・関数・トリガーを使い分ける

MariaDB講座

ストアドプロシージャはCALLで明示実行し、ストアド関数はSQL式の値を返し、トリガーは表変更へ反応して自動実行します。どれもサーバーに処理を保存しますが、呼び出し方、権限とトランザクションの責任が違います。

目的と暗黙の動作を区別する

プロシージャが必ず更新専用、関数が必ず算術専用という制約ではありません。この例は在庫変更、金額計算、変更記録で差を示します。トリガーは行ごとなので、大量更新ならその数の仕事も発生します。

アプリケーションが明示的に呼ぶ処理と、すべての書込経路で発動する処理を分けます。隠れた処理は障害調査を難しくし得るため、必要な共通規則かを判断します。呼出側のトランザクションとVIEWの実行権限も比較できます。

権限と二つのInnoDB表を準備する

専用appops_labでCREATE ROUTINE、TRIGGER、表の権限を持つ実習用接続を使います。呼出にはEXECUTEも必要で、全体管理者権限をアプリへ与える解決にしません。関数作成とbinary log方針で追加条件がある場合は、DBAが確認します。

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
  product_id INT NOT NULL PRIMARY KEY,
  stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
  audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_stock INT NOT NULL,
  new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);

両表がInnoDBなので、在庫とトリガーの記録を一緒に取り消せます。既存オブジェクトは定義とデータを確認し、無条件に消さないでください。

三種類の完全な定義を作る

mariadbクライアントで使うスクリプトです。DELIMITERはクライアントの文終端設定で、サーバーSQLではありません。GUIは対応するスクリプト実行、PythonはDELIMITERと//を除いた完全CREATE文を一回ずつ送ります。

DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
  IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
  END IF;
  RETURN ROUND(p_price * p_qty, 2);
END//

CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
  IF p_delta IS NULL OR p_delta = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
  END IF;
  UPDATE routine_stock SET stock = stock + p_delta
  WHERE product_id = p_product_id AND stock + p_delta >= 0;
  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
  END IF;
END//

CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
  IF OLD.stock <> NEW.stock THEN
    INSERT INTO routine_audit (product_id, old_stock, new_stock)
    VALUES (NEW.product_id, OLD.stock, NEW.stock);
  END IF;
END//
DELIMITER ;

line_totalは負数・NULLを拒否し小数2桁を返します。大額や数量でDECIMALの範囲を再検討し、DETERMINISTIC NO SQLは実際の処理と一致させます。adjust_stockは既存一商品を非負になる場合だけ更新し、直後のROW_COUNTで検査してSIGNALを返します。間に別文を入れると診断の対象が変わります。

トリガーはOLDとNEWが違うときだけ記録します。これは変更ログの実習で、独立した改ざん防止監査システムではありません。

在庫と記録を取消・確定で比べる

同じ接続で実行します。

SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;

金額37.50、途中在庫5・記録8→5、ROLLBACK後は在庫8・監査0件です。自動採番の欠番は取消失敗の証拠ではありません。次は確定します。

START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;

在庫6、記録8→6の一件です。プロシージャにCOMMITを入れないため呼出側が他処理とまとめられ、トリガーも元の文の文脈で動き独立確定しません。

不正入力とセキュリティ境界を確かめる

二文は別々に実行します。

CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);

どちらもSQLSTATE 45000で、在庫6・確定記録一件が残るか確認します。関数と手続きはINVOKERなので、EXECUTEだけでUPDATEがないユーザーは在庫を変えられません。トリガーはDEFINERの文脈なので、定義者の寿命と権限を管理します。

トリガーには元表の再変更やトランザクション制御などの制限があります。長い外部処理を隠さず、DBトランザクションに関連した短い作業へ限定します。

定義とバックアップ、削除範囲を確認する

定義と実行文脈を読みます。

SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;

バックアップには必要なルーチンとトリガーが含まれるか確認します。復元できてもDEFINER不在や権限不足で動かない場合があります。不要と確認した実習専用だけを消します。

DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;

通常のROLLBACKでは戻せません。本番の変更は呼出側との調整、移行と復旧手順も必要です。