你之前的作業都很輕鬆搞定嗎？那我來給你點難一點的吧。現在我們來鞏固一下 PL-SQL 的知識，開始寫 procedure 跟 trigger。準備好了嗎？
建立 procedure
下面這些 procedure 建議你加到你的 marketplace 資料庫裡。每個 procedure 都有說明它解決什麼問題、對應哪些商業流程，還有為什麼值得實作。這些 procedure 涵蓋了自動化關鍵操作、提升用戶體驗、優化前台、物流、客服、行銷和分析。
1. 下單時自動預留商品庫存
下單是 marketplace 最核心的操作。這個 procedure 不只要建立訂單和明細，還要在倉庫預留商品、扣庫存、記錄狀態、啟動付款流程，還有後續的通知和物流。自動化這流程可以減少人工錯誤、避免 over-selling，確保庫存即時同步。
2. 自動補貨
為了避免 out-of-stock 和損失銷售，當庫存低於門檻時要及時補貨。這個 procedure 會檢查所有商品的庫存，跟最小庫存比對，自動產生採購單或內部補貨單。自動化可以縮短反應時間，也減少營運部門的人工負擔。
3. 批次更新訂單狀態並通知客戶
在 catalog 裡常常要批次把訂單從一個狀態轉到另一個（像是 “paid” → “shipped” 或 “shipped” → “completed”），依照處理階段。這個 procedure 會更新所有符合條件的訂單狀態、寫入變更歷史（方便 audit），還可以發送通知給客戶。這樣可以自動化後台工作，減少人工錯誤。
4. 批次套用折扣/優惠碼到商品
推廣商品或辦活動時，常常要對整個類別、品牌或一批商品批次套用優惠碼和折扣。這個 procedure 會自動加上折扣、檢查限制（日期、次數）、更新使用次數，還能避免重複。
5. 自動退款給用戶
處理退貨和退款對於用戶信任超級重要。這個 procedure 要檢查訂單狀態、退貨是否合法、啟動付款退款、更新狀態、log 紀錄交易，還要通知用戶。把所有條件都包在一個 transaction 裡，可以減少錯誤和濫用。
6. 重新計算商品/賣家的平均評分
每次有新評論、修改或刪除時，商品或用戶的平均評分都要即時更新。這個 procedure 會快速重算並更新 “avg_rating”/“review_count” 欄位，讓前端查詢更快，也確保分析數據一致。
7. 自動下架（停用）庫存為零的商品
為了讓 marketplace 前台正常運作，也避免用戶有不好的體驗，庫存為零的商品要自動隱藏（停用）。這個 procedure 會定期檢查倉庫和商品狀態，把狀態改成 “inactive”，log 變更，讓 catalog 維持最新狀態，不用人工檢查。
8. 自動指派訂單給快遞員並更新配送狀態
物流上，快速透明地指派快遞員、更新配送狀態、記錄所有動作很重要。這個 procedure 可以自動化這些步驟，省下經理人工操作。
9. 批次發送通知給用戶（活動 trigger、提醒）
像是特賣、退貨、狀態變更或行銷活動的通知，都要批次發送，還要根據條件（活躍用戶、很久沒買、放棄購物車等等）。這個 procedure 可以根據設定的 segment 發送 push/email 通知。
10. 歸檔舊資料（例如已完成訂單或不活躍商品）
為了維持資料庫效能、減少 “熱” 表的資料量，舊的紀錄（舊訂單、歸檔商品、過期客服單）要定期搬移或標記為 “archive”。這個 procedure 讓刪除或搬移資料更方便，也減少管理員的人工操作。
建立 trigger
1. 訂單狀態變更 log 紀錄
完整記錄訂單狀態變更對於 audit、客服、分析和自動通知用戶都很重要。trigger 會在每次訂單狀態變更時，自動在 "order".order_status_log 建立一筆紀錄，開發者不用在應用層手動維護歷史。
2. 自動記錄商品 variant 價格變更歷史
SKU 價格變動歷史對分析、顯示 “原價”、追蹤活動和自動通知折扣都很重要。trigger 會把 product.variant 表的 price 欄位每次變更都寫到 product.price_history。這樣可以完整保存價格動態，不會漏掉。
3. 商品庫存異動時同步更新倉庫紀錄
每次倉庫庫存變動（像是盤點、進貨、出貨），都要自動更新 last_updated 欄位，方便分析和確保資料即時。這種 trigger 也可以自動檢查是否達到最小庫存門檻，然後啟動自動補貨。
4. 商品 variant 庫存為零時自動停用
為了避免客戶買到沒庫存的商品，當所有倉庫的某個 variant 庫存為零時，要自動把 is_active 欄位設為 FALSE。這樣可以減少負評和訂單取消。
5. 管理員登入嘗試 log 紀錄（安全）
管理員登入控管是資訊安全的關鍵。所有登入嘗試（成功或失敗）都會透過 BEFORE INSERT trigger 自動寫到 admin.login_attempt。這樣可以及時發現攻擊、入侵或可疑行為。
6. 商品關鍵變更 log 紀錄和自動寫入歷史
所有重要的商品異動（狀態、描述、名稱變更）都要記錄，方便 audit、錯誤還原和防止濫用。trigger 會在狀態變更時寫到 product.status_history，也可以擴充到其他關鍵欄位。
7. 自動更新優惠碼使用次數
正確統計優惠碼使用次數對限制活動和防止濫用很重要。trigger 每次在 marketing.promo_usage 插入時，會自動把 marketing.promo_code 的 used_count 加一，避免資料不同步。
8. 用戶錢包餘額在交易時自動更新
為了正確顯示用戶 bonus/cashback 錢包餘額，每次交易都要自動更新 payment.wallet 的最終餘額。trigger 在插入新交易時，能減少應用程式錯誤導致的資料遺失或異常。
9. 自動設置 “主要” 地址、email 或電話
為了避免用戶沒有主要 email/電話/地址（這對帳號恢復和聯絡很重要），trigger 會自動把第一筆資料設為 is_primary=TRUE，如果還沒有的話，並確保同一用戶只有一筆是主要。
10. 新增評論時自動計算商品平均評分
為了讓商品卡片和搜尋能快速顯示 “平均評分”，每次新增評論時即時更新這個值，比每次查詢都重算 aggregate 還有效率。trigger 會維護 product.product 表的 avg_rating 和/或 review_count 快取欄位。
11. 自動設置內容發佈日期
對於文章、頁面或其他內容，當狀態變成 “published” 時，要正確填寫 published_at 欄位。這樣 CMS 才能保持一致：用戶和管理員都能看到正確的發佈日期，前端也不用再手動更新。
12. 客服事件（ticket 狀態變更）log 紀錄
完整記錄客服 ticket 狀態變更，可以評估客服表現、做分析，也讓用戶看到透明的處理流程。trigger 會在 ticket 狀態變更時，自動寫到 support.ticket_status_log。
備註
加上這些 trigger，可以大幅提升 marketplace 關鍵商業流程的可靠性、透明度和自動化，減輕應用層負擔，也確保資料在資料庫層級的一致性。這些都是大型 e-commerce 關聯式系統設計的最佳實踐。
GO TO FULL VERSION