1、实验六 触发器、存储过程编程实验一、实验目的学习存储过程和触发器的创建和使用方法。二、实验环境硬件：PC 机软件：SQL Server 2000三、实验原理1. 触发器触发器是一种特殊的过程，它不带参数，不被用户和程序调用，只能由用户对数据库中的表的操作（插入、删除、修改）触发。因此，可以利用触发器来维护表间的数据一致性。触发器只能在表上建立，一张表最多可有 3 个触发器，即插入触发器、删除触发器、修改触发器，分别由插入、删除、修改操作触发。触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则及数据完整性。创建触发器创建触发器时需指定： 名称。 在其上定义触发
2、器的表。 触发器将何时激发。 激活触发器的数据修改语句。有效选项为 INSERT、UPDATE 或 DELETE。多个数据修改语句可激活同一个触发器。例如，触发器可由 INSERT 或 UPDATE 语句激活。 执行触发操作的编程语句。语法：CREATE TRIGGER 触发器名ON 表名 | 视图名 FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE ASSQL 语句块RETURN 使用 inserted 和 deleted 表触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。Microsoft SQL Server
3、2000 自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。inserted 和 deleted 表主要用于触发器中： 扩展表间引用完整性。 在以视图为基础的基表中插入或更新数据。 检查错误并基于错误采取行动。 找到数据修改前后表状态的差异，并基于此差异采取行动。 Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。Inserted 表用
4、于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。更新事务类似于在删除之后执行插入；首先旧行被复制到 deleted 表中，然后新行被复制到触发器表和 inserted 表中。在设置触发器条件时，应当为引发触发器的操作恰当使用 inserted 和 deleted 表。虽然在测试 INSERT 时引用 deleted 表或在测试 DELETE 时引用 inserted 表不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何行。删除触发器从当
5、前数据库中删除一个或多个触发器。语法：DROP TRIGGER 触发器名 ,.n 2. 自定义数据类型SQL Server 2000 允许用户定义自己的数据类型。创建用户定义的数据类型 语法：sp_addtype 类型名, 系统数据类型名, 属性删除用户定义的数据类型 语法：sp_droptype 类型名查看用户定义的数据类型 语法：sp_help 类型名 3. 函数SQL Server 2000 支持两种函数类型： 内置函数 按 Transact-SQL 参考中定义的方式运行且不能修改。只有使用 Transact-SQL 参考中所定义语法的 Transact-SQL 语句才能引用这类函数。
6、用户定义函数 可以用 CREATE FUNCTION 语句定义自己的 Transact-SQL 函数。创建函数语法：CREATE FUNCTION 函数名( 参数 AS 类型 = default ,.n ) RETURNS 函数返回值类型 | TABLEAS BEGIN函数体语句RETURN 函数返回值 | SELECT 语句END函数调用语法：Declare 变量名 数据类型 Select 变量名= 函数名 ( 参数表达式 ,. )删除函数语法：DROP FUNCTION 函数名 ,.n 4. 存储过程存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。它
7、在建立时由 RDBMS 编译和优化，其执行代码存储于数据库中的程序中。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 存储过程分为两类：系统存储过程和自定义存储过程。系统存储过程在系统安装时自动装载于系统数据库中，便于用户或数据库管理员管理和维护数据库中的各种数据信息和对象。系统存储过程以“sp_”开头。用户自定义存储过程，由用户定义。创建存储过程语法：CREATE PROC 过程名 ( 参数变量 数据类型 = default OUTPUT ) ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE ,
8、 ENCRYPTION AS SQL 语句 .n RETURN 存储过程执行状态执行存储过程可在命令行或批中调用。如在批中调用，应加 EXEC 过程名 参数 OUTPUT ,.n删除存储过程语法：DROP PROC 过程名查看存储过程语法：sp_helptext 过程名更改存储过程名称语法：sp_rename 旧过程名, 新过程名四、实验内容1. 利用 T-SQL 语句创建触发器，并测试其作用要求：在 person 表上创建一个触发器，当删除表 person 中的员工信息时，级联删除表 pay中该员工的工资信息。参考：CREATE TRIGGER person_delON person FOR
9、 DELETEASIF rowcount=0RETURNDELETE payFROM pay t, deleted dWHERE t.No=d.NoRETURN 注：rowcount=0 是 SQL Server 提供的系统变量，其值表示表中有几行记录被删除了。在 pay 表上创建一个触发器，检查在修改该表时是否有不存在于 person 表中的员工代码出现。参考：CREATE TRIGGER pay_updateON pay FOR UpdateASDeclare num_rows intSelect num_rows=rowcountIF num_rows=0RETURNIF (Select
10、 count(*)From person p, inserted iWhere p.No=i.No) !=num_rowsBeginraiserror 33333 试图修改非法的员工号到 pay 表中rollback transactionreturnEndRETURN 在 pay 表上创建一个触发器，当向该表插入数据时，必须参考表 person 中的 No。参考：CREATE TRIGGER pay_insertON pay FOR InsertASDeclare num_rows intSelect num_rows=rowcountIF num_rows=0RETURNIF (Selec
11、t count(*)From person p, inserted iWhere p.No=i.No) !=num_rowsBeginraiserror 33333 试图插入非法的员工号到 pay 表中rollback transactionreturnEndRETURN 2. 利用 T-SQL 语句自定义数据类型要求：定义一个数据类型 d_no，将其长度定义为 2B，并以此来重新定义 dept 表。参考：sp_addtype d_no, char(2), NOT NULLgocreate table dept1(deptno d_no,deptname varchar(10) Not Nul
12、l)3. 利用 T-SQL 语句创建函数，并调用要求：创建一个函数 Fun_CheckNo，检测给定的员工号是否存在，如果存在返回 0，否则返回-1。参考：Create function Fun_CheckNo(pno char(6)Returns integer as BeginDeclare num IntIf Exists (Select no from personWhere no=pno)Select num=0ElseSelect num=-1Return numEND调用函数 Fun_CheckNo，如果返回 0，则向表 pay 中插入一行该员工的工资记录。参考：Declare
13、num IntSelect num=DBO. Fun_CheckNo(000008)If num=0Insert pay values(000008,2005,2,2200,280,12.4)4. 利用 T-SQL 语句创建存储过程，并调用要求：创建一个存储过程 Pro_CalAge，根据 person 表中的出生日期计算其实际年龄。参考：Create PROC Pro_CalAge code char(6), age int OUTPUTAsDeclare birth varchar(4), today varchar(4)Select birth=DATENAME(year,birthda
14、y)From personWhere no=codeSelect today=DATENAME(year,GETDATE()Select age=CONVERT(INT,today)-CONVERT(INT,birth)调用存储过程 Pro_CalAge，计算工号为000001的员工实际年龄。参考：Declare age IntExecute Pro_CalAge 000001, age outputPrint age 五、练习利用 T-SQL 语句，在 person 表上创建一个触发器，当修改表 person 中的员工工号时，级联修改表 pay 中该员工的工号信息。利用企业管理器，完成以上实验内容。
