在权限管理系统中，用户的新增、角色调整、状态变更等场景都需要同步更新对应的权限配置，传统手动修改或者应用层主动调用的方式不仅开发成本高，还容易出现权限更新不及时的问题。借助SQL触发器的特性，可以在用户表发生数据变更时自动执行权限分配逻辑，实现权限的动态同步。
SQL触发器基础原理
SQL触发器是数据库的一种特殊对象，当指定表发生INSERT、UPDATE、DELETE操作时，会自动执行预先定义好的逻辑。触发器可以访问操作前后的数据，因此非常适合用来处理数据变更后的关联操作，比如用户表变更后自动更新权限表。
触发器通常包含几个核心要素：
- 触发事件：指定触发器的操作类型，比如INSERT、UPDATE、DELETE，也可以组合使用
- 触发时机：分为BEFORE和AFTER两种，BEFORE是在数据操作执行前触发，AFTER是在数据操作执行后触发
- 触发逻辑：触发器要执行的具体SQL语句，用来完成权限分配等相关操作
动态权限分配场景设计
假设我们有如下两张核心表：
| 表名 | 作用 | 核心字段 |
|---|---|---|
| user_info | 存储用户基础信息 | user_id（用户ID）、user_role（用户角色）、user_status（用户状态：1正常 0禁用） |
| user_permission | 存储用户权限配置 | id（主键）、user_id（用户ID）、permission_code（权限编码）、create_time（创建时间） |
我们需要实现三个场景的自动权限分配：
- 新增用户时，根据用户的初始角色自动分配对应基础权限
- 用户角色变更时，清空原有角色权限，分配新角色的对应权限
- 用户状态变为禁用时，自动清空该用户的所有权限
MySQL环境触发器实现
以下以MySQL 5.7及以上版本为例，演示触发器的编写方式。
1. 新增用户自动分配权限触发器
当用户表新增记录时，根据user_role字段的值自动插入对应的权限记录到user_permission表。
-- 创建新增用户触发器
DELIMITER //
CREATE TRIGGER tr_user_insert_after
AFTER INSERT ON user_info
FOR EACH ROW
BEGIN
-- 根据新用户的角色分配对应权限
IF NEW.user_role = 'admin' THEN
-- 管理员角色分配所有权限
INSERT INTO user_permission (user_id, permission_code, create_time)
VALUES (NEW.user_id, 'perm_user_manage', NOW()),
(NEW.user_id, 'perm_order_manage', NOW()),
(NEW.user_id, 'perm_goods_manage', NOW());
ELSEIF NEW.user_role = 'normal' THEN
-- 普通用户分配基础查看权限
INSERT INTO user_permission (user_id, permission_code, create_time)
VALUES (NEW.user_id, 'perm_order_view', NOW()),
(NEW.user_id, 'perm_goods_view', NOW());
END IF;
END //
DELIMITER ;
2. 用户角色变更自动更新权限触发器
当用户表的user_role字段发生更新时，先删除该用户原有角色对应的权限，再插入新角色的权限。
-- 创建用户角色更新触发器
DELIMITER //
CREATE TRIGGER tr_user_role_update_after
AFTER UPDATE ON user_info
FOR EACH ROW
BEGIN
-- 仅当角色发生变更时执行逻辑
IF OLD.user_role != NEW.user_role THEN
-- 先删除该用户原有的所有权限
DELETE FROM user_permission WHERE user_id = NEW.user_id;
-- 根据新角色分配权限
IF NEW.user_role = 'admin' THEN
INSERT INTO user_permission (user_id, permission_code, create_time)
VALUES (NEW.user_id, 'perm_user_manage', NOW()),
(NEW.user_id, 'perm_order_manage', NOW()),
(NEW.user_id, 'perm_goods_manage', NOW());
ELSEIF NEW.user_role = 'normal' THEN
INSERT INTO user_permission (user_id, permission_code, create_time)
VALUES (NEW.user_id, 'perm_order_view', NOW()),
(NEW.user_id, 'perm_goods_view', NOW());
END IF;
END IF;
END //
DELIMITER ;
3. 用户禁用自动清空权限触发器
当用户状态从正常变为禁用时，自动清空该用户的所有权限记录。
-- 创建用户状态更新触发器
DELIMITER //
CREATE TRIGGER tr_user_status_update_after
AFTER UPDATE ON user_info
FOR EACH ROW
BEGIN
-- 仅当用户状态从正常变为禁用时执行
IF OLD.user_status = 1 AND NEW.user_status = 0 THEN
DELETE FROM user_permission WHERE user_id = NEW.user_id;
END IF;
END //
DELIMITER ;
触发器使用注意事项
虽然触发器可以简化权限分配的流程，但使用时需要注意以下几点：
- 触发器逻辑不宜过于复杂，避免数据操作时耗时过长，影响数据库性能
- 不同数据库的触发器语法存在差异，比如SQL Server、Oracle的触发器编写方式和MySQL不同，需要根据实际使用的数据库调整语法
- 触发器的执行是隐式的，排查问题时需要额外关注触发器的逻辑，避免隐藏的bug影响数据一致性
- 如果权限分配逻辑需要调用外部接口或者做复杂的业务判断，建议还是放在应用层实现，触发器更适合简单的数据关联操作
测试验证
我们可以执行以下测试语句验证触发器的效果：
-- 测试新增用户 INSERT INTO user_info (user_id, user_role, user_status) VALUES (1001, 'admin', 1); -- 查询权限表，应该能看到1001用户的三个管理员权限 -- 测试角色变更 UPDATE user_info SET user_role = 'normal' WHERE user_id = 1001; -- 查询权限表，1001用户的管理员权限被清空，新增两个普通用户权限 -- 测试用户禁用 UPDATE user_info SET user_status = 0 WHERE user_id = 1001; -- 查询权限表，1001用户的所有权限被清空
通过以上触发器的配置，就可以实现用户表变更时权限的自动分配和清理，减少应用层的开发工作量，同时保证权限更新的及时性。