早睡早起,方能养生
Sleep early rise early, way to keep healthy

Mysql创建触发器

super
2023-01-08 18:28
views 842

创建了两个表

 

 


-- ----------------------------
-- 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 条讨论
top