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

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

一、实验目的

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

二、实验环境

硬件：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 ]