Mysql创建触发器
super
2023-01-08 18:28
![views](/static/super/images/icon/evaluate-9a.png)
创建了两个表
![](/uploads/article/20230108/e2b9a136538a688b9ece54c2cecdb012.png)
-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '一年级');
INSERT INTO `class` VALUES (2, '二年级');
INSERT INTO `class` VALUES (3, '三年级');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`class_id` int(10) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, 1, '小书');
INSERT INTO `student` VALUES (2, 2, 'super');
INSERT INTO `student` VALUES (3, 3, '小六');
![](/uploads/article/20230108/ce114c1d6b11415c4f63354cd8b66753.png)
解析:在删除class表中的某一行数据时,会同步删除student表中对应的数据
当前语句中的OLD表示,正在删除的这一行
OLD.id 表示 class表中将要被删除的记录的id
如果是插入触发器,那么使用NEW代指新插入的这一行,例如
INSERT INTO `class` VALUES (1, '一年级');
NEW.id就是‘1’
NEW.name就是‘一年级’
新建插入触发器
![](/uploads/article/20230108/ab112a63f595a53ff0cd5662bf999919.png)
创建完,别点保存,点右边的SQL预览,可以看当前的sql语句
![](/uploads/article/20230108/a13573724441f7318aad01a74ed645f7.png)
测试一下,往class表中添加一个4年级
![](/uploads/article/20230108/bda6a0545905ec70cc057b65c73483af.png)
![](/uploads/article/20230108/09c7a111e00579a2392a12c551013725.png)
自动新增了小王这条数据
0 条讨论