mysql 触发器实现级联删除有外键的多张表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 触发器实现级联删除有外键的多张表，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9076字，纯文字阅读大概需要13分钟。
内容图文
----------------------------
-- Table structure for tb_grade
-- ----------------------------
DROP TABLE IF EXISTS `tb_grade`;
CREATE TABLE `tb_grade` (
`grade_id` int(11) NOT NULL AUTO_INCREMENT,
`grade_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`grade_id`),
UNIQUE KEY `grade_check` (`grade_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_grade
-- ----------------------------
INSERT INTO `tb_grade` VALUES (‘1‘, ‘一年级‘);
INSERT INTO `tb_grade` VALUES (‘2‘, ‘二年级‘);
INSERT INTO `tb_grade` VALUES (‘3‘, ‘三年级‘);
INSERT INTO `tb_grade` VALUES (‘4‘, ‘四年级‘);
INSERT INTO `tb_grade` VALUES (‘5‘, ‘五年级‘);
-- ----------------------------
-- Table structure for tb_subject
-- ----------------------------
DROP TABLE IF EXISTS `tb_subject`;
CREATE TABLE `tb_subject` (
`subject_id` int(11) NOT NULL AUTO_INCREMENT,
`subject_name` varchar(20) DEFAULT NULL,
`class_hour` int(3) DEFAULT NULL,
`grade_id` int(11) DEFAULT NULL,
PRIMARY KEY (`subject_id`),
KEY `grade_id` (`grade_id`),
CONSTRAINT `tb_subject_ibfk_1` FOREIGN KEY (`grade_id`) REFERENCES `tb_grade` (`grade_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_subject
-- ----------------------------
INSERT INTO `tb_subject` VALUES (‘1‘, ‘java‘, ‘60‘, ‘1‘);
INSERT INTO `tb_subject` VALUES (‘2‘, ‘html‘, ‘40‘, ‘1‘);
INSERT INTO `tb_subject` VALUES (‘3‘, ‘javascript‘, ‘30‘, ‘1‘);
INSERT INTO `tb_subject` VALUES (‘4‘, ‘database‘, ‘60‘, ‘2‘);
INSERT INTO `tb_subject` VALUES (‘5‘, ‘java oop‘, ‘60‘, ‘2‘);
INSERT INTO `tb_subject` VALUES (‘6‘, ‘servlet‘, ‘40‘, ‘2‘);
INSERT INTO `tb_subject` VALUES (‘7‘, ‘jsp‘, ‘40‘, ‘2‘);
INSERT INTO `tb_subject` VALUES (‘8‘, ‘struts2‘, ‘60‘, ‘3‘);
INSERT INTO `tb_subject` VALUES (‘9‘, ‘hibernate‘, ‘60‘, ‘3‘);
INSERT INTO `tb_subject` VALUES (‘10‘, ‘spring‘, ‘60‘, ‘3‘);
-- ----------------------------
-- Table structure for tb_student
-- ----------------------------
DROP TABLE IF EXISTS `tb_student`;
CREATE TABLE `tb_student` (
`student_no` varchar(20) NOT NULL COMMENT ‘学号‘,
`login_pwd` varchar(20) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
`student_name` varchar(20) NOT NULL COMMENT ‘姓名‘,
`sex` enum(‘女‘,‘男‘) DEFAULT ‘男‘ COMMENT ‘性别‘,
`grade_id` int(11) DEFAULT NULL COMMENT ‘年级 - 外键‘,
`phone` varchar(20) DEFAULT NULL COMMENT ‘联系电话‘,
`address` varchar(100) DEFAULT ‘学生宿舍‘ COMMENT ‘现住址‘,
`birthday` date DEFAULT NULL COMMENT ‘出生日期‘,
`email` varchar(50) DEFAULT NULL COMMENT ‘电子邮件‘,
PRIMARY KEY (`student_no`),
KEY `grade_id` (`grade_id`),
CONSTRAINT `tb_student_ibfk_1` FOREIGN KEY (`grade_id`) REFERENCES `tb_grade` (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_student
-- ----------------------------
INSERT INTO `tb_student` VALUES (‘s01‘, ‘123456‘, ‘宋江‘, ‘男‘, ‘1‘, ‘13888811111‘, ‘学生宿舍‘, ‘1985-10-10‘, ‘13888811111@qq.com‘);
INSERT INTO `tb_student` VALUES (‘s02‘, ‘123456‘, ‘卢俊义‘, ‘男‘, ‘1‘, ‘13888822222‘, ‘教场西路8号‘, ‘1987-08-08‘, ‘13888822222@qq.com‘);
INSERT INTO `tb_student` VALUES (‘s03‘, ‘123456‘, ‘吴用‘, ‘男‘, ‘2‘, ‘13888833333‘, ‘教场西路8号‘, ‘1991-06-06‘, ‘13888833333@qq.com‘);
INSERT INTO `tb_student` VALUES (‘s04‘, ‘123456‘, ‘孙二娘‘, ‘女‘, ‘2‘, ‘13888844444‘, ‘教场西路16号‘, ‘1983-05-05‘, ‘13888844444@qq.com‘);
INSERT INTO `tb_student` VALUES (‘s05‘, ‘123456‘, ‘李逵‘, ‘男‘, ‘1‘, ‘13888855555‘, ‘学生宿舍‘, ‘1992-01-01‘, ‘13888855555@qq.com‘);
INSERT INTO `tb_student` VALUES (‘s06‘, ‘123456‘, ‘顾大嫂‘, ‘女‘, ‘1‘, ‘13888866666‘, ‘教场西路16号‘, ‘1990-02-02‘, ‘13888866666@qq.com‘);
INSERT INTO `tb_student` VALUES (‘s07‘, ‘123456‘, ‘柴进‘, ‘男‘, ‘3‘, ‘13888877777‘, ‘学生宿舍‘, ‘1991-03-03‘, null);
INSERT INTO `tb_student` VALUES (‘s08‘, ‘123456‘, ‘林冲‘, ‘男‘, ‘3‘, ‘13888888888‘, ‘教场西路8号‘, ‘1986-04-04‘, null);
INSERT INTO `tb_student` VALUES (‘s09‘, ‘123456‘, ‘鲁智深‘, ‘男‘, ‘3‘, ‘13888899999‘, ‘教场西路8号‘, ‘1991-05-05‘, ‘‘);
INSERT INTO `tb_student` VALUES (‘s10‘, ‘123456‘, ‘扈三娘‘, ‘女‘, ‘2‘, ‘13888800000‘, ‘学生宿舍‘, ‘1983-06-06‘, ‘‘);
-- ----------------------------
-- Table structure for tb_score
-- ----------------------------
DROP TABLE IF EXISTS `tb_score`;
CREATE TABLE `tb_score` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘成绩编号‘,
`student_no` varchar(255) NOT NULL COMMENT ‘学号 - 外键‘,
`subject_id` int(11) DEFAULT NULL COMMENT ‘所考科目‘,
`student_score` float(5,2) DEFAULT ‘0.00‘ COMMENT ‘分数‘,
`exam_date` date DEFAULT NULL COMMENT ‘考试日期‘,
PRIMARY KEY (`id`),
KEY `student_no` (`student_no`),
KEY `subject_id` (`subject_id`),
CONSTRAINT `tb_score_ibfk_1` FOREIGN KEY (`student_no`) REFERENCES `tb_student` (`student_no`),
CONSTRAINT `tb_score_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `tb_subject` (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_score
-- ----------------------------
INSERT INTO `tb_score` VALUES (‘1‘, ‘s01‘, ‘1‘, ‘90.50‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘2‘, ‘s02‘, ‘1‘, ‘85.00‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘3‘, ‘s03‘, ‘1‘, ‘69.50‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘4‘, ‘s04‘, ‘1‘, ‘85.50‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘5‘, ‘s05‘, ‘1‘, ‘45.50‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘6‘, ‘s06‘, ‘1‘, ‘95.50‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘7‘, ‘s07‘, ‘1‘, ‘76.50‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘8‘, ‘s08‘, ‘1‘, ‘88.00‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘9‘, ‘s09‘, ‘1‘, ‘70.00‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘10‘, ‘s10‘, ‘1‘, ‘55.00‘, ‘2016-11-05‘);
INSERT INTO `tb_score` VALUES (‘11‘, ‘s01‘, ‘2‘, ‘80.50‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘12‘, ‘s02‘, ‘2‘, ‘70.00‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘13‘, ‘s03‘, ‘2‘, ‘68.50‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘14‘, ‘s04‘, ‘2‘, ‘82.50‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘15‘, ‘s05‘, ‘2‘, ‘43.50‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘16‘, ‘s06‘, ‘2‘, ‘90.50‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘17‘, ‘s07‘, ‘2‘, ‘70.50‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘18‘, ‘s08‘, ‘2‘, ‘80.00‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘19‘, ‘s09‘, ‘2‘, ‘80.00‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘20‘, ‘s10‘, ‘2‘, ‘56.00‘, ‘2016-11-07‘);
INSERT INTO `tb_score` VALUES (‘21‘, ‘s01‘, ‘3‘, ‘84.50‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘22‘, ‘s02‘, ‘3‘, ‘73.00‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘23‘, ‘s03‘, ‘3‘, ‘69.50‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘24‘, ‘s04‘, ‘3‘, ‘86.50‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘25‘, ‘s05‘, ‘3‘, ‘44.50‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘26‘, ‘s06‘, ‘3‘, ‘80.50‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘27‘, ‘s07‘, ‘3‘, ‘75.50‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘28‘, ‘s08‘, ‘3‘, ‘87.00‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘29‘, ‘s09‘, ‘3‘, ‘85.00‘, ‘2016-11-09‘);
INSERT INTO `tb_score` VALUES (‘30‘, ‘s10‘, ‘3‘, ‘66.00‘, ‘2016-11-09‘);
2、表结构
3、需求：针对数据库中的4张数据表(tb_grade、tb_subject、tb_student、tb_score)
在需要的数据表上创建触发器，达到每删除一个数据表的记录时都能顺顺利执行(级联删除)。
4、具体实现：
-- 为tb_grade创建触发器 DROP TRIGGER IF EXISTS trig_grade; delimiter // CREATE TRIGGER trig_grade BEFORE delete on tb_grade for each row BEGIN DELETE FROM tb_student WHERE grade_id = old.grade_id; DELETE FROM tb_subject WHERE grade_id = old.grade_id; END // delimiter ; -- 为tb_student创建触发器 DROP TRIGGER IF EXISTS trig_student; delimiter // CREATE TRIGGER trig_student BEFORE delete on tb_student for each row BEGIN DELETE FROM tb_score WHERE student_no = old.student_no; END // delimiter ; -- 为tb_subject创建触发器 DROP TRIGGER IF EXISTS trig_subject; delimiter // CREATE TRIGGER trig_subject BEFORE delete on tb_subject for each row BEGIN DELETE FROM tb_score WHERE subject_id = old.subject_id; END // delimiter ; -- 查看此数据库中的触发器 SHOW TRIGGERS; -- 测试删除数据是否能成功 DELETE FROM tb_student WHERE student_no = "S01"; DELETE FROM tb_grade WHERE grade_id = 1; DELETE FROM tb_subject WHERE subject_id = 3; DELETE FROM tb_score WHERE id = <span style="color: #80000 本文系统来源：https://www.cnblogs.com/zhangzimuzjq/p/11659842.html
内容总结
以上是互联网集市为您收集整理的mysql 触发器实现级联删除有外键的多张表全部内容，希望文章能够帮你解决mysql 触发器实现级联删除有外键的多张表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】