目录

前言

创建触发器

update 触发器

创建有多个执行语句的触发器

用户和权限

前言

1.MySQL触发器简介

   MySQL 数据库中触发器是一个特殊的存储过程；  

   存储过程要使用 CALL 语句来调用，而触发器的执行是事件(insert、update、delete)自动触发。

2.引发触发器执行的事件，如下：

   增加一条学生记录时，会自动检查年龄是否符合范围要求。

   每当删除一条学生信息时，自动删除其成绩表上的对应记录。

   每当删除一条数据时，在数据库存档表中保留一个备份

3.触发程序的优点，如下：

   触发程序的执行是自动的，当对触发程序相关表的数据做出相应的修改后立即执行。

   触发程序可以通过数据库中相关的表层叠修改另外的表。

   触发程序可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。

4.触发器的特性：

   有begin end体，begin end;之间的语句可以写的简单或者复杂

   什么条件会触发：Insert、Update、Delete

   什么时候触发：在增删改前或者后

   触发频率：针对每一行执行

   触发器定义在表上，附着在表上

5.MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器

 触发器包含两个虚拟表：new表、old表：

   insert触发器引用new表来存储被插入的行；

   update触发器引用new表存储更新后的值，引用old表存储更新前的值；

   delete触发器引用old表存储删除前的值。

====================================================================================

创建触发器

创建触发器的语法如下：

create trigger trigger_name trigger_time trigger_event ON tb_name for each row trigger_stmt

trigger_name：触发器的名称

tirgger_time：触发时机，为BEFORE或者AFTER

trigger_event：触发事件，为INSERT、DELETE或者UPDATE

tb_name：表示建立触发器的表名，就是在哪张表上建立触发器

for each row: 表示任何一条记录上的操作满足触发事件都会触发该触发器

trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

可以说MySQL创建以下六种触发器：

BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE

AFTER INSERT,AFTER DELETE,AFTER UPDATE

2.案例1：before触发器

（1）创库

create database benet;
use benet;

（2）创表

create table tb_emp8 
(id int not null primary key,
name varchar(22) not null,
deptid int not null,
salary float not null);

（3）创建求和触发器

create trigger sumofsalary 
before insert on tb_emp8
for each row
set @sum=@sum+NEW.salary;

（4）验证求和函数

set @sum=0;
insert into tb_emp8 values (1,'A',1,1000),(2,'B',2,500);
select * from tb_emp8;
select @sum;

3.案例2：alter触发器

use benet;
create table tb_emp6 like tb_emp8;
create table tb_emp7 like tb_emp8;
create trigger doubleofsalary
after insert on tb_emp6
for each row
insert into tb_emp7
values (
NEW.id,NEW.name,NEW.deptId,2*NEW.salary);
INSERT INTO tb_emp6 VALUES (1,'A',1,1000),(2,'B',1,500);
SELECT * FROM tb_emp6;
SELECT * FROM tb_emp7;

4.案例3：delete触发器

delimiter //
create trigger deny_del before delete
on tb_emp7 for each row
begin
rollback;
end //
报错，显式语句start transaction,commit,rollback在触发器语句中不识别。
create procedure rollbk() begin rollback; end//
create trigger deny_del before delete
on tb_emp7 for each row
begin
call rollbk;
end //
delimiter ;
delete from tb_emp7 where id=1;

=====================================================================

update 触发器

1.案例1

create table test(
    id int primary key,
    flg nvarchar(20)
);
insert into test values(1,'');
insert into test values(2,'');
delimiter //
CREATE TRIGGER trig_test_update
before  update  on  test for each row
BEGIN
  set new.flg='有更新';
END;
//
delimiter ;
update test set id='3' where id=2;
select * from test;

2.案例2

项目需求: 提现表,支付宝信息被修改,怎么限制, 插入的数据针对字段不被修改

create table user_withdraw 
(id int primary key,name varchar(20),alipay_num int,money int,real_money int);
insert into user_withdraw values 
(1,'zhangsan','10001','100000','200000'),
(2,'lisi',10002,300000,400000);
delimiter //
CREATE TRIGGER user_withdraw_update  BEFORE UPDATE ON user_withdraw
       FOR EACH ROW
       BEGIN
           IF OLD.alipay_num is not null THEN
               SET NEW.alipay_num = OLD.alipay_num,NEW.money= OLD.money,NEW.real_money=OLD.real_money;
           END IF;
       END;//
delimiter ;
update user_withdraw set money='30000' where id=2;
select * from user_withdraw;

=====================================================================

创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件

ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END
DELIMITER //
CREATE TRIGGER demo BEFORE DELETE
ON users FOR EACH ROW
BEGIN
INSERT INTO logs VALUES(NOW());
INSERT INTO logs VALUES(NOW());
END //
DELIMITER ;

load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。

2.用户users表

CREATE TABLE users (   
id int  NOT NULL AUTO_INCREMENT primary key,
name varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL, 
add_time varchar(20) DEFAULT NULL )  
AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4;

3.日志logs表：

CREATE TABLE `logs` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `log` varchar(255) DEFAULT NULL COMMENT '日志说明',
  PRIMARY KEY (`Id`)
)  DEFAULT CHARSET=utf8mb4 COMMENT='日志表';

4.创建触发器：当在users中插入一条数据，就会在logs中生成一条日志信息。

DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
BEGIN
declare s1 VARCHAR(40)character set utf8mb4;
declare s2 VARCHAR(20) character set utf8mb4;
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2);     
INSERT INTO logs(log) values(s1);
END $
DELIMITER ;

5.插入数据，并查看日志表

insert into users(name,add_time) values('zhangsan',now());

6.查看触发器

SHOW TRIGGERS;

SELECT * FROM information_schema.triggers;

7.删除触发器

drop trigger user_log;

8.限制和注意事项

触发器会有以下两种限制：

（1）触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。

（2）不能在触发器中使用以显示或隐式方式开始或结束事务的语句，如START TRANS-ACTION,COMMIT或ROLLBACK。

注意事项：MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作，如果对事务表进行的操作，如果出现错误，那么将会被回滚，如果是对非事务表进行操作，那么就无法回滚了，数据可能会出错。

9.总结

触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过多的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

数据库触发器有以下的作用（增强性）：

（1）安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

 # 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。

 # 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

（2）审计。可以跟踪用户对数据库的操作。  

 # 审计用户操作数据库的语句。

 # 把用户对数据库的更新写入审计表。

（3）实现复杂的数据完整性规则

 # 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。

 # 提供可变的缺省值。

（4）实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

 # 在修改或删除时级联修改或删除其它表中的与之匹配的行。

 # 在修改或删除时把其它表中的与之匹配的行设成NULL值。

 # 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

 # 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。

（5）同步实时地复制表中的数据。

（6）自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据

=========================================================================

用户和权限

select  user,host,grant_priv from mysql.user;

mysql.user表：特殊列属性

       max_questions:      用户每小时允许执行的查询操作次数    

         max_updates:        用户每小时允许执行的更新操作次数

     max_connections:         服务器每小时最大允许执行的连接操作次数

max_user_connections:         单用户允许同时建立的连接次数

mysql.db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。

select user,host,db from mysql.db;

2.创建新用户

CREATE USER 'tom'@'%' IDENTIFIED BY 'mypass';                #仅创用户
GRANT SELECT,insert,UPDATE ON world.* TO 'tom'@'%' IDENTIFIED BY '123.com';  #创用户并赋权，mysql 8.0不允许
FLUSH privileges;    #刷新权限列表

3.删除用户

DROP USER tom@'%';
或
DELETE FROM mysql.user WHERE host='%' and user='tom';

4.修改用户密码

mysqladmin -u root -p password "rootpassword"
GRANT USAGE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY '123.com';
update mysql.user set password=password("rootpwd") where user="root" and host="localhost";
SET PASSWORD=PASSWORD("123.com");  #默认修改root账户密码
SET PASSWORD FOR 'testUser'@'localhost'=PASSWORD("newped");  #修改普通用户密码

5.常用权限

create        创建

drop        删除

grant option    赋权(超级管理员权限，谨慎使用)

references    外键

event        事件

alter        修改

delete        删除

index        索引

insert        插入

select        查看

update        更新

lock tables    锁表

trigger        触发器

create view    视图

file        文件

reload        重载

shutdown    关闭

process        进程

create user    创建用户

REPLICATION SLAVE 主从复制

6.撤权

REVOKE INSERT ON *.* FROM 'tom'@'%';

7.查看权限

SHOW GRANTS FOR 'tom'@'%'\G