触发器和权限管理
- 一、触发器
- 1、如何操作触发器
- 2、触发器的优缺点
- 二、权限管理
- 1、角色的作用
- 2、角色的操作
- 3、用户的操作
一、触发器
当商品信息和库存信息分别存放在两个不同的数据表中时，可以创建一个触发器， 让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。
1、如何操作触发器
创建触发器
创建触发器语法结构：
CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE}
ON 表名 FOR EACH ROW 表达式:
在创建时，你一定要注意触发器的三个要素：
- 表名：表示触发器监控的对象。
- INSERT | UPDATE | DELETE：表示触发的事件。INSERT表示插入记录时触发; UPDATE表示更新记录时触发; DELETE 表示删除记录时触发。
- BEFORE | AFTER：表示触发的时间。BEFORE表示在事件之前触发; AFTER表示在事件之后触发。
查看触发器
SHOW TRIGGERS\G;
删除触发器
DROP TRIGGER 触发器名称;
案例
超市项目实际实施过程中，客户经常要查询储值余额变动的明细，但是，查询会员消费流水时，存在数据汇总不及时、查询速度比较慢的问题。这时，我们就想到用触发器，及时把会员储值金额的变化信息记录到一个专门的表中。
DELIMITER //
CREATE TRIGGER demo.upd_membermaster BEFORE UPDATE -- 在更新前触发
ON demo.membermaster
FOR EACH ROW -- 表示每更新-条记录，触发一次
BEGIN -- 开始程序体
IF (new.memberdeposit <> old.memberdeposit) -- 如果储值金额有变化
THEN
INSERT INTO demo.deposithist
(
memberid,
transdate,
oldvalue,
newvalue,
changedvalue
)
SELECT
NEW.memberid,
NOW(),
OLD.memberdeposit, -- 更新前的储值金额
NEW.memberdeposit, -- 更新后的储值金额
NEW.memberdeposit-OLD.memberdeposit; -- 储值金额变化值
END IF;
END
DELIMITER ;
2、触发器的优缺点
优点：
-
触发器可以确保数据的完整性；
-
触发器可以帮助我们记录操作日志；利用触发器，可以具体记录什么时间发生了什么。
-
触发器可以用在操作数据前，对数据进行合法性检查；通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统。
缺点：
- 可读性差。因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能不受应用层的控制。这对系统维护是非常有挑战的。
另外，相关数据的变更，特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。
一个小建议：维护一个完整的数据库设计文档。因为运维人员可能会经常变动，如果有一个完整的数据库设计文档，就可以帮助新人快速了解触发器的设计思路，从而减少错误，降低系统维护的成本。
二、权限管理
1、角色的作用
角色是在MySQL 8.0中引入的新功能，相当于一个权限的集合。引入角色的目的是方便管理拥有相同权限的用户。
2、角色的操作
创建角色
CREATE ROLE 角色名;
-- 例如：创建一个经理的角色
CREATE ROLE 'manager'@'localhost';
这里的意思是，创建一个角色， 角色名称是"manager" ，角色可以登录的主机是"localhost"，意思是只能从数据库服务器运行的这台计算机登录这个账号。
CREATE ROLE 'manager';
-- 如果不写主机名，MySQL默认是通配符“%”，
--意思是这个账号可以从任何一台主机上登录数据库。
MySQL中创建了角色之后，默认都是没有被激活的，也就是不能用，必须要用下面的语句激活:
SET global activate_all_roles_on_login=ON;
-- 这条SQL语句的意思是，对所有角色永久激活。
给角色赋予权限
创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。
GRANT 权限 ON 表名 TO 角色名;
-- 举例
GRANT SELECT ON demo.settlement TO 'manager';
GRANT SELECT , INSERT , DELETE , UPDATE ON demo.invcount TO 'stocker'
查看角色权限
SHOW GRANTS FOR 'manager';
只要你创建了一个角色，系统就会自动给你一个"USAGE" 权限，意思是连接登录数据库的权限。
删除角色
当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。
DROP ROLE 角色名称;
3、用户的操作
我们可以把数据库中的角色看作是一个权限集。角色本身不能操作数据,这个任务还是要靠用户来完成。在操作用户前，我们先要创建一个用户。
创建用户
CREATE USER 用户名 [IDENTIFIED BY 密码];
-- 举例
CREATE USER 'zhangsan' IDENTIFIED BY 'mysql';
“[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。
给用户授权
给用户授权的方式有2种，分别是通过把角色赋予用户给用户授权，和直接给用户授权。
通过把角色赋予用户给用户授权的语法结构如下:
GRANT 角色名称 TO 用户名称;
GRANT 权限 ON 表名 TO 用户名;
查看用户权限
SHOW GRANTS FOR 用户名;
-- 举例
SHOW GRANTS FOR 'zhangsan';
删除用户
DROP USER 用户名;
