1. 触发器基础概念与核心原理
1.1 什么是SQL触发器
SQL触发器（Trigger）是一种特殊的存储过程，它会在指定的数据库事件（INSERT、UPDATE、DELETE）发生时自动执行。触发器与特定的表关联，当该表发生数据变更操作时，触发器会被激活执行预定义的操作逻辑。
核心特性：
自动执行
：无需手动调用，事件触发即执行
事件驱动
：基于DML操作（INSERT/UPDATE/DELETE）
事务绑定
：与触发它的SQL语句在同一事务中
访问新旧数据
：可以访问操作前后的数据值
1.2 触发器的类型
1.2.1 按触发时机分类
BEFORE触发器
：在数据操作执行前触发，可用于验证或修改数据
AFTER触发器
：在数据操作执行后触发，常用于审计、级联操作
1.2.2 按触发事件分类
INSERT触发器
：在插入数据时触发
UPDATE触发器
：在更新数据时触发
DELETE触发器
：在删除数据时触发
1.2.3 按触发频率分类
行级触发器
：对受影响的每一行都执行一次
语句级触发器
：每个SQL语句只执行一次
2. 触发器的语法结构详解
2.1 标准语法模板
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW] — 行级触发器
[WHEN condition] — 触发条件
BEGIN
— 触发器主体逻辑
— 可以使用 NEW 和 OLD 关键字访问数据
END;
2.2 关键字说明
NEW
：代表新插入或更新后的数据行（INSERT和UPDATE可用）
OLD
：代表更新前或删除前的数据行（UPDATE和DELETE可用）
NEW.column_name
：访问新数据的特定列
OLD.column_name
：访问旧数据的特定列
2.3 不同数据库的语法差异
MySQL语法示例
DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
— 检查薪资是否降低
IF NEW.salary < OLD.salary THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '薪资不能降低！';
END IF;
END$$
DELIMITER ;
PostgreSQL语法示例
CREATE OR REPLACE FUNCTION check_salary_update()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.salary < OLD.salary THEN
RAISE EXCEPTION '薪资不能降低！';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary_update();
SQL Server语法示例
CREATE TRIGGER trg_employee_update
ON employees
FOR UPDATE
AS
BEGIN
IF EXISTS (
SELECT 1 FROM inserted i
JOIN deleted d ON i.employee_id = d.employee_id
WHERE i.salary < d.salary
)
BEGIN
RAISERROR('薪资不能降低！', 16, 1);
ROLLBACK TRANSACTION;
END
END;
3. 实战场景一：数据完整性保障
3.1 场景描述
业务需求
：在员工管理系统中，确保员工的入职日期不能晚于当前日期，且薪资不能低于公司最低标准。
3.2 MySQL实现方案
-- 创建测试表
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
hire_date DATE NOT NULL,
salary DECIMAL(10,2) NOT NULL,
department VARCHAR(50)
);
-- 创建BEFORE INSERT触发器
DELIMITER $$
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
DECLARE min_salary DECIMAL(10,2) DEFAULT 3000.00;
-- 验证入职日期
IF NEW.hire_date > CURDATE() THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘入职日期不能晚于当前日期！’;
END IF;
— 验证薪资标准
IF NEW.salary < min_salary THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '薪资不能低于最低标准3000元！';
END IF;
END$$
DELIMITER ;
3.3 测试验证
-- 测试1：正常插入（应该成功）
INSERT INTO employees (name, hire_date, salary, department)
VALUES ('张三', '2024-01-15', 5000.00, '技术部');
-- 测试2：入职日期错误（应该失败）
INSERT INTO employees (name, hire_date, salary, department)
VALUES ('李四', '2024-12-31', 5000.00, '技术部');
-- 预期错误：入职日期不能晚于当前日期！
-- 测试3：薪资过低（应该失败）
INSERT INTO employees (name, hire_date, salary, department)
VALUES ('王五', '2024-01-15', 2500.00, '技术部');
-- 预期错误：薪资不能低于最低标准3000元！
3.4 PostgreSQL实现方案
-- 创建触发器函数
CREATE OR REPLACE FUNCTION validate_employee_insert()
RETURNS TRIGGER AS $$
DECLARE
min_salary DECIMAL(10,2) := 3000.00;
BEGIN
-- 验证入职日期
IF NEW.hire_date > CURRENT_DATE THEN
RAISE EXCEPTION ‘入职日期不能晚于当前日期！’;
END IF;
— 验证薪资标准
IF NEW.salary < min_salary THEN
RAISE EXCEPTION '薪资不能低于最低标准%元！', min_salary;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 创建触发器
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_employee_insert();
4. 实战场景二：自动审计日志
4.1 场景描述
业务需求
：记录所有员工表的变更操作，包括变更时间、操作类型、变更前后的数据，用于安全审计和数据追溯。
4.2 MySQL实现方案
-- 创建审计日志表
CREATE TABLE employee_audit_log (
log_id INT PRIMARY KEY AUTO_INCREMENT,
operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
employee_id INT,
old_data JSON,
new_data JSON,
changed_by VARCHAR(100),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建AFTER INSERT触发器
DELIMITER $$
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit_log (
operation_type,
employee_id,
new_data,
changed_by
) VALUES (
'INSERT',
NEW.employee_id,
JSON_OBJECT(
'name', NEW.name,
'hire_date', NEW.hire_date,
'salary', NEW.salary,
'department', NEW.department
),
USER()
);
END$$
-- 创建AFTER UPDATE触发器
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit_log (
operation_type,
employee_id,
old_data,
new_data,
changed_by
) VALUES (
'UPDATE',
NEW.employee_id,
JSON_OBJECT(
'name', OLD.name,
'hire_date', OLD.hire_date,
'salary', OLD.salary,
'department', OLD.department
),
JSON_OBJECT(
'name', NEW.name,
'hire_date', NEW.hire_date,
'salary', NEW.salary,
'department', NEW.department
),
USER()
);
END$$
-- 创建AFTER DELETE触发器
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit_log (
operation_type,
employee_id,
old_data,
changed_by
) VALUES (
'DELETE',
OLD.employee_id,
JSON_OBJECT(
'name', OLD.name,
'hire_date', OLD.hire_date,
'salary', OLD.salary,
'department', OLD.department
),
USER()
);
END$$
DELIMITER ;
4.3 测试验证
-- 执行数据操作
INSERT INTO employees (name, hire_date, salary, department)
VALUES ('赵六', '2024-02-01', 6000.00, '市场部');
UPDATE employees
SET salary = 6500.00
WHERE name = '赵六';
DELETE FROM employees
WHERE name = '赵六';
-- 查看审计日志
SELECT * FROM employee_audit_log;
预期结果：
log_id | operation_type | employee_id | old_data | new_data | changed_by | changed_at
-------|----------------|-------------|----------|----------|------------|-----------
1 | INSERT | 1 | NULL | {...} | root@localhost | 2024-01-15 10:00:00
2 | UPDATE | 1 | {...} | {...} | root@localhost | 2024-01-15 10:00:01
3 | DELETE | 1 | {...} | NULL | root@localhost | 2024-01-15 10:00:02
4.4 PostgreSQL实现方案
-- 创建审计日志表
CREATE TABLE employee_audit_log (
log_id SERIAL PRIMARY KEY,
operation_type VARCHAR(10) NOT NULL,
employee_id INTEGER,
old_data JSONB,
new_data JSONB,
changed_by VARCHAR(100),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建通用审计函数
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO employee_audit_log (
operation_type, employee_id, new_data, changed_by
) VALUES (
'INSERT', NEW.employee_id, row_to_json(NEW), CURRENT_USER
);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO employee_audit_log (
operation_type, employee_id, old_data, new_data, changed_by
) VALUES (
'UPDATE', NEW.employee_id, row_to_json(OLD), row_to_json(NEW), CURRENT_USER
);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO employee_audit_log (
operation_type, employee_id, old_data, changed_by
) VALUES (
'DELETE', OLD.employee_id, row_to_json(OLD), CURRENT_USER
);
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
-- 创建触发器（支持三种操作）
CREATE TRIGGER employee_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_employee_changes();
5. 实战场景三：级联更新与数据同步
5.1 场景描述
业务需求
：当员工表中的部门信息变更时，自动更新部门表中的员工数量统计；当删除员工时，自动减少部门员工数量。
5.2 MySQL实现方案
-- 创建部门表
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(100) NOT NULL,
employee_count INT DEFAULT 0,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 初始化部门数据
INSERT INTO departments (dept_name) VALUES ('技术部'), ('市场部'), ('人事部');
-- 创建UPDATE触发器
DELIMITER $$
CREATE TRIGGER after_employee_update_dept
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
-- 如果部门发生变化
IF OLD.department != NEW.department THEN
-- 减少原部门计数
UPDATE departments
SET employee_count = employee_count - 1
WHERE dept_name = OLD.department;
-- 增加新部门计数
UPDATE departments
SET employee_count = employee_count + 1
WHERE dept_name = NEW.department;
END IF;
END$$
-- 创建DELETE触发器
CREATE TRIGGER after_employee_delete_count
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
UPDATE departments
SET employee_count = employee_count - 1
WHERE dept_name = OLD.department;
END$$
-- 创建INSERT触发器
CREATE TRIGGER after_employee_insert_count
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
UPDATE departments
SET employee_count = employee_count + 1
WHERE dept_name = NEW.department;
END$$
DELIMITER ;
5.3 测试验证
-- 查看初始状态
SELECT * FROM departments;
-- 预期：employee_count都为0
-- 插入员工
INSERT INTO employees (name, hire_date, salary, department) VALUES
('张三', '2024-01-15', 5000.00, '技术部'),
('李四', '2024-01-15', 5500.00, '技术部'),
('王五', '2024-01-15', 6000.00, '市场部');
-- 查看部门计数
SELECT * FROM departments;
-- 预期：技术部=2，市场部=1，人事部=0
-- 更新员工部门
UPDATE employees SET department = '市场部' WHERE name = '张三';
-- 查看部门计数
SELECT * FROM departments;
-- 预期：技术部=1，市场部=2，人事部=0
-- 删除员工
DELETE FROM employees WHERE name = '李四';
-- 查看部门计数
SELECT * FROM departments;
-- 预期：技术部=0，市场部=2，人事部=0
5.4 PostgreSQL实现方案
-- 创建部门表
CREATE TABLE departments (
dept_id SERIAL PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL UNIQUE,
employee_count INTEGER DEFAULT 0,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 初始化部门数据
INSERT INTO departments (dept_name) VALUES ('技术部'), ('市场部'), ('人事部');
-- 创建通用处理函数
CREATE OR REPLACE FUNCTION update_department_count()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE departments
SET employee_count = employee_count + 1
WHERE dept_name = NEW.department;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
IF OLD.department != NEW.department THEN
UPDATE departments
SET employee_count = employee_count - 1
WHERE dept_name = OLD.department;
UPDATE departments
SET employee_count = employee_count + 1
WHERE dept_name = NEW.department;
END IF;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE departments
SET employee_count = employee_count - 1
WHERE dept_name = OLD.department;
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
-- 创建触发器
CREATE TRIGGER employee_count_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_department_count();
6. 实战场景四：复杂业务规则验证
6.1 场景描述
业务需求
：在电商系统中，当用户下单时，需要验证：
库存是否充足
用户余额是否足够
订单金额是否超过用户信用额度
自动计算订单总价和优惠
6.2 MySQL实现方案
-- 创建相关表
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
stock_quantity INT,
price DECIMAL(10,2)
);
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(100),
balance DECIMAL(10,2),
credit_limit DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
product_id INT,
quantity INT,
total_price DECIMAL(10,2),
discount DECIMAL(10,2),
final_price DECIMAL(10,2),
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 初始化测试数据
INSERT INTO products VALUES (1, '笔记本电脑', 10, 5000.00);
INSERT INTO users VALUES (1, 'user1', 8000.00, 10000.00);
-- 创建BEFORE INSERT触发器
DELIMITER $$
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE product_price DECIMAL(10,2);
DECLARE product_stock INT;
DECLARE user_balance DECIMAL(10,2);
DECLARE user_credit DECIMAL(10,2);
DECLARE subtotal DECIMAL(10,2);
DECLARE discount_rate DECIMAL(5,2) DEFAULT 0.00;
-- 获取商品信息
SELECT price, stock_quantity INTO product_price, product_stock
FROM products WHERE product_id = NEW.product_id;
-- 获取用户信息
SELECT balance, credit_limit INTO user_balance, user_credit
FROM users WHERE user_id = NEW.user_id;
-- 验证库存
IF product_stock < NEW.quantity THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '库存不足！';
END IF;
-- 计算小计
SET subtotal = product_price * NEW.quantity;
-- 验证用户余额和信用额度
IF user_balance + user_credit < subtotal THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '余额和信用额度不足！';
END IF;
-- 计算优惠（满5000减500）
IF subtotal >= 5000 THEN
SET discount_rate = 0.10; — 10%折扣
END IF;
— 更新订单字段
SET NEW.total_price = subtotal;
SET NEW.discount = subtotal * discount_rate;
SET NEW.final_price = subtotal – NEW.discount;
END$$
DELIMITER ;
6.3 测试验证
— 测试1：正常下单
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 1, 1);
— 预期：total_price=5000, discount=500, final_price=4500
— 测试2：库存不足
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 1, 20);
— 预期错误：库存不足！
— 测试3：余额不足
UPDATE users SET balance = 1000 WHERE user_id = 1;
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 1, 2);
— 预期错误：余额和信用额度不足！
7. 高级技巧与最佳实践
7.1 触发器的性能优化
7.1.1 避免在触发器中执行耗时操作
— ❌ 错误示例：在触发器中调用外部API
CREATE TRIGGER bad_example
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
— 不要在触发器中做HTTP请求
CALL external_api_call(NEW.order_id); — 严重性能问题！
END;
— ✅ 正确做法：使用消息队列或异步处理
— 将任务放入队列表，由后台任务处理
INSERT INTO task_queue (task_type, task_data)
VALUES (‘SEND_NOTIFICATION’, JSON_OBJECT(‘order_id’, NEW.order_id));
7.1.2 控制触发器数量
— ✅ 最佳实践：合并相关触发器
— 将多个审计逻辑合并到一个触发器中
CREATE TRIGGER combined_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
— INSERT逻辑
END IF;
IF UPDATING THEN
— UPDATE逻辑
END IF;
IF DELETING THEN
— DELETE逻辑
END IF;
END;
7.2 触发器的调试技巧
7.2.1 使用临时日志表
— 创建调试日志表
CREATE TABLE trigger_debug_log (
id INT AUTO_INCREMENT PRIMARY KEY,
trigger_name VARCHAR(100),
debug_message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
— 在触发器中添加调试信息
DELIMITER $$
CREATE TRIGGER debug_example
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO trigger_debug_log (trigger_name, debug_message)
VALUES (‘debug_example’,
CONCAT(‘Processing employee: ‘, NEW.name, ‘ Salary: ‘, NEW.salary));
— 你的业务逻辑…
END$$
DELIMITER ;
7.2.2 使用条件编译（MySQL 8.0+）
— 通过变量控制调试模式
SET @debug_mode = TRUE;
CREATE TRIGGER conditional_debug
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF @debug_mode THEN
INSERT INTO trigger_debug_log (trigger_name, debug_message)
VALUES (‘conditional_debug’, ‘Trigger fired’);
END IF;
END;
7.3 触发器的版本管理
— 创建触发器元数据表
CREATE TABLE trigger_metadata (
trigger_name VARCHAR(100) PRIMARY KEY,
version INT,
description TEXT,
created_by VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);
— 示例：记录触发器版本
INSERT INTO trigger_metadata (trigger_name, version, description, created_by)
VALUES (‘before_employee_insert’, 1, ‘员工数据验证触发器’, ‘DBA团队’);
8. 触发器的限制与注意事项
8.1 常见限制
8.1.1 递归触发器限制
— ❌ 错误：递归触发器
CREATE TRIGGER recursive_trigger
AFTER UPDATE ON table1
FOR EACH ROW
BEGIN
UPDATE table1 SET col = col + 1 WHERE id = NEW.id; — 无限递归！
END;
— ✅ 正确：使用条件避免递归
CREATE TRIGGER safe_trigger
AFTER UPDATE ON table1
FOR EACH ROW
BEGIN
IF NEW.col != OLD.col THEN — 防止递归
— 业务逻辑
END IF;
END;
8.1.2 触发器中的事务限制
— 注意：触发器与主SQL在同一事务中
BEGIN;
INSERT INTO employees (name, salary) VALUES (‘测试’, 5000);
— 如果触发器失败，整个事务回滚
COMMIT; — 或 ROLLBACK;
8.2 触发器的维护
8.2.1 查看触发器定义
— MySQL
SHOW TRIGGERS;
SELECT * FROM information_schema.triggers WHERE trigger_name = ‘your_trigger’;
— PostgreSQL
SELECT * FROM information_schema.triggers WHERE trigger_name = ‘your_trigger’;
— SQL Server
SELECT * FROM sys.triggers WHERE name = ‘your_trigger’;
8.2.2 禁用/启用触发器
— MySQL
ALTER TABLE employees DISABLE KEYS;
ALTER TABLE employees ENABLE KEYS;
— PostgreSQL
ALTER TABLE employees DISABLE TRIGGER your_trigger;
ALTER TABLE employees ENABLE TRIGGER your_trigger;
— SQL Server
DISABLE TRIGGER your_trigger ON employees;
ENABLE TRIGGER your_trigger ON employees;
8.2.3 删除触发器
DROP TRIGGER IF EXISTS your_trigger;
9. 实战案例：完整的员工管理系统
9.1 完整的触发器集合
— ============================================
— 完整的员工管理系统触发器
— ============================================
— 1. 数据验证触发器（BEFORE INSERT/UPDATE）
DELIMITER $$
CREATE TRIGGER trg_validate_employee
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
— 验证薪资范围
IF NEW.salary < 3000 OR NEW.salary > 100000 THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘薪资必须在3000-100000之间！’;
END IF;
— 验证入职日期
IF NEW.hire_date > CURDATE() THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘入职日期不能晚于当前日期！’;
END IF;
— 验证邮箱格式（简单验证）
IF NEW.email IS NOT NULL AND NEW.email NOT LIKE ‘%_@_%._%’ THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘邮箱格式不正确！’;
END IF;
END$$
— 2. 自动计算工龄（BEFORE INSERT/UPDATE）
CREATE TRIGGER trg_calculate_service_years
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
SET NEW.service_years = TIMESTAMPDIFF(YEAR, NEW.hire_date, CURDATE());
END$$
— 3. 审计日志（AFTER ALL）
CREATE TRIGGER trg_audit_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
DECLARE operation VARCHAR(10);
DECLARE old_data JSON;
DECLARE new_data JSON;
IF INSERTING THEN
SET operation = ‘INSERT’;
SET old_data = NULL;
SET new_data = JSON_OBJECT(
’employee_id’, NEW.employee_id,
‘name’, NEW.name,
‘salary’, NEW.salary,
‘department’, NEW.department
);
ELSIF UPDATING THEN
SET operation = ‘UPDATE’;
SET old_data = JSON_OBJECT(
’employee_id’, OLD.employee_id,
‘name’, OLD.name,
‘salary’, OLD.salary,
‘department’, OLD.department
);
SET new_data = JSON_OBJECT(
’employee_id’, NEW.employee_id,
‘name’, NEW.name,
‘salary’, NEW.salary,
‘department’, NEW.department
);
ELSE
SET operation = ‘DELETE’;
SET old_data = JSON_OBJECT(
’employee_id’, OLD.employee_id,
‘name’, OLD.name,
‘salary’, OLD.salary,
‘department’, OLD.department
);
SET new_data = NULL;
END IF;
INSERT INTO employee_audit_log (
operation_type, employee_id, old_data, new_data, changed_by
) VALUES (operation, COALESCE(NEW.employee_id, OLD.employee_id),
old_data, new_data, USER());
END$$
— 4. 部门统计更新（AFTER ALL）
CREATE TRIGGER trg_update_dept_count
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE departments
SET employee_count = employee_count + 1
WHERE dept_name = NEW.department;
ELSIF UPDATING THEN
IF OLD.department != NEW.department THEN
UPDATE departments
SET employee_count = employee_count – 1
WHERE dept_name = OLD.department;
UPDATE departments
SET employee_count = employee_count + 1
WHERE dept_name = NEW.department;
END IF;
ELSE
UPDATE departments
SET employee_count = employee_count – 1
WHERE dept_name = OLD.department;
END IF;
END$$
— 5. 薪资变更历史记录（AFTER UPDATE）
CREATE TRIGGER trg_salary_history
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_history (
employee_id, old_salary, new_salary, change_date, changed_by
) VALUES (
OLD.employee_id, OLD.salary, NEW.salary, NOW(), USER()
);
END IF;
END$$
DELIMITER ;
9.2 测试完整流程
— 准备测试数据
CREATE TABLE salary_history (
history_id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
change_date TIMESTAMP,
changed_by VARCHAR(100)
);
— 测试1：插入员工
INSERT INTO employees (name, email, hire_date, salary, department)
VALUES (‘钱七’, ‘qianqi@company.com’, ‘2024-01-15’, 8000.00, ‘技术部’);
— 测试2：更新薪资
UPDATE employees SET salary = 9000.00 WHERE name = ‘钱七’;
— 测试3：更新部门
UPDATE employees SET department = ‘市场部’ WHERE name = ‘钱七’;
— 测试4：删除员工
DELETE FROM employees WHERE name = ‘钱七’;
— 验证结果
SELECT ‘员工表’ AS 表名, COUNT(*) AS 记录数 FROM employees
UNION ALL
SELECT ‘审计日志’, COUNT(*) FROM employee_audit_log
UNION ALL
SELECT ‘薪资历史’, COUNT(*) FROM salary_history
UNION ALL
SELECT ‘部门统计’, SUM(employee_count) FROM departments;
10. 触发器的替代方案与选择建议
10.1 何时使用触发器
适合场景：
✅ 强制数据完整性规则
✅ 自动审计和日志记录
✅ 级联数据更新
✅ 复杂的业务规则验证
不适合场景：
❌ 需要调用外部服务
❌ 执行耗时操作（>1秒）
❌ 需要用户交互
❌ 业务逻辑频繁变更
10.2 替代方案对比
方案
优点
缺点
适用场景
触发器
自动执行，无法绕过
难以调试，影响性能
数据完整性、审计
应用层逻辑
易于调试和维护
可能被绕过
业务规则处理
存储过程
性能好，可重用
需要显式调用
复杂业务操作
外键约束
声明式，性能好
功能有限
简单级联操作
事件调度器
定时执行
非实时
批量处理
10.3 混合方案示例
— 场景：订单处理系统
— 1. 触发器：实时验证数据完整性
— 2. 应用层：处理业务逻辑和外部调用
— 3. 事件调度器：处理批量任务
— 触发器只做基础验证
CREATE TRIGGER trg_order_validation
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
— 只验证数据完整性，不处理业务
IF NEW.quantity <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数量必须大于0！';
END IF;
END;
-- 应用层处理复杂业务（伪代码）
/*
def create_order(user_id, product_id, quantity):
# 1. 验证库存（调用库存服务）
# 2. 计算价格（调用定价服务）
# 3. 扣减库存（调用库存服务）
# 4. 创建订单（数据库操作）
# 5. 发送通知（调用消息服务）
pass
*/
11. 总结与进阶学习路径
11.1 核心要点回顾
触发器类型
：BEFORE/AFTER, INSERT/UPDATE/DELETE, 行级/语句级
数据访问
：NEW（新数据）和 OLD（旧数据）
主要用途
：数据验证、审计日志、级联更新、自动计算
性能考虑
：避免复杂逻辑，控制触发器数量
调试技巧
：使用日志表，条件调试
11.2 常见问题排查
-- 问题1：触发器不执行
-- 检查触发器是否启用
SHOW TRIGGERS LIKE 'your_table';
-- 问题2：触发器性能慢
-- 查看触发器执行时间
SET profiling = 1;
-- 执行SQL
SHOW PROFILES;
-- 问题3：触发器递归
-- 检查触发器是否修改同一张表
SELECT * FROM information_schema.triggers
WHERE event_object_table = 'your_table';
11.3 进阶学习路径
基础阶段
：掌握触发器语法和基本操作
实战阶段
：在实际项目中应用触发器
优化阶段
：学习性能调优和最佳实践
高级阶段
：研究触发器与存储过程、事件调度器的配合使用
11.4 推荐学习资源
官方文档
：MySQL/PostgreSQL/SQL Server官方手册
实践项目
：构建完整的CRM/ERP系统
性能监控
：学习数据库性能分析工具
源码研究
：阅读开源项目的触发器实现
总结
：触发器是数据库开发中强大的工具，合理使用可以大幅提升数据质量和系统可靠性。但切记：触发器不是银弹，需要根据具体场景权衡使用。建议从简单的数据验证开始，逐步掌握复杂业务逻辑的实现，最终形成适合项目的触发器使用规范。