前回の課題は簡単だった？じゃあ、ちょっとレベルアップしようか。PL-SQLの知識を固めつつ、プロシージャとトリガーを書いてみよう。準備はいい？
プロシージャの作成
下に、君のマーケットプレイスのデータベースに追加するのがおすすめなプロシージャをまとめてみた。それぞれ、どんな課題やビジネスプロセスを解決するのか、なぜ実装すべきかも説明してるよ。これらのプロシージャは、主要なオペレーションの自動化、ユーザー体験の向上、ストアフロントや物流、サポート、マーケティング、分析の最適化をカバーしてる。
1. 商品の自動在庫確保付き注文処理
注文処理はどんなマーケットプレイスでも超重要なオペレーションだよね。このプロシージャは、注文とその明細を作成するだけじゃなくて、倉庫で商品を自動的に確保して在庫数を減らし、ステータスを記録して、支払いを開始し、通知や物流などの後続プロセスも起動する。こうやって自動化することで、手作業のミスやover-sellingを防げるし、在庫の一貫性もリアルタイムで保てるよ。
2. 商品在庫補充の自動化
out-of-stockや販売機会の損失を防ぐには、在庫がしきい値を下回ったらすぐに補充するのが大事。このプロシージャは、全商品の在庫をチェックして、最小レベルと比べて自動的に発注や内部リサプライの申請を作成する。自動化で反応速度が上がるし、オペレーション部門の手間も減るよ。
3. 注文ステータスの一括更新と顧客通知
カタログでは、注文を一括でステータス変更（例: “paid” → “shipped” や “shipped” → “completed”）することがよくある。このプロシージャは、該当する注文のステータスを全部更新して、変更履歴を書き込んで（監査用）、必要なら顧客への通知も送る。バックオフィスの作業を自動化して、手作業ミスも減らせるよ。
4. 商品への一括割引・プロモコード適用
商品のプロモーションやキャンペーンでは、カテゴリやブランド、特定商品のグループに一括でプロモコードや割引を適用することが多い。このプロシージャは、割引を自動で設定し、制限（期間や上限）を守りつつ、利用回数も更新して、重複を防ぐよ。
5. ユーザーへの自動返金処理
返品や返金は、ユーザーの信頼を保つために超重要なプロセス。このプロシージャは、注文のステータスや返金の妥当性をチェックして、支払いの返金を開始し、ステータスを更新、トランザクションをログに記録し、ユーザーに通知する。全部を1つのトランザクションで処理することで、ミスや悪用のリスクを減らせるよ。
6. 商品・出品者の平均評価の再計算
新しいレビューが追加・変更・削除されるたびに、商品の平均評価やユーザーの評価も最新にしておきたい。このプロシージャは、“avg_rating”や“review_count”フィールドをすぐに再計算・更新して、フロントエンドのクエリを高速化し、分析の一貫性も保つよ。
7. 在庫ゼロ商品の自動非表示（非アクティブ化）
ストアフロントの正しい動作や、ユーザーのネガティブ体験を防ぐために、在庫ゼロの商品は自動的に非表示（非アクティブ）にするべき。このプロシージャは、定期的に倉庫と商品のステータスをチェックして、“inactive”に変更し、変更をログに記録して、カタログを手作業なしで最新に保てるよ。
8. 注文の自動配達員割り当てと配送ステータス更新
物流では、注文に配達員を素早く割り当てて、配送ステータスを変更し、すべてのアクションを追跡できるようにするのが大事。このプロシージャは、そのステップを自動化して、マネージャーの手作業をなくせるよ。
9. ユーザーへの一括通知配信（キャンペーントリガーやリマインダー）
セールや返品、ステータス変更、マーケティング活動の通知は、一括で配信しつつ、条件（アクティブユーザー、長期間購入してない人、カゴ放置など）も考慮したい。このプロシージャは、指定したセグメントにpush/email通知を一括送信できるよ。
10. 古いデータのアーカイブ（例: 完了済み注文や非アクティブ商品）
データベースのパフォーマンス維持や“ホット”テーブルの容量削減のために、古いレコード（過去の注文、アーカイブ商品、古いサポートチケットなど）は定期的に移動または“アーカイブ”としてマークするべき。このプロシージャは、データの削除や移動を簡単にして、管理者の手間も減らせるよ。
トリガーの作成
1. 注文ステータス変更のログ記録
注文ステータスの変更履歴を完全に記録するのは、監査やサポート、分析、ユーザーへの通知自動化のために超重要。トリガーは、注文ステータスが変わるたびに"order".order_status_logに自動でレコードを作成して、アプリ側で履歴を手作業で管理する必要をなくすよ。
2. 商品バリアント価格変更履歴の自動記録
SKUの価格変更履歴は、分析や“旧価格”表示、キャンペーン追跡、自動割引通知に必要。トリガーは、product.variantテーブルのpriceフィールドが変わるたびに、product.price_historyに履歴を記録する。これで価格の推移をミスなく完全に保存できるよ。
3. 商品在庫変更時の倉庫在庫同期
倉庫在庫の変更（例: 棚卸し、入荷、出庫）があるたびに、last_updatedフィールドを自動更新して、分析やデータの新しさを管理するのが大事。また、このトリガーで最小在庫のチェックや自動発注も起動できるよ。
4. 在庫ゼロ商品のバリアント自動非アクティブ化
在庫ゼロの商品をユーザーが買えないように、全倉庫で在庫がゼロになったら、そのバリアントのis_activeフィールドを自動でFALSEにする。これでネガティブレビューや注文キャンセルを減らせるよ。
5. 管理者ログイン試行のログ記録（セキュリティ）
管理者のログイン管理は情報セキュリティのカギ。すべてのログイン試行（成功・失敗問わず）をadmin.login_attemptにBEFORE INSERTトリガーで自動記録する。これで攻撃や不審な動きをすぐに検知できるよ。
6. 商品の主要変更のログ記録と履歴自動作成
商品の重要な修正（ステータス、説明、名前の変更など）は、スタッフの操作監査やミス復旧、不正防止のために記録すべき。トリガーは、ステータス変更時にproduct.status_historyに履歴を作成し、他の重要フィールドにも拡張できるよ。
7. プロモコード利用回数の自動カウント更新
プロモコードの利用回数を正しく管理するのは、キャンペーン制限や悪用防止のために重要。トリガーは、marketing.promo_usageへのINSERTごとに、marketing.promo_codeのused_countを増やして、データの不整合を防ぐよ。
8. ユーザーウォレット残高のトランザクション時自動更新
ボーナスやキャッシュバックウォレットの残高表示を正しくするには、各トランザクションごとにpayment.walletの最終残高を自動更新するのが大事。新しいトランザクションのINSERTトリガーで、アプリのミスによるデータ損失や不整合を減らせるよ。
9. メイン住所・メール・電話の自動設定
ユーザーにメインのメール/電話/住所がないと、アクセス復旧や連絡に困るから、トリガーで最初のレコードにis_primary=TRUEを自動設定し、1ユーザー内で一意性も保証するよ。
10. レビュー追加時の商品平均評価の自動計算
商品カードや検索で“平均評価”を素早く表示するには、新しいレビュー追加ごとにこの値を更新するのが効率的。トリガーは、product.productテーブルのavg_ratingやreview_countフィールドのキャッシュを保つよ。
11. コンテンツ公開日の自動設定
記事やページなどの公開時、“published”ステータスに変わったらpublished_atフィールドを正しく埋めるのが大事。これでCMSの整合性が保たれて、ユーザーや管理者が正確な公開日を見れるし、フロントで手作業の更新も不要になるよ。
12. サポートイベント（チケットステータス変更）の全履歴ログ
サポートへの問い合わせやそのステータス変更の全履歴は、サポート品質の評価や分析、ユーザーへの透明性確保に役立つ。トリガーは、チケットのステータス変更時にsupport.ticket_status_logに自動で記録するよ。
メモ
これらのトリガーを追加することで、マーケットプレイスの主要ビジネスプロセスの信頼性・透明性・自動化が大幅にアップするし、アプリ側の負担も減って、データベースレベルでデータの整合性を守れるよ。大規模e-commerce向けリレーショナルシステム設計のベストプラクティスだね。
GO TO FULL VERSION