-- 添加剩余可选报人数字段 ALTERTABLE course ADD available INT;
-- 利用之前定义的视图更新available字段 UPDATE course SET available = up_limit - (SELECT student_num FROM available_course_view WHERE available_course_view.course_no = course.course_no);
-- 选报科目 插入数据到choose 更新course里面的available -1 DROPTRIGGER choose_insert_before_trigger; DELIMITER $$ CREATETRIGGER choose_insert_before_trigger BEFORE INSERT ON choose FOREACHROW BEGIN UPDATE course SET available = available -1WHERE course_no = new.course_no; END $$ DELIMITER ;
DELIMITER $$ CREATETRIGGER dept_delete_before_trigger BEFORE DELETE ON dept FOREACHROW BEGIN DELETEFROM employee WHERE d_no = old.d_no; END $$ DELIMITER ;