触发器和存储过程的创建

前面的任务你是不是轻松搞定了？那好，接下来我给你点更有挑战性的内容。我们来巩固一下PL-SQL的知识，写点存储过程和触发器。准备好了吗？

存储过程的创建

下面这些存储过程，建议你加到你的电商平台数据库里。每个过程我都配了解释，告诉你它解决什么问题、为什么值得实现。这些过程覆盖了关键操作的自动化、提升用户体验、优化前台、物流、客服、营销和分析等。

1. 下单并自动预留库存

下单是任何电商平台的核心操作。这个过程不仅要创建订单和订单项，还要在仓库里预留商品、扣减数量、记录状态、发起支付、触发后续流程（比如通知、物流）。自动化这个流程能减少手动输入的错误，避免超卖，保证库存实时一致。

2. 自动补货

为了避免缺货和损失销售额，必须在库存低于阈值时及时补货。这个过程会检查所有商品的库存，和最小库存比对，自动生成采购单或内部补货单。自动化能加快响应速度，减少运营团队的手动工作量。

3. 批量更新订单状态并通知客户

后台经常需要批量切换订单状态（比如“paid” → “shipped” 或 “shipped” → “completed”），根据处理阶段来走。这个过程会批量更新所有符合条件的订单状态，写入变更历史（方便审计），还能触发客户通知。这样能自动化后台工作，减少手动失误。

4. 批量应用折扣/优惠码到商品

做促销或者活动时，经常需要给某个品类、品牌或一批商品批量加优惠码和折扣。这个过程会自动加上折扣，检查限制条件（日期、次数），更新使用计数，避免重复。

5. 自动退款给用户

退货和退款对用户信任很关键。这个过程要检查订单状态、验证退货、发起支付退款、更新状态、记录日志、通知用户。所有条件都在一个事务里处理，能降低出错和被滥用的风险。

6. 自动重新计算商品/卖家的平均评分

每次有新评价、修改或删除时，商品或用户的平均评分都要保持最新。这个过程会及时重新计算并更新“avg_rating”/“review_count”字段，加快前端查询，保证分析数据一致。

7. 自动下架库存为零的商品

为了让电商前台正常运行，避免用户遇到不好的体验，库存为零的商品要自动隐藏（下架）。这个过程会定期检查库存和商品状态，把状态改成“inactive”，记录日志，让商品目录始终是最新的，无需人工检查。

8. 自动分配订单给快递员并更新配送状态

物流环节要快速透明地把订单分配给快递员，更新配送状态，记录所有操作方便追踪。这个过程能自动化这些步骤，省去经理手动操作。

9. 批量推送通知给用户（活动触发、提醒）

促销、退款、状态变更或营销活动的通知要批量推送，按条件筛选（活跃用户、很久没买、弃单等）。这个过程能按指定用户群发push/email通知。

10. 归档过期数据（比如已完成订单或下架商品）

为了让数据库性能更好、减少“热”表的数据量，过期的记录（老订单、归档商品、过期工单）要定期转移或标记为“归档”。这个过程能简化数据删除或迁移，减少管理员手动操作。

触发器的创建

1. 订单状态变更日志

完整记录订单状态变更对审计、客服、分析和自动通知用户都很重要。触发器会在每次订单状态变更时，自动在"order".order_status_log插入一条记录，开发者不用在应用层手动维护历史。

2. 自动记录商品SKU价格变更历史

SKU价格变更历史对分析、显示“原价”、追踪活动和自动通知降价都很有用。触发器会把product.variant表里price字段的每次变更都写进product.price_history。这样能完整保存价格动态，不会漏掉。

3. 商品库存变更时同步更新时间

每次仓库库存变更（比如盘点、入库、出库）都要自动更新last_updated字段，方便分析和数据时效性控制。这个触发器还能自动检查是否到达最小库存，触发自动补货。

4. 自动下架库存为零的商品SKU

为了避免用户买到没货的商品，当所有仓库的SKU库存为零时，自动把is_active字段设为FALSE。这样能减少差评和订单取消。

5. 管理员登录尝试日志（安全）

管理员登录控制是信息安全的关键。所有登录尝试（成功和失败）都会通过BEFORE INSERT触发器自动写进admin.login_attempt。这样能及时发现攻击、入侵或异常操作。

6. 商品关键变更日志和自动记录历史

商品的所有重要修改（状态、描述、名称）都要记录，方便员工操作审计、错误恢复和防止滥用。触发器会在状态变更时写入product.status_history，也可以扩展到其他关键字段。

7. 自动更新优惠码使用次数

正确统计优惠码使用次数对限制活动、防止滥用很重要。每次往marketing.promo_usage插入时，触发器会自动增加marketing.promo_code表里的used_count，避免数据不一致。

8. 用户钱包余额变更时自动更新

为了保证用户积分/返现钱包余额显示正确，每次有新交易都要自动更新payment.wallet表里的最终余额。插入新交易的触发器能减少因应用错误导致的数据丢失或错乱。

9. 自动设置“主”地址、邮箱或电话

为了避免用户没有主邮箱/电话/地址（这对找回账号和沟通很关键），触发器会在没有主记录时自动把第一条设为is_primary=TRUE，并保证同一用户只有一个主记录。

10. 新评价时自动更新商品平均评分

为了在商品卡片和搜索里快速显示“平均评分”，每次有新评价时直接更新这个值，比每次都聚合查询高效。触发器会维护product.product表里的avg_rating和/或review_count缓存字段。

11. 自动设置内容发布时间

对于文章、页面或其他内容，状态变为“published”时要正确填充published_at字段。这样CMS的数据才完整，用户和管理员能看到准确的发布时间，前端不用手动更新。

12. 客服事件日志（工单状态变更）

完整记录客服工单和状态变更，方便评估客服工作、做分析、保证用户透明。触发器会在工单状态变更时自动写入support.ticket_status_log。

备注

加上这些触发器，能大大提升电商平台关键业务流程的可靠性、透明度和自动化，减少应用代码负担，保证数据在数据库层面的完整性。这些都是大型电商关系型系统的最佳实践。