【趣学SQL】第四章：高级 SQL 功能 4.1 触发器与存储过程——数据库的“自动机器人“和“万能工具箱“

第四章：高级 SQL 功能

4.1 触发器与存储过程——数据库的"自动机器人"和"万能工具箱"

欢迎来到「数据库魔法工坊」！今天我们将化身"SQL巫师"，用一家虚拟咖啡店的会员系统崩溃案例，教你如何用触发器和存储过程打造自动化的数据流水线。☕️🤖

---

4.1.1 触发器的基本概念——当数据库学会"条件反射"

真实惨案：
某咖啡店因人工操作失误导致：

• VIP会员充值100元，积分却未到账
• 新用户注册未分配默认优惠券
• 凌晨3点订单激增，值班程序员被迫起床改数据

触发器的三大特性：

1. 事件驱动：在增删改操作前后自动触发
2. 隐形执行：像后台运行的智能管家
3. 连锁反应：一个操作可触发多个动作

-- 当订单表插入数据时，自动计算折扣价  
DELIMITER $$  
CREATE TRIGGER before_order_insert  
BEFORE INSERT ON orders  
FOR EACH ROW  
BEGIN  
  IF NEW.amount > 100 THEN  
    SET NEW.discount_price = NEW.amount * 0.9;  
  ELSE  
    SET NEW.discount_price = NEW.amount;  
  END IF;  
END$$  
DELIMITER ;  

输出效果：

INSERT INTO orders (amount) VALUES (120);  
-- 数据库自动将discount_price设为108  

📌 警告：触发器不是乐高积木！过度使用会导致"触发器地狱"（比如触发器的触发器中再触发触发器…）

---

4.1.2 创建和管理触发器——给数据库安装"智能芯片"

语法模板：

CREATE TRIGGER trigger_name  
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}  
ON table_name  
FOR EACH ROW  
BEGIN  
  -- 魔法发生在这里  
END;  

管理命令三件套：

-- 查看所有触发器  
SHOW TRIGGERS;  

-- 删除触发器  
DROP TRIGGER before_order_insert;  

-- 临时禁用触发器  
ALTER TABLE orders DISABLE TRIGGER ALL;  

搞笑案例：
某新手创建了AFTER DELETE触发器来备份数据，结果删除测试数据时触发备份——测试数据成了永生不灭的僵尸！

---

4.1.3 触发器的常见应用场景

场景1：数据校验（保安型触发器）

CREATE TRIGGER validate_phone  
BEFORE INSERT ON customers  
FOR EACH ROW  
BEGIN  
  IF NEW.phone NOT REGEXP '^1[3-9]\\d{9}$' THEN  
    SIGNAL SQLSTATE '45000'  
    SET MESSAGE_TEXT = '手机号格式错误！';  
  END IF;  
END;  

效果：试图插入138一二三四5678会触发错误，就像门卫拦住没戴口罩的访客

场景2：审计日志（记录型触发器）

CREATE TRIGGER log_balance_change  
AFTER UPDATE ON members  
FOR EACH ROW  
BEGIN  
  INSERT INTO balance_logs  
  (member_id, old_balance, new_balance)  
  VALUES  
  (OLD.id, OLD.balance, NEW.balance);  
END;  

输出示例：

+---------+------------+------------+  
| 1001    | 500.00     | 300.00     | ← 记录每次余额变动  
+---------+------------+------------+  

场景3：数据同步（搬运工触发器）

CREATE TRIGGER sync_inventory  
AFTER INSERT ON orders  
FOR EACH ROW  
BEGIN  
  UPDATE products  
  SET stock = stock - NEW.quantity  
  WHERE id = NEW.product_id;  
END;  

💡 就像咖啡师做完一杯拿铁，自动在库存本上划掉一份咖啡豆

---

4.1.4 存储过程的基本概念——SQL的"瑞士军刀"

血泪教训：
某电商活动期间，前端重复发送1000次相同SQL请求，导致数据库CPU飙到100%——如果用存储过程，网络传输量减少90%！

存储过程优势：

• 预编译执行：像微波炉加热预制菜
• 减少网络开销：传输"菜名"而非整个菜谱
• 事务封装：保证多步操作原子性

---

4.1.5 创建和管理存储过程——编写SQL的"智能脚本"

语法模板：

DELIMITER $$  
CREATE PROCEDURE procedure_name(IN param1 INT, OUT result VARCHAR(20))  
BEGIN  
  -- 魔法脚本在此  
END$$  
DELIMITER ;  

咖啡店会员充值案例：

CREATE PROCEDURE recharge_member(  
  IN member_id INT,  
  IN amount DECIMAL(10,2),  
  OUT new_balance DECIMAL(10,2)  
)  
BEGIN  
  START TRANSACTION;  
  UPDATE members  
  SET balance = balance + amount  
  WHERE id = member_id;  

  INSERT INTO recharge_logs  
  (member_id, amount)  
  VALUES  
  (member_id, amount);  

  SELECT balance INTO new_balance  
  FROM members  
  WHERE id = member_id;  

  COMMIT;  
END;  

调用示例：

CALL recharge_member(1001, 200.00, @balance);  
SELECT @balance; -- 输出：700.00  

---

4.1.6 存储过程的常见应用场景

场景1：复杂事务处理（原子操作）

CREATE PROCEDURE place_order(  
  IN user_id INT,  
  IN product_id INT,  
  IN quantity INT  
)  
BEGIN  
  DECLARE EXIT HANDLER FOR SQLEXCEPTION  
  BEGIN  
    ROLLBACK;  
    RESIGNAL;  
  END;  

  START TRANSACTION;  
  INSERT INTO orders (user_id, product_id, quantity)  
  VALUES (user_id, product_id, quantity);  

  UPDATE products  
  SET stock = stock - quantity  
  WHERE id = product_id;  

  COMMIT;  
END;  

💡 就像咖啡师做拿铁：必须同时完成"取杯、加咖啡、加奶"三步，少一步就倒掉重做

场景2：批量数据处理（性能优化）

CREATE PROCEDURE batch_update_prices(  
  IN increase_rate DECIMAL(5,2)  
)  
BEGIN  
  UPDATE products  
  SET price = price * (1 + increase_rate)  
  WHERE category = '咖啡豆';  
END;  

幽默调用：

CALL batch_update_prices(0.15); -- 咖啡豆涨价15%，打工人泪目！  

场景3：数据迁移与归档

CREATE PROCEDURE archive_old_orders()  
BEGIN  
  INSERT INTO orders_archive  
  SELECT * FROM orders  
  WHERE order_date < DATE_SUB(NOW(), INTERVAL 2 YEAR);  

  DELETE FROM orders  
  WHERE order_date < DATE_SUB(NOW(), INTERVAL 2 YEAR);  
END;  

💡 就像定期清理咖啡店的过期豆子，但先拍照存档以防纠纷

---

课后彩蛋：黑科技冷知识

• MySQL的存储过程最早在5.0版本支持，曾被吐槽"难用得像用脚写的"
• Oracle数据库的触发器可以控制到语句级（STATEMENT）和行级（ROW）
• 某程序员用触发器实现数据库自动回复情人节祝福，结果被女友发现后差点分手

现在你已经成为"数据库自动化大师"！下一章我们将进入《动态 SQL 与预编译语句——数据库的"变形金刚"和"防弹咖啡"》的玄学领域，记得给你的数据库准备防弹咖啡——代码复杂度即将爆炸！ 💥☕️