如何在MySQL中编写用户定义的函数，以便从一个表中删除一行时，另一表中的特定行也被删除? [英] How can I write a user defined function in MySQL so that when I delete a row from one table, that particular row from another table also gets deleted?

问题描述

CREATE TABLE `student`

(
  `student_id` int(5) NOT NULL AUTO_INCREMENT,
  `student_first_name` varchar(30) not null,
  `student_lase_name` varchar(30) not null,
  `student_roll_no` int(5) not null,
  `student_class` int(2) not null,
  PRIMARY KEY (`student_id`)
);

CREATE TABLE `result_sheet_1`
(
  `student_id` int(5),
  `student_first_name` varchar(30),
  `student_lase_name` varchar(30),
  `student_roll_no` int(5),
  `student_class` int(2),
  `mid_1_english` int(2),
  `mid_2_english` int(2),
  `mid_1_mathematics` int(2),
  `mid_2_mathematics` int(2),
  `semester_final_english` int(2),
  `semester_final_mathematics` int(2)
);

delimiter $$
create trigger result_sheet_1_insert_trigger

after insert on student
for each row 
begin 
insert into result_sheet_1(student_id, student_first_name, student_lase_name, student_roll_no, student_class, mid_1_english, mid_2_english, mid_1_mathematics, mid_2_mathematics, semester_final_english, semester_final_mathematics)
values (new.student_id, new.student_first_name, new.student_lase_name, new.student_roll_no, new.student_class, 0, 0, 0, 0, 0, 0);

end$$
delimiter ;

每当从学生表中删除特定行时，我都希望通过用户定义的函数从result_sheet_1中删除该行.预先谢谢你！

I want to delete a row from result_sheet_1 through a user defined function whenever that particular row gets deleted from student table. Thank you in advance!

推荐答案

您只需使用外键即可使用Cascade来执行任务.

You can simply use Foreign Key to perform the task with using Cascade.

更改表result_sheet_1添加外键(student_id) 参考test.student(student_id)在更新时删除级联 级联；

ALTER TABLE result_sheet_1 ADD FOREIGN KEY (student_id) REFERENCES test.student(student_id) ON DELETE CASCADE ON UPDATE CASCADE;

这篇关于如何在MySQL中编写用户定义的函数，以便从一个表中删除一行时，另一表中的特定行也被删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！