msyql触发器实践案例

案例

当学生表学生的分数发生变化时，利用触发器功能，自动记录学生分数变更记录

学生表的sql：

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '学生姓名',
  `score` int(11) NULL DEFAULT NULL COMMENT '学生分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES (1, '王磊', 100);
INSERT INTO `student` VALUES (2, '王文杰', 0);

学生分数变动日志表的sql：

CREATE TABLE `student_score_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '学生姓名',
  `old_score` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '旧的分数',
  `new_score` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '新的分数',
  `log_time` datetime(0) NULL DEFAULT NULL COMMENT '变化时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
INSERT INTO `student_score_log` VALUES (5, '王文杰', '30', '50', '2021-04-02 16:32:32');
INSERT INTO `student_score_log` VALUES (4, '王文杰', '50', '30', '2021-04-02 16:32:22');
INSERT INTO `student_score_log` VALUES (6, '王文杰', '0', '30', '2021-04-02 16:32:39');

创建触发器的sql：

CREATE
/*下面这句话的意思是填写触发器的名字，Before代表执行前还是执行后操作，UPDATE代表执行什么操作的时候出发  ON 表名*/
    TRIGGER `trigger_student_score` BEFORE UPDATE ON `student` 
    FOR EACH ROW BEGIN 
/*判断score字段内容的新还是旧*/
 IF  NEW.score  <> OLD.score
 THEN
/*执行添加语句*/
 INSERT INTO `student_score_log`( `name`, `old_score`, `new_score`, `log_time`) VALUES ( old.name,new.score,  OLD.score,NOW());
 END IF;
END;
$$
DELIMITER ;

最终效果学生表的score发生变化后，自动在日志表记录