一、实验项目要求
实验目的
使学生加深对触发器和存储过程的创建和使用。
实验内容
1.掌握触发器的使用。
2.掌握存储过程的创建、修改和删除;掌握存储过程的执行。
具体内容如下:
(1)触发器的使用
①在数据表“学生”中创建update触发器,级联更新“选课”表相应的记录。
②利用新表,将“学生”表中被删除的记录存储到“学生备份”表中,以供日后的数据查询和分析。
(2)存储过程的使用
①在建立的数据库中,建立一个存储过程,要求统计计算机系学生的人数,并将人数返回给用户。
②在建立的数据库中,建立一个存储过程,要求
统计成绩大于等于90分学生的人数;
统计成绩大于等于80分并且小于90分学生的人数,并将人数返回给用户。
二、理论分析或算法分析
触发器是用户定义在关系表上的一类由事件驱动的特殊过程。
触发器又叫做事件-条件-动作规则。
SQL使用CREATE TRIGGER命令建立触发器,其一般格式为:
CREATE TRIGGER<触发器名>
{BEFORE|AFTER}<触发事件>ON<表名>
REFERENCING NEW|OLD ROW AS <变量>
FOR EACH{ROW|STATEMENT}
[WHEE<触发条件>]<触发动作体>
- 只有表的拥有者,即创建表的用户才可以在表上创建触发器,并且一个表上只能创建一定数量的触发器;
- 触发器名;
- 表名;
- 触发事件;
- 触发器类型;
- 触发条件;
- 触发动作体。
激活触发器:
- 执行该表上的BEFORE触发器;
- 激活触发器的SQL语句;
执行该表上的AFTER触发器。
三、实现方法
(1)触发器的使用
①在数据表“学生”中创建update触发器,级联更新“选课”表相应的记录。
在自己的建的数据库中(ST),在自己要创建的表中(STUDENT1),把这个表展开,下面有个触发器,然后点右键-----新建触发器,在BEGIN END中编译;
创建update触发器;
CREATE TRIGGER Stu_UPDATE
ON STUDENT
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @bSno char(9),@aSno char(9)
select @bSno = Sno from deleted
print '更新前的学号:'+ @bSno
select @aSno = Sno from inserted
print '更新后的学号:' + @aSno;
update SC set Sno = @aSno where SC.Sno = @bSno
END
GO
GO级联更新“选课”表:
use ST;
CREATE TABLE SC2(
Sno CHAR(9),
Cno CHAR(8),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Sno) REFERENCES STUDENT(Sno)
ON UPDATE cascade,
FOREIGN KEY (Cno) REFERENCES COURSE(Cno),
);
②利用新表,将“学生”表中被删除的记录存储到“学生备份”表中,以供日后的数据查询和分析。
CREATE TRIGGER STT_DETELE
ON STUDENT
FOR DETELE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
print'使用delete触发器备份删除STUDENT表中的数据'
insert into STUDENT1,
print'使用delete触发器备份删除STUDENT表中的数据'
SELECT * FROM deleted
-- Insert statements for trigger here
END
GO
(2)存储过程的使用
①在建立的数据库中,建立一个存储过程,要求统计计算机系学生的人数,并将人数返回给用户。
首先在自己建立的数据库中(ST),将其展开,可以看见一个可编程性,有一个存储过程,右键新建存储过程。
因为‘05’是计算机工程系,所以这里的Sdept为“05”;
CREATE PROCEDURE Proc_count
@Sno char(9),
@num smallint output
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @num=count(*) from STUDENT where Sno = @Sno
declare @Sdept varchar(20),@n smallint
set @Sdept = '05'
exec Proc_count @Sdept,@n output
print convert(char(6),@n)
-- Insert statements for procedure here
--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
- ②在建立的数据库中,建立一个存储过程,要求
0.1绩大于等于90分学生的人数;
0.2绩大于等于80分并且小于90分学生的人数,并将人数返回给用户。
CREATE PROCEDURE Proc_ST
@n1 smallint output,
@n2 smallint output
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @n1=count(*) FROM SC where Grade>=90
SELECT @n2=count(*) FROM SC where Grade>=80 AND Grade<90
declare @n1 smallint,@n2 smallint
exec Proc_ST @n1 output,@n2 output
print convert(varchar(6),@n1)
print convert(varchar(6),@n2)
-- Insert statements for procedure here
--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
四、实验结果分析
执行成功验证
实验总结:
通过反复的看书,也通过自己的练习,终于完成了这次的实验。同样通过这次的实验,自己也学会了不少东西,比如说触发器如何使用,还有备份表的应用,还有存储过程的创建,修改,和执行等等,在实验中也碰到了好多的问题,后悔上课的时候没有好好听讲,只要以后认真听讲,相信这些问题都是可以解决的。
思考题:
1 触发器和存储过程何时执行?
触发器(trigger)是SQL server提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作 ( insert,delete,update)时就会激活它执行。
存储过程必须有用户、应用程序或者触发器来显示的调用并执行,而触发器是当特定时间出现的时候,自两行或者激活的,与连接用数据率中的用户、或者应用留抚关。
2 触发器能否带参数?
触发器是不支持参数的