触发器与事件
触发器与事件
生活类比
想象一个智能家居系统：
- 触发器就像"当有人按门铃时，自动打开门廊灯"——某个事件发生时，自动执行预设的动作。
- 事件调度器就像"每天早上7点，自动打开窗帘"——按照时间计划，定期执行任务。
核心概念
什么是触发器
触发器（Trigger）是与表关联的数据库对象，当表上发生特定事件时自动执行：
- 在数据插入（INSERT）、更新（UPDATE）或删除（DELETE）前后触发
- 可以访问被修改的数据（NEW 和 OLD 引用）
- 用于实现复杂的业务规则、审计日志、数据完整性约束
触发时机：BEFORE vs AFTER
| 时机 | 说明 | 典型用途 |
|---|---|---|
| BEFORE | 在操作执行前触发 | 数据验证、自动修改值 |
| AFTER | 在操作执行后触发 | 审计日志、级联更新 |
触发事件类型
- INSERT：插入新记录时触发
- UPDATE：更新记录时触发
- DELETE：删除记录时触发
NEW 和 OLD 引用
在触发器中可以访问被操作的数据：
| 事件 | NEW | OLD |
|---|---|---|
| INSERT | 新插入的行 | 不可用 |
| UPDATE | 更新后的值 | 更新前的值 |
| DELETE | 不可用 | 被删除的行 |
SQL
-- SQLite 示例：访问 NEW 和 OLD
-- NEW.column_name：引用新数据
-- OLD.column_name：引用旧数据
触发器的典型使用场景
- 审计日志：记录谁在什么时间修改了什么数据
- 级联更新：自动更新关联表的数据
- 数据验证：在数据写入前进行复杂的业务规则验证
- 自动计算：自动计算派生字段的值
- 维护冗余数据：同步更新汇总表或缓存表
扩展知识：其他数据库的事件调度器 EVENT SCHEDULER
⚠️ 注意：SQLite 不支持事件调度器。以下内容为扩展知识，展示 MySQL 的事件调度器语法作为参考。
事件调度器允许按照时间计划自动执行任务：
SQL
-- MySQL 事件调度器示例
-- 启用事件调度器
SET GLOBAL event_scheduler = ON;
-- 创建定时事件：每天凌晨清理过期数据
CREATE EVENT cleanup_expired_orders
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO
DELETE FROM orders
WHERE status = 'expired'
AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
-- 创建一次性事件
CREATE EVENT one_time_report
ON SCHEDULE AT '2024-12-31 23:59:59'
DO
INSERT INTO annual_report (year, total_sales)
SELECT YEAR(NOW()), SUM(amount) FROM orders WHERE YEAR(created_at) = YEAR(NOW());
-- 查看所有事件
SHOW EVENTS;
-- 禁用事件
ALTER EVENT cleanup_expired_orders DISABLE;
-- 删除事件
DROP EVENT IF EXISTS cleanup_expired_orders;
基本语法
SQLite 触发器语法
SQLite 支持触发器，语法如下：
SQL
-- 创建触发器基本语法
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
-- 触发器逻辑
END;
MySQL 触发器语法
SQL
-- MySQL 触发器语法
DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
-- 触发器逻辑
-- 使用 NEW 和 OLD 引用数据
END //
DELIMITER ;
PostgreSQL 触发器语法
SQL
-- PostgreSQL 需要先创建触发器函数
CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
-- 触发器逻辑
RETURN NEW; -- 或 RETURN OLD
END;
$$ LANGUAGE plpgsql;
-- 然后创建触发器
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function_name();
💡 提示：触发器会增加数据库操作的复杂度，应谨慎使用，避免创建过于复杂的触发器逻辑。
示例
示例：创建审计日志触发器
创建一个触发器，自动记录员工薪资的修改历史。
SQL
-- SQLite 版本
-- 首先创建审计日志表
CREATE TABLE IF NOT EXISTS salary_audit_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee_id INTEGER,
old_salary REAL,
new_salary REAL,
changed_by TEXT,
changed_at TEXT DEFAULT (datetime('now', 'localtime')),
action TEXT
);
-- 创建触发器：记录薪资更新
CREATE TRIGGER audit_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
WHEN OLD.salary != NEW.salary
BEGIN
INSERT INTO salary_audit_log (
employee_id,
old_salary,
new_salary,
changed_by,
action
)
VALUES (
NEW.id,
OLD.salary,
NEW.salary,
'system', -- 实际应用中可使用当前用户
'UPDATE'
);
END;
-- 测试触发器
UPDATE employees SET salary = 12000 WHERE id = 1;
-- 查看审计日志
SELECT * FROM salary_audit_log;
示例：BEFORE INSERT 触发器自动填充数据
创建一个触发器，在插入订单时自动设置订单日期和初始状态。
SQL
-- 创建订单表
CREATE TABLE IF NOT EXISTS orders_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_name TEXT NOT NULL,
product_id INTEGER,
quantity INTEGER DEFAULT 1,
order_date TEXT,
status TEXT DEFAULT 'pending',
created_at TEXT
);
-- 创建 BEFORE INSERT 触发器
CREATE TRIGGER set_order_defaults
BEFORE INSERT ON orders_new
FOR EACH ROW
BEGIN
-- 自动设置订单日期为当前日期
SET NEW.order_date = COALESCE(NEW.order_date, date('now', 'localtime'));
-- 自动设置创建时间
SET NEW.created_at = COALESCE(NEW.created_at, datetime('now', 'localtime'));
-- 如果数量为空或0，默认设为1
SET NEW.quantity = CASE WHEN NEW.quantity IS NULL OR NEW.quantity <= 0 THEN 1 ELSE NEW.quantity END;
END;
-- 测试：插入时不指定 order_date 和 status
INSERT INTO orders_new (customer_name, product_id, quantity) VALUES ('张三', 1, 3);
-- 验证自动填充的结果
SELECT * FROM orders_new;
预期输出：
TEXT
id customer_name product_id quantity order_date status created_at
1 张三 1 3 2026-06-28 pending 2026-06-28 10:30:00
💡 SQLite 限制：SQLite 的触发器语法与 MySQL/PostgreSQL 有所不同。MySQL 支持
SET NEW.column = value，而 PostgreSQL 需要使用 NEW.column := value 在 BEFORE 触发器中修改新值。
应用场景
场景1：自动更新订单汇总表
SQL
-- SQLite 版本
-- 创建订单汇总表
CREATE TABLE IF NOT EXISTS order_summary (
customer_id INTEGER PRIMARY KEY,
total_orders INTEGER DEFAULT 0,
total_amount REAL DEFAULT 0,
last_order_date TEXT
);
-- 创建触发器：插入订单时自动更新汇总
CREATE TRIGGER update_order_summary_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT OR REPLACE INTO order_summary (customer_id, total_orders, total_amount, last_order_date)
SELECT
NEW.customer_id,
COALESCE(os.total_orders, 0) + 1,
COALESCE(os.total_amount, 0) + NEW.amount,
NEW.order_date
FROM (SELECT 1) AS dummy
LEFT JOIN order_summary os ON os.customer_id = NEW.customer_id;
END;
-- 创建触发器：删除订单时自动更新汇总
CREATE TRIGGER update_order_summary_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
UPDATE order_summary
SET
total_orders = total_orders - 1,
total_amount = total_amount - OLD.amount
WHERE customer_id = OLD.customer_id;
-- 如果订单数为0，删除汇总记录
DELETE FROM order_summary
WHERE customer_id = OLD.customer_id AND total_orders <= 0;
END;
场景2：数据验证与自动填充
SQL
-- SQLite 版本：自动计算订单总价
CREATE TRIGGER calculate_order_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
-- 自动设置创建时间
SELECT NEW.created_at IS NULL THEN
SET NEW.created_at = datetime('now', 'localtime');
END IF;
-- 验证金额必须为正数
IF NEW.amount < 0 THEN
SELECT RAISE(ABORT, '订单金额不能为负数');
END IF;
END;
❓ 常见问题
Q：触发器会影响数据库性能吗？ A： 会的。每次触发器激活都会执行额外的SQL操作，大量触发器或复杂逻辑会降低写入性能。建议保持触发器逻辑简洁，避免在触发器中执行耗时操作。
Q：一个表可以有多个触发器吗？ A： SQLite 对同一事件和时机只允许一个触发器。MySQL 和 PostgreSQL 允许多个触发器，可以通过 FOLLOWS/PRECEDES 指定执行顺序。
Q：触发器中可以访问其他表吗？ A： 可以。触发器中可以查询和修改其他表的数据，但要注意避免循环触发和死锁问题。
Q：如何调试触发器？ A： 可以在触发器中插入临时测试数据到日志表，查看执行流程。MySQL 可以使用 SIGNAL 语句抛出自定义错误信息。
📖 小节
本课我们学习了：
- 触发器的概念和工作机制
- BEFORE 和 AFTER 触发时机的区别
- NEW 和 OLD 引用的使用方法
- SQLite 触发器的语法和示例
- 触发器的典型使用场景（审计、级联更新、数据验证）
- 事件调度器的概念（MySQL 扩展知识）
📝 作业
-
基础练习：创建一个触发器，当删除员工时，自动将其信息备份到
employees_backup表中。 -
进阶练习：创建一个触发器系统，实现以下功能：
- 当插入新订单时，自动减少产品库存
- 当库存不足时，阻止订单插入并提示错误
-
思考题：触发器和应用程序代码都可以实现业务逻辑，各自适用于什么场景？如何选择？
