数据库-- 触发器与存储过程

一、实验项目要求

实验目的

使学生加深对触发器和存储过程的创建和使用。

实验内容

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<触发条件>]<触发动作体>

1. 只有表的拥有者，即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器；
2. 触发器名；
3. 表名；
4. 触发事件；
5. 触发器类型；
6. 触发条件；
7. 触发动作体。

   激活触发器：

1. 执行该表上的BEFORE触发器；
2. 激活触发器的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 触发器能否带参数？

触发器是不支持参数的