实验六 视图、存储程和触发器 实验报告.docVIP

实验六 视图、存储过程和触发器 实验内容1 建立视图 实现代码 create view 学生成绩 as select sc.snum,sections.cnum,course.cname,sc.score from sc,sections,course where sc.secnum = sections.secnum and sections.cnum = course.cnum 运行界面 数据查询 实现代码 select distinct cname from 学生成绩 where cname not in (select cname from 学生成绩where score 60) 运行界面 实验内容2 建立存储过程1 实现代码 create procedure 学生信息;1 as select * from student 运行界面 建立存储过程2 实现代码 create procedure学生信息;2 @_birthyear int as select * from student where year(birthday)=@_birthyear 运行界面 实验内容3 建立触发器1 实现代码 create trigger学号约束 on sc for insert,update as begin if((select snum from inserted ins)not in (select snum from student)) begin print违反参照完整性约束 rollback end end 建立触发器2 CREATE trigger sections_delete on sections for delete as declare @_secnum char(8) select @_secnum = d.secnum from deleted d if not exists (select * from sc where secnum = @_secnum) begin print 已删除没有被选的课程班级 commit tran end else begin delete from sc where secnum = @_secnum end