Mysql创建触发器
super
2023-01-08 18:28
1374
创建了两个表
-- ----------------------------
-- 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, '小六');
解析:在删除class表中的某一行数据时,会同步删除student表中对应的数据
当前语句中的OLD表示,正在删除的这一行
OLD.id 表示 class表中将要被删除的记录的id
如果是插入触发器,那么使用NEW代指新插入的这一行,例如
INSERT INTO `class` VALUES (1, '一年级');
NEW.id就是‘1’
NEW.name就是‘一年级’
新建插入触发器
创建完,别点保存,点右边的SQL预览,可以看当前的sql语句
测试一下,往class表中添加一个4年级
自动新增了小王这条数据
0 条讨论