数据库高级技术实例存储过程触发器.docx

1、数据库高级技术实例存储过程触发器按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）1、 运用存储过程创建STUDENT、GRADE、COURSE表（要求实现完整性）。按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）2、 运用存储过程创建STUDENT、DEPARTMENT、DORM表（要求实现完整性）。按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）3、 运用存储过程对STUDENT、DEPARTMENT、DORM表插入一条符合完整性的记录。(内容自己定义)按照要求

2、编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）4、 编写带参的存储过程：实现删除01课程数据库原理的相关信息。按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）5、 编写带参的存储过程：实现删除990101学生原野的相关信息。按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）6、 编写带参的存储过程：实现宿舍号从2101修改为3101。按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）7、 编写带参的存储过程：实现查询宿舍号“250

3、5”的相关信息。按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）8、 运用触发器对GRADE实现插入记录“980101，08，99”（相关信息自己定义）。按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）9、 运用触发器对STUDENT表中的SEX实现数据范围检查，若出错，提示信息。按照要求编写相应代码，执行结果后，并删除对象：（以学号+题号做文件名提交：990101_1）10、运用触发器对STUDENT表的操作只能在工作时间：9-15，每周一到五，实现数据安全性控制。答案：1、 用存储过程创建STUDENT

4、、GRADE、COURSE表（要求实现完整性）。use student_datago if exists (select name from sysobjectswhere name=cun and type=P)drop procedure cungocreate procedure cunas begin if not exists(select name from sysobjects where name=student and type=U) begin create table student (sno char(6)NOT null unique, sname char(8) n

5、ot null, sex char(2), sage int, dno char(4), dormno char(5), constraint FK_DEP FOREIGN KEY (dno) references department(dno), constraint PK_student primary key(sno) )insert into studentvalues(990101,原野,男,21,1,2101);endif not exists(select name from sysobjects where name=grade and type=U) begin create

6、 table grade (sno char(6), cno char(2), score int constraint pk_grade primary key (sno,cno);insert into gradevalues (990101,01,85);endif not exists(select name from sysobjects where name=course and type=U) begin create table course (cno char(2) NOT null unique, cname char(20) not null, cpno char(2),

7、 credit int, teacher char(8) constraint FK_CP foreign key (cpno) references dorm(cpno), constraint PK_COr primary key(cno)insert into coursevalues(01,数据库原理,05,4,王凯); endendgocunselect *from student2、 运用存储过程创建STUDENT、DEPARTMENT、DORM表（要求实现完整性）。3、 运用存储过程对STUDENT、DEPARTMENT、DORM表插入一条符合完整性的记录。(内容自己定义)use

8、 studentgo if exists (select name from sysobjectswhere name=cun and type=P)drop procedure cungocreate procedure cunssno char(6),ssname char(8),ssex char(2),ssage int,ddno char(4),ddorm char(5),dddno char(4),ddname char(8),hhead char(20),dddorno char(5),dddtel char(7)asbegin if not exists(select sno

9、from student where sno=ssno) if exists (select dno from department where dno=ddno) If exists(select dormno from dorm where dormno=ddorm) insert into student (sno,sname,sex,sage,dno,dormno) values(ssno,ssname,ssex,ssage,ddno,ddorm) if not exists(select dno from department where dno=dddno) if not exis

10、ts(select dname from department where dname=ddname) insert into department values(dddno,ddname,hhead) if not exists(select dormno from dorm where dormno=dddorno) if not exists(select tele from dorm where tele=dddtel) insert into dorm values(dddorno,dddtel) endgouse studentgocun 990109,达到,男,19,4,2404

11、,5,地质,哈哈,6504,8306753select *from studentselect *from departmentselect *from studentselect *from dorm4、 编写带参的存储过程：实现删除01课程数据库原理的相关信息。if exists(select name from sysobjects where name=del_cname and type=p)drop procedure del_cnamegocreate procedure del_cnamecname char(20)asbegindelete grade where cno =

12、(select cno from course where cname=数据库原理)delete course where cname=cnameendgoselect * from course select * from gradeuse student_datagodel_cname 数据库原理go drop procedure del_cname5、 编写带参的存储过程：实现删除990101学生原野的相关信息。use student_data /*打开数据库*/go if exists(select name from sysobjects where name =del_Sand t

13、ype =p)drop procedure del_S gocreate procedure del_Sstu char(6)as begin delete grade where sno=stu delete student where sno=stu endgoselect * from grade select * from studentdel_S 9901016、 编写带参的存储过程：实现宿舍号从2101修改为3101。use student_dataif exists (select name from sysobjects where name = adjust_roo and

14、type = P) drop procedure adjust_roogocreate procedure adjust_roodormnoo char(5) ,dormnoo1 char(5)as begin update student set dormno=dormnoo1 where dormno=dormnoo delete from dorm where dormno=dormnoo end goadjust_roo 2101, 3101goselect * from student7、 编写带参的存储过程：实现查询宿舍号“2505”的相关信息。use studentgoif ex

15、ists(select name from sysobjects where name=search_dorm and type=p)drop procedure search_dormgocreate procedure search_dormdormno char(5)asbeginselect dorm.*, sno,sname from dorm,student where student.dormno=dorm. dormno and dormno=dormnoendgouse studentexec search_dorm 2505godrop procedure search_d

16、orm8、 运用触发器对GRADE实现插入记录“980101，08，99”（相关信息自己定义）。use student_dataif exists(select name from sysobjects where name =SCand type =tr)drop trigger SC gocreate trigger SC on gradefor insertas declare Sno char(6) declare Cno char(2) declare msg1 char(100) declare msg2 char(100) declare msg3 char(100) decla

17、re Score int select Sno= Sno from inserted select msg1=没有学生 if not exists(select * from student where sno =Sno ) begin print msg1 rollback transaction return end select Cno=Cno from inserted select msg2=没有课程 if not exists(select * from course where cno =Cno ) begin print msg2 rollback transaction re

18、turn end select Score=score from inserted select msg3=成绩不对 if (Score is not NULL and (Score100) begin print msg3 rollback transaction end goinsert into grade(sno,cno,score)values(990101,01,NULL) delete grade where sno=9901019、 运用触发器对STUDENT表中的SEX实现数据范围检查，若出错，提示信息。use student_dataif exists (select na

19、me from sysobjects where name=tr_ssex and type=TR) drop trigger tr_ssexgocreate trigger tr_ssex on student before update,insert as declare msg varchar(100) declare vssex char(2) select msg=数据有误,请检查后,重新输入! select vssex=sex from inserted if (vssex男and vssex女) begin print msg rollback transaction endre

20、turngoselect * from studentupdate student set sex=王where sno=990101update student set sex=女 where sno=990101insert into student(sno,sname,sex)values(200001,A,王)insert into student(sno,sname,sex)values(200002,B,女)drop trigger tr_ssexsp_helptext tr_ssex10、运用触发器对STUDENT表的操作只能在工作时间：9-15，每周一到五，实现数据安全性控制。

21、use studentif exists(select name from sysobjectswhere name=tr_up_studentand type=TR)drop trigger tr_up_studentgocreate trigger tr_up_student on studentfor update,insert,deleteasdeclare msg varchar(100)select msg =无法操作if(datename(weekday,getdate() like sat or datename(weekday,getdate() like sun or datename(hour,getdate() not between 9 and 15)BEGIN PRINT msgrollbackendgoselect * from studentupdate studentset sage=30where sno=990101