实验五 存储过程和触发器(参考预习)

实验五存储过程、触发器的创建和使用一．实验目的：1)了解存储过程的概念2)了解使用存储过程的特点及用途3)掌握创建存储过程的方法4)掌握执行存储过程的方法5)了解查看、修改和删除存储过程的方法6)了解触发器和一般存储过程的主要区别7)了解使用触发器的优点8)掌握创建触发器的方法9)掌握查看触发器信息的方法10)了解删除触发器的方法二．实验准备1．熟悉存储过程的使用2．熟悉触发器的使用；三．实验要求1．在实验之前做好实验准备2. 完成数据库设计，并验收实验结果，提交实验报告四．实验内容实验内容及步骤：（一）存储过程的创建和使用例1：在企业管理器中创建一个名为StuInfo的存储过程，完成的功能是在student表中查询系号为D2的学号、姓名、性别、年龄、系号的内容。

CREATE PROCEDURE StuInfoASSELECT SNO AS 学号，SNAME AS 姓名，SSEX AS 性别，SAGE AS 年龄,DNO AS 系号FROM SWHERE DNO=’D2’例2：创建存储过程StuScoreInfo，完成的功能是在表student,course和study中查询以下字段：学号、姓名、性别、课程名称、考试分数。

程序清单：打开student数据库use mydb--查询是否已存在此存储过程，如果存在，就删除它if exists (select name from sysobjectswhere name =’StuScoreInfo’ and type= ‘P’)drop procedure StuScoreInfogo--创建存储过程creat proceure StuScoreInfoasselect student.sno as 学号，sname as 姓名，sex as 性别,ame as 课程名称,study.grade as 考试分数from student,course,studywhere student.sno=study.sno and o=o例3：创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在t_student中查询此学生的信息。

程序清单：--删除已存在的存储过程if exists (select name from sysobjectswhere name = ‘stu_info’ and type =’P’)drop procedure stu_infogo--创建存储过程create procedure stu_info@sno varchar(8)asselectsno as 学号,sname as 姓名,sex as 性别,birthday as 出生日期polity as 政治面貌from studentwhere sno=@sno（二）执行存储过程的方法例1：执行前面例1中创建的StuInfo存储过程。

Use mydbExec StuInfo /*或者直接写存储过程的名称StuInfo*/注意：如果省略Exec关键字，则存储过程必须是批处理中的第一条语句，否则会出错。

例2：执行前面例2中创建的StuScoreInfo存储过程。

Use mydbExec StuScoreInfo例3：执行前面例3中创建的Stu_Info存储过程，该存储过程有一个输入参数“学号”，在执行时要传入一个学号值。

Use mydbExec Stu_Info ‘s1’或：Use mydbExec Stu_Info @sno=‘s1’例4：创建存储过程d_grade，根据指定的课程名（输入参数）返回该课程的最高分、最低分、平均分（输出参数）。

要求在创建存储过程前要先判断该存储过程是否已存在，如果存在，则将其删除。

（三）查看存储过程使用企业管理器，右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”对话框，在此对话框中可看到存储过程的源代码。

1.使用系统存储过程查看用户创建的存储过程：sp_help [[@objname=]name]用于显示存储过程的参数及其数据类型。

sp_helptext [[@objname=]name]用于显示存储过程的代码。

sp_depends [@objname=]’object’用于显示和存储过程相关的数据库对象。

（四）修改存储过程使用企业管理器中右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”对话框，在此对话框中可直接修改存储过程的代码。

1.使用T_SQl修改存储过程例1：修改前面创建的stu_info存储过程，使之完成以下功能：根据传入的学号，在表student,course,study中查询此学生的学号，姓名、性别、考试课程名称和考试分数。

Use mydbAlter procedure stu_info@sno varchar(10)asselect sname as 姓名，sex as 性别,ame as 课程名称,score as 考试成绩from student,course,studywhere student.sno=@sno and student.sno=study.sno and o=oexec stu_info ‘s1’（五）重命名存储过程1. 使用企业管理器，右击要操作的存储过程，在弹出的快捷菜单中选择“重命名”选项。

2.使用T_SQl修改存储过程sp_rename 原存储过程名称，新存储过程名称（六）删除存储过程3.使用SQL Server Management Studio 修改存储过程在SQL Server Management Studio 中，右击要操作的存储过程，在弹出的快捷菜单中选择“删除”选项。

4.使用T_SQl修改存储过程drop procedure {procedure} [,…n]触发器的创建和使用（一）触发器的创建例1：创建一个INSERT触发器，当在表student中插入一条新记录时，触发该触发器，并给出“你插入了一条新记录！”的提示信息。

在文本框中输入以下文本：CREATE TRIGGER Stu_Insert on [dbo].[student]FOR INSERTASDECLARE @msg char(30)SET @msg=”你插入了一条新记录！”print @msg例2：创建一个AFTER触发器，要求实现以下功能：在sc表上创建一个插入、更新类型的触发器scoreCheck，当在grade字段中插入或修改考试分数后，触发该触发器，检查分数是否在0~100之间。

Use studentif exists (select name from sysobjects where name = ‘scoreCheck’and type =’TR’)drop trigger scoreCheck/*创建触发器*/create trigger scoreCheckon scfor insert,updateasif update(score)print ‘AFTER触发器开始执行……’begindeclare @ScoreValue realselect @ScoreValue=(select score from inserted)if @ScoreValue>100 or @ScoreValue<0print ‘输入的分数有误，请确认输入的考试分数！’end创建了scoreCheck触发器之后，在T-SQL中输入以下SQL语句：use mydbprint ‘在sc中插入记录时触发器执行结果：’ /*在屏幕上显示引号中内容*/print ‘’ /*在屏幕上显示一空行*/insert into scvalues(‘s2’,’01’,-40) /*在屏幕上显示输入错误信息*/update scset score=123where sno=’s1’and cno=’1’ /*在屏幕上显示输入错误信息*/例3：创建一个INSTEAD OF触发器，要求实现以下功能：在course表上创建一个删除类型的触发器NotAllowDelete，当在c表中删除记录时，触发该触发器，显示“本表中的数据不允许被删除！不能执行删除操作！”的提示信息。

if exists (select name from sysobjectswhere name = ‘NotAllowDelete’ and type =’TR’)drop trigger NotAllowDelete/*创建触发器*/create triggerNotAllowDeleteon courseinstead of deleteasprint ‘INSTEAD OF触发器开始执行……’print ‘本表中的数据不允许被删除！不能执行删除操作！’测试NotAllowDelete触发器，在T-SQL中输入以下SQL语句：delete from course where cno=20030101’/*屏幕上显示NSTEAD OF触发器开始执行……*/例4：为course表创建一个INSERT触发器，当插入的新行中开课学分的值不是1~6时，就激活该出发器，撤销该插入操作，并使用RAISERROR语句返回一个错误信息。

CREATE TRIGGER tri_INSERT_KC ON COURSEFOR INSERTASDECLARE @开课学分 tinyintSELECT @开课学分=COURSE.creditFROM COURSE,InsertedWHERE COURSE.课程号= Inserted.课程号——如果新插入行的开课学分的值不是1~6，则撤销插入，并给出错误信息IF @开课学分 NOT BETWEEN 1 AND 6BEGINROLLBACK TRANSACTIONRAISERROR(‘开课学分的取值只能是1~6!’，16,10)END例5：为COURSE表再创建一个UPDATE触发器，当更新了某门课程的课程号信息时，就激活该触发器级联更新STUDY表中相关的课程号信息，并使用PRINT语句返回一个提示信息。

CREATE TRIGGER tri_UPDATE_KC ON COURSEFOR UPDATEASIF UPDATE(CNO) ――检测课程号列是否被更新BEGINDECLARE @原课程号char(3),@新课程号 char(3) ――声明变量――获取更新前后的课程号的值SELECT @原课程号=Deleted.课程号,@新课程号=Inserted.课程号FROM Deleted,InsertedWHERE Deleted.课程名=Inserted.课程名PRINT ‘准备级联更新STUDY表中的课程号信息…’——级联更新STUDY表中相关成绩记录的课程号信息UPDATE STUDYSET CNO=@新课程号WHERE CNO=@原课程号PRINT ‘已经级联更新STUDY表中原课程号’+@原课程号+’为’+@新课程号END（二）触发器的查看使用SQL Server Management Studio 查看查看触发器在企业管理器中右击要查看的表，从弹出的快捷菜单中选择“所有任务”│“管理触发器”选项，在“触发器属性”对话框中，从名称下拉列表框中选择要查看的触发器名称，在下面的文本框中就会显示该触发器的定义语句。