十三、MySQL触发器

一、创建触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

-- 创建只有一个执行语句的触发器
-- 创建触发器的语法：
CREATE TRIGGER trigger_name trigger_time trigger_event
        ON table_name FOR EACH  ROW trigger_stmt

DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `mysql_test`.`tri_01` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `mysql_test`.`student`
    FOR EACH ROW 
    BEGIN
            sql语句
    END$$
DELIMITER ;

• trigger_time:触发时机， before（在检查约束前触发）或after（在检查约束后触发）

• trigger_event:触发事件 INSERT、UPDATE、DELETE、

• table_name：建立触发器的表明，即在那张表上创建触发器

• trigger_stmt:触发器执行语句

MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程勋，触发器是由事假来触发某个操作，这些事件包括INSERT、UPDATE、DELETE语句。

1.1、简单的Insert触发器

　　假设存在一张学生表（student），包括学生的基本信息，学号（sid）为主键。

CREATE TABLE `student` (
  `sid` int(8) NOT NULL AUTO_INCREMENT,
  `sname` varchar(10) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=20200105 DEFAULT CHARSET=utf8

　　另外存在一张成绩表（score_sheet），对应每个学生包括一个值。其中number表示序号为主键，自动递增序列。它在插入过程中默认自增。同时假设成绩表中包括学生姓名和学号。

CREATE TABLE `score_sheet` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '成绩表id,主键自增',
  `stu_id` int(8) NOT NULL COMMENT '学生表id',
  `stu_name` varchar(10) NOT NULL COMMENT '学生名称',
  `english` double DEFAULT NULL COMMENT '英语成绩',
  `chinese` double DEFAULT NULL COMMENT '语文成绩',
  `match` double DEFAULT NULL COMMENT '数学成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

　　该成绩表目前没有值，先需要设计一个触发器，当增加新的学生时，需要在成绩表中插入对应的学生信息，至于具体math、chinese、english后面由老师打分更新即可。 那么，如何设计触发器呢？

1. 首先它是一个插入Insert触发器，是建立在表student上的；
2. 然后是after，插入后的事件；
3. 事件内容是插入成绩表，主需要插入学生的学号和姓名，number为自增，而成绩目前不需要。
4. 注意：new表示student中新插入的值。

-- 创建触发器 
DELIMITER $
CREATE TRIGGER ins_stu  
AFTER INSERT ON student FOR EACH ROW   
BEGIN  
      INSERT INTO score_sheet (stu_id, stu_name)  VALUES( NEW.sid, NEW.sname); 
END$
DELIMITER;  
-- 给学生表插入一条记录
INSERT INTO student (sname)VALUES ('张三');

--  可以看到成绩表同时插入一条记录 
SELECT *  FROM student
SELECT *  FROM score_sheet

1.2、判断值后调用触发器

　　这里简单讲述几个判断插入类型的触发器。 比如触发器调用，当插入时间小时为20时，对数据进行插入：

DELIMITER $
create trigger ins_info
after insert on nhfxelect for each row 
begin
    if HOUR(new.RecordTime)='20' then  
    insert into nhfxbyhour (UnitDepName, UnitDepCode, ElectCost, TimeJG, RecordTime)
        values( '数统学院', '1', new.USERKWH, '20', new.RecordTime);
    end if;
END$
DELIMITER;  

　　这个触发器中，RecordTime为datetime类型，如“2016-08-28 20:10:00”，这时hour()这个值为20才能插入；否则数据不能插入。同时可以date_format(new.RecordTime, ‘%Y-%m-%d’)判断日期为某天或某年某月进行插入。 同时，再如更新触发器，如果设置的值为某个范围，才进行操作或性别为“男”或“女”才进行操作。

# 基本语法：
    if 判断条件 then
        sql语句;
    end if;

1.3、Update触发器-实时更新

　　假设存在一个实时插入数据的服务器，例如学生的消费金额或用电量等。 StuCost：学生的用电数据，实时插入，Cost为每30秒消费金额，RecordTime为每分钟插入时间，datetime类型； StuCostbyHour：统计学生一小时的消费金额，HourCost为金额总数，按小时统计，TimeJD时间段，1~24，对应每小时，RecordTime为统计时间。 现在需要设计一个实时更新触发器，当插入消费数据时，按小时统计学生的消费金额，同理，用电量等。

DROP TRIGGER IF EXISTS `upd_info`;
create trigger upd_info
after insert on StuCost for each row 
begin
    update StuCostbyHour set HourCost = HourCost + new.Cost
        where (TimeJD = hour(new.RecordTime) + 1) and date_format(new.RecordTime, '%Y-%m-%d') = date_format(RecordTime, '%Y-%m-%d');
end;

二、查看触发器

SHOW TRIGGERS;

　　在triggers表中查看触发器信息

　　在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过select来查看，语法：SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition

三、删除触发器

DROP TRIGGER [scheme_name] trigger_name

　　scheme_name:表示数据库名称，是可选的。如果省略了scheme_name，将从当前数据库中舍弃触发程序；

https://www.cnblogs.com/zh-1721342390/p/9602941.html

https://www.cnblogs.com/zzwlovegfj/archive/2012/07/04/2576989.html

https://blog.csdn.net/qq_36396104/article/details/80469997