案例

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

学生表的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发生变化后，自动在日志表记录