实验六 触发器、存储过程编程实验

一、实验目的

学习存储过程和触发器的创建和使用方法。

二、实验环境

硬件：PC机

软件：SQL Server 2000

三、实验原理

1. 触发器

触发器是一种特殊的过程，它不带参数，不被用户和程序调用，只能由用户对数据库中的表的操作（插入、删除、修改）触发。因此，可以利用触发器来维护表间的数据一致性。

触发器只能在表上建立，一张表最多可有3个触发器，即插入触发器、删除触发器、修改触发器，分别由插入、删除、修改操作触发。

触发器可以查询其它表，而且可以包含复杂的SQL语句。它们主要用于强制复杂的业务规则及数据完整性。

⑴创建触发器 创建触发器时需指定：    

名称。

在其上定义触发器的表。 触发器将何时激发。

激活触发器的数据修改语句。有效选项为INSERT、UPDATE或DELETE。多个数据修改语句可激活同一个触发器。例如，触发器可由INSERT或UPDATE语句激活。 

执行触发操作的编程语句。 语法：

CREATE TRIGGER 触发器名 ON {表名 | 视图名 }

{ FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT] [,] [UPDATE] } AS

SQL语句块 RETURN

⑵使用inserted和deleted表

触发器语句中使用了两种特殊的表：deleted表和inserted表。Microsoft SQL Server 2000

自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。

inserted 和 deleted 表主要用于触发器中：    

扩展表间引用完整性。

在以视图为基础的基表中插入或更新数据。 检查错误并基于错误采取行动。

找到数据修改前后表状态的差异，并基于此差异采取行动。

Deleted表用于存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。Deleted表和触发器表通常没有相同的行。

Inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。

更新事务类似于在删除之后执行插入；首先旧行被复制到deleted表中，然后新行被复制到触发器表和inserted表中。

在设置触发器条件时，应当为引发触发器的操作恰当使用inserted和deleted表。虽然在测试INSERT时引用deleted表或在测试DELETE时引用inserted表不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何行。

⑶删除触发器

从当前数据库中删除一个或多个触发器。 语法：

DROP TRIGGER 触发器名 [ ,...n ]

2. 自定义数据类型

SQL Server 2000允许用户定义自己的数据类型。 ⑴创建用户定义的数据类型 语法：

sp_addtype ‘类型名’, ‘系统数据类型名’, ‘属性’ ⑵删除用户定义的数据类型 语法：

sp_droptype ‘类型名’ ⑶查看用户定义的数据类型 语法：

sp_help ‘类型名’ 3. 函数

SQL Server 2000 支持两种函数类型：

 内置函数

按Transact-SQL参考中定义的方式运行且不能修改。只有使用Transact-SQL参考中所定义语法的Transact-SQL语句才能引用这类函数。



用户定义函数

可以用CREATE FUNCTION语句定义自己的Transact-SQL函数。 ⑴创建函数 语法：

CREATE FUNCTION 函数名

( [ { @参数 [AS] 类型 [ = default ] } [ ,...n ] ] ) RETURNS 函数返回值类型 | TABLE AS BEGIN 函数体语句

RETURN 函数返回值 | SELECT语句 END

⑵函数调用 语法：

[Declare 变量名 数据类型 ]

[Select @变量名= ] 函数名 ( [参数表达式] [,...] )

⑶删除函数 语法：

DROP FUNCTION 函数名 [ ,...n ]

4. 存储过程

存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。它在建立时由RDBMS编译和优化，其执行代码存储于数据库中的程序中。

存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

存储过程分为两类：系统存储过程和自定义存储过程。系统存储过程在系统安装时自动装载于系统数据库中，便于用户或数据库管理员管理和维护数据库中的各种数据信息和对象。系统存储过程以“sp_”开头。用户自定义存储过程，由用户定义。

⑴创建存储过程 语法：

CREATE PROC 过程名

[ ( @参数变量 数据类型 [ = default ] [ OUTPUT ] ) ] [ ,...n ]

[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS SQL语句 [ ...n ]

[RETURN [存储过程执行状态]]

⑵执行存储过程 可在命令行或批中调用。

如在批中调用，应加EXEC 过程名 [参数 [OUTPUT] ] [,...n]

⑶删除存储过程 语法：

DROP PROC 过程名

⑷查看存储过程 语法：

sp_helptext 过程名

⑸更改存储过程名称 语法：

sp_rename 旧过程名, 新过程名

四、实验内容

1. 利用T-SQL语句创建触发器，并测试其作用 要求：

⑴在person表上创建一个触发器，当删除表person中的员工信息时，级联删除表pay中该员工的工资信息。

参考：

CREATE TRIGGER person_del ON person FOR DELETE AS

IF @@rowcount=0 RETURN DELETE pay FROM pay t, deleted d WHERE t.No=d.No RETURN

注：@@rowcount=0是SQL Server提供的系统变量，其值表示表中有几行记录被删除了。

⑵在pay表上创建一个触发器，检查在修改该表时是否有不存在于person表中的员工代码出现。

参考：

CREATE TRIGGER pay_update ON pay FOR Update AS

Declare @num_rows int Select @num_rows=@@rowcount IF @num_rows=0 RETURN IF (Select count(*) From person p, inserted i Where p.No=i.No) !=@num_rows Begin

raiserror 33333 '试图修改非法的员工号到pay表中' rollback transaction return End RETURN

⑶在pay表上创建一个触发器，当向该表插入数据时，必须参考表person中的No。 参考：

CREATE TRIGGER pay_insert ON pay FOR Insert AS

Declare @num_rows int Select @num_rows=@@rowcount IF @num_rows=0 RETURN IF (Select count(*) From person p, inserted i Where p.No=i.No) !=@num_rows Begin

raiserror 33333 '试图插入非法的员工号到pay表中' rollback transaction

return End RETURN

2. 利用T-SQL语句自定义数据类型

要求：定义一个数据类型d_no，将其长度定义为2B，并以此来重新定义dept表。 参考：

sp_addtype d_no, 'char(2)', 'NOT NULL' go

create table dept1 (deptno d_no,

deptname varchar(10) Not Null)

3. 利用T-SQL语句创建函数，并调用 要求：

⑴创建一个函数Fun_CheckNo，检测给定的员工号是否存在，如果存在返回0，否则返回-1。

参考：

Create function Fun_CheckNo(@pno char(6)) Returns integer as Begin

Declare @num Int

If Exists (Select no from person

Where no=@pno) Select @num=0

Else

Select @num=-1 Return @num END

⑵调用函数Fun_CheckNo，如果返回0，则向表pay中插入一行该员工的工资记录。 参考：

Declare @num Int

Select @num=DBO. Fun_CheckNo('000008') If @num=0

Insert pay values('000008',2005,2,2200,280,12.4)

4. 利用T-SQL语句创建存储过程，并调用

要求：

⑴创建一个存储过程Pro_CalAge，根据person表中的出生日期计算其实际年龄。 参考：

Create PROC Pro_CalAge @code char(6), @age int OUTPUT As

Declare @birth varchar(4), @today varchar(4) Select @birth=DATENAME(year,birthday) From person Where no=@code

Select @today=DATENAME(year,GETDATE())

Select @age=CONVERT(INT,@today)-CONVERT(INT,@birth)

⑵调用存储过程Pro_CalAge，计算工号为’000001’的员工实际年龄。 参考：

Declare @age Int

Execute Pro_CalAge '000001', @age output Print @age

五、练习

⑴利用T-SQL语句，在person表上创建一个触发器，当修改表person中的员工工号时，级联修改表pay中该员工的工号信息。

⑵利用企业管理器，完成以上实验内容。