存储过程与触发器
1.1实训目的
? ?
掌握使用企业管理器建立存储过程与触发器的方法。 掌握使用T-SQL语句编写存储过程与触发器的方法。
1.2实训要求
? ? ?
熟悉编程结构。
会使用企业管理建立存储过程与各种简单的触发器。
能熟练使用T-SQL语句编写存储过程与简单实用的触发器。
1.3 实训内容及步骤
1使用企业管理器还原“学生管理数据库” 步骤略。 2使用企业管理器建立存储过程
例：编写一个在student表中插入数据，然后再进行数据查询的存储过程。
（1）打开企业管理器，在树状目录中展开“学生管理库”节点，并选择其下一级节点“存储过程”，单击鼠标右键，在弹出菜单中选择命令“新建存储过程”，打开创建存储过程的窗口。如下图7-1新建存储过程窗口所示。
（2）在存储过程文本框中将下列语句写入相应位置。
CREATE PROCEDURE sp_instudent AS
insert into student (学号,姓名,性别,年龄,所学专业)values(15,'李梦露','女',21,'计算机
软件') select * from student
按下“语法检查”按钮，出现“语法检查成功！”对话框，则说明没有问题。如图7-2建存储过程语法检查窗口。
7-1新建存储过程窗口
7-2建存储过程语法检查窗口
（3）执行存储过程。
在查询分析器中输入下面语句exec sp_instudent，点击“执行查询”按钮行存储过程。
如图7-3执
7-3执行存储过程
3使用T-SQL语句编写存储过程
例：编写一个存储过程，它能根据学号统计该学生的各门课程的总成绩，存储过程名称为“sp_sum”，写出T-SQL语句。
（1）在查询分析器中输入下面语句，点击“执行查询”按钮编写存储过程。
create proc sp_sum @sid int as
select sum (成绩) from report where 学号=@sid
保存存储过程如图7-4
图7-4编写存储过程
（2）执行存储过程语句：exec sp_ sum 1
图7-5执行存储过程
4使用企业管理器创建触发器
例：在course表上编写一个插入类型的触发器，当有新记录插入时打印“有新记录插入！”。 （1）在企业管理器中，选中course表，单击鼠标右键，选择“所有任务”，再选择“管理触发器”，打开触发器属性对话框，在其中输入如下内容：
CREATE TRIGGER course_insert ON dbo . course FOR INSERT
AS
print '新的课程加入！'
print '请做好操作记录！'
（2）输入以上语句后，按下“检查语法”按钮，如图7-6创建触发器，再按下“确定” 按钮，就创建了一个名为course_insert的触发器。
图7-6创建触发器
5通过事件触发触发器
例：在course表中插入一条新记录，检查course_insert的触发器的执行效果。 在查询分析器中写入下面语句并执行：insert course values(6,'高等数学',5 ,'王老师') 如图7-7插入数据行触发触发器。
图7-7插入数据行触发触发器
6触发器应用举例
例1：设student表中增加一列exnum表示共参加了几门考试，初始缺省值设为0。每当向report 插入一条记录，则应该将exnum++
创建触发器的语句如下：
CREATE TRIGGER report_insert ON report FOR INSERT AS UPDATE s SET exnum=exnum+1 FROM student s
INNER JOIN inserted I ON s.sid=i.sid
在创建触发器之前先做如下准备工作：
（1）在企业管理器中对student表增加列 exnum类型为smallint （2）建临时表#tmp用来存放每个学生参加考试科目数
select sid,count(sid) as countt into #tmp from report group by sid
update s set s.exnum=#tmp.countt from student s inner join #tmp on s.sid=#tmp.sid select * from student 例2：级联删除课程
CREATE TRIGGER course_delete ON course
FOR DELETE AS
DELETE r FROM report r INNER JOIN deleted d ON r.cid=d.cid 例3：级联修改课程号 CREATE TRIGGER course_update ON course FOR UPDATE AS IF UPDATE(cid) BEGIN
UPDATE r SET r.cid=
(SELECT i.cid FROM inserted i INNER JOIN course c ON i.cid=c.cid) FROM report r INNER JOIN deleted d ON r.cid=d.cid END
验证：UPDATE course SET cid=100 WHERE cid=1
7实训练习题
（1）编写一个名为“sp_max”的存储过程，它能给出成绩最高的一位学生姓名、性别、所学专业、成绩，写出创建存储过程及执行存储过程的T-SQL语句。
（2）编写一个名为“sp_zong”的存储过程，它能根据学生姓名统计该学生的总成绩并能将成绩返回给调用者，写出创建存储过程及执行存储过程的T-SQL语句。
（3）编写一个名为“sp_update”存储过程，它能根据课程号将该课程的考核成绩提高10%，写出创建存储过程及执行存储过程的T-SQL语句。
（4）编写一个名为“sp_avg”存储过程，它能根据学号统计该学生的各门课程的平均成绩，写出创建存储过程及执行存储过程的T-SQL语句。
（5）建立插入触发器
在查询分析器中建立Students表的插入触发器，名称为Students_insert。在查询分析器中检验。
（6）建立修改触发器
在查询分析器中建立Students表修改触发器，名称为Students_ update。在查询分析器中检验。
（7）建立删除触发器
在查询分析器中建立Students表的删除触发器，名称为Students_delete。在查询分析器中检验。
数据库-存储过程与触发器
