SQL#13 トリガーとストアドプロシージャの実践

はじめに
学習の概要
実践
課題
1. 実践課題
まとめ

はじめに

こんにちは、シルです。
データベースの運用では、「データの変更に応じて自動処理を実行する」 ことがよくあります。
例えば：

注文が入ったら在庫を自動で減らす
新しいユーザー登録時にデフォルト権限を付与
定期的にレポートを生成して管理者に送る

このような 「データ変更時の自動処理」や「複雑な処理の自動化」 を実現するために トリガー や ストアドプロシージャ を活用します。

前回までの内容です
SQL#1 環境準備と基本操作
 SQL#2 テーブルとデータベースの管理
 SQL#3 ユーザー管理と権限の設定
 SQL#4 サーバーステータスとインデックス管理
 SQL#5 データベースのサイズ管理と構造確認
 SQL#6 基本的なデータ操作
 SQL#7 データ検索と削除の拡張
 SQL#8 ウィンドウ関数を使ったデータ分析
 SQL#9 集計と結合
 SQL#10 インデックス管理と最適化
 SQL#11 バックアップとリカバリ
 SQL#12 データモデルの設計と正規化の実践

学習の概要

📌 含まれる内容

トリガーを使った自動データ更新
ストアドプロシージャの作成と運用
パラメータ付きストアドプロシージャの使用
エラーハンドリングとログ記録

実践

トリガーを使った自動データ更新

トリガー（Trigger）とは、「INSERT」「UPDATE」「DELETE」などの操作が発生したときに、自動的に実行される処理 のことです。

例：注文が入ったら在庫を減らす

注文が確定したときに、在庫数を自動的に減らすトリガーを作成します。

1. 商品テーブル（Items）

CREATE TABLE Items (
    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(255),
    Stock INT
);

2. 注文詳細テーブル（OrderItems）

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    ItemID INT,
    Quantity INT,
    FOREIGN KEY (ItemID) REFERENCES Items(ItemID)
);

3. 在庫を減らすトリガー

DELIMITER //

CREATE TRIGGER reduce_stock
AFTER INSERT ON OrderItems
FOR EACH ROW
BEGIN
    UPDATE Items
    SET Stock = Stock - NEW.Quantity
    WHERE ItemID = NEW.ItemID;
END;

//
DELIMITER ;

✅ 注文が登録されたら、該当商品の Stock（在庫）を自動的に減らす！

ストアドプロシージャの作成と運用

ストアドプロシージャ（Stored Procedure）は、複数のSQL処理をまとめて実行するためのプログラム です。

例：特定の顧客の注文履歴を取得するストアドプロシージャ

DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN customerID INT)
BEGIN
    SELECT Orders.OrderID, Orders.OrderDate, OrderItems.ItemID, OrderItems.Quantity
    FROM Orders
    JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
    WHERE Orders.CustomerID = customerID;
END;

//
DELIMITER ;

実行方法

CALL GetCustomerOrders(1);

✅ 顧客ID（customerID）を指定すると、その顧客の注文履歴を取得！

パラメータ付きストアドプロシージャの使用

ストアドプロシージャにパラメータを渡すことで、動的な処理 を実装できます。

例：注文金額が一定額以上の注文を取得

DELIMITER //

CREATE PROCEDURE GetLargeOrders(IN minAmount DECIMAL(10,2))
BEGIN
    SELECT Orders.OrderID, Orders.CustomerID, SUM(Items.Price * OrderItems.Quantity) AS TotalAmount
    FROM Orders
    JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
    JOIN Items ON OrderItems.ItemID = Items.ItemID
    GROUP BY Orders.OrderID
    HAVING TotalAmount > minAmount;
END;

//
DELIMITER ;

実行方法

CALL GetLargeOrders(5000);

✅ 注文金額が 5000 以上の注文を取得できる！

エラーハンドリングとログ記録

ストアドプロシージャやトリガー内で、エラーハンドリングを実装 することで、安全なデータ管理ができます。

例：在庫が足りない場合にエラーを出す

DELIMITER //

CREATE TRIGGER check_stock
BEFORE INSERT ON OrderItems
FOR EACH ROW
BEGIN
    DECLARE stockLeft INT;

    SELECT Stock INTO stockLeft
    FROM Items
    WHERE ItemID = NEW.ItemID;

    IF stockLeft < NEW.Quantity THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '在庫不足のため、注文できません';
    END IF;
END;

//
DELIMITER ;

📌 実行結果

INSERT INTO OrderItems (OrderItemID, OrderID, ItemID, Quantity)
VALUES (1, 1001, 2001, 10);

➡ 在庫が不足していたら「在庫不足のため、注文できません」とエラーが発生！

課題

実践課題

ユーザー登録時に自動的に権限を付与するトリガーを作成
- 新規ユーザーが Users テーブルに登録されたら、自動的に UserRoles テーブルに「一般ユーザー（role = 'user'）」を登録する。
月次レポートの生成を自動化するストアドプロシージャを設計
- Orders テーブルから、指定した月の売上合計を取得し、レポートテーブル MonthlyReports に保存するストアドプロシージャを作成する。

まとめ

✅ トリガーを使って「データの自動更新」を実装した！
✅ ストアドプロシージャで「複数のSQL処理をまとめて実行」できるようになった！
✅ エラーハンドリングで「安全なデータ管理」ができるようになった！

この技術を活用すれば、データベースの運用を自動化し、より効率的なシステムが構築できるようになります！ 🚀