案例
当学生表学生的分数发生变化时，利用触发器功能，自动记录学生分数变更记录
学生表的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发生变化后，自动在日志表记录
