student 表 -course 表 student-course 关系表
当insert student的时候在关系表里添加student和course的关系。由于course是必修课，所以属于同一个major的student和course要默认选择。即student必须选择major的course。
1 --创建insert插入类型触发器 2 if (object_id('tgr_student_insert', 'tr') is not null) 3 drop trigger tgr_student_insert 4 GO 5 6 create trigger tgr_student_insert 7 on student 8 for insert --插入触发 9 as 10 --定义变量 11 declare @studentid int, @name varchar(20), @major int; 12 --在inserted表中查询已经插入记录信息 13 select @studentid = id, @name = name ,@major = majorID from inserted; 14 -- set @name = @name + convert(varchar, @id); 15 --set @temp = @id / 2; 16 DECLARE @courseid int; 17 DECLARE contact_cursor CURSOR FOR 18 select id from course where majorID = @major; 19 20 OPEN contact_cursor; 21 22 -- Perform the first fetch and store the values in variables. 23 -- Note: The variables are in the same order as the columns 24 -- in the SELECT statement. 25 26 FETCH NEXT FROM contact_cursor 27 INTO @courseid; 28 29 -- Check @@FETCH_STATUS to see if there are any more rows to fetch. 30 WHILE @@FETCH_STATUS = 0 31 BEGIN 32 33 -- Concatenate and display the current values in the variables. 34 --PRINT 'Course Name: ' + @courseid 35 INSERT INTO student_course (course_id,student_id) VALUES(@courseid,@studentid); 36 -- This is executed as long as the previous fetch succeeds. 37 FETCH NEXT FROM contact_cursor 38 INTO @courseid; 39 END 40 41 CLOSE contact_cursor; 42 DEALLOCATE contact_cursor; 43 44 GO
