该题的重点就是要获取该专业同学全部选修的课程的课程号
这里就涉及到insert触发器要同时插入多条记录
GO
CREATE TRIGGER alter_major ON student
AFTER DELETE
AS
declare @sno varchar(9)
declare @ccno varchar(10)
declare @mno varchar(6)
INSERT INTO student_course(sNO,ccNO)
SELECT @sno,@ccno
FROM student_course sc,student s
where sc.sNO = s.sNO and s.mNO = @mno
GROUP BY ccNO
Having(COUNT(*))=(SELECT COUNT(*) FROM student WHERE student.mNO=@mno)-1
GO
由于是after型触发器,所有不能算上刚转专业同学的,因此总数要-1.
同时insert into后面的select查询的信息便是要插入的多条数据