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

实验六 触发器、存储过程编程实验一、实验目的学习存储过程和触发器的创建和使用方法。二、实验环境硬件：PC机软件：SQL Server 2000三、实验原理1. 触发器触发器是一种特殊的过程，它不带参数，不被用户和程序调用，只能由用户对数据库中的表的操作（插入、删除、修改）触发。因此，可以利用触发器来维护表间的数据一致性。触发器只能在表上建立，一张表最多可有3个触发器，即插入触发器、删除触发器、修改触发器，分别由插入、删除、修改操作触发。触发器可以查询其它表，而且可以包含复杂的SQL语句。它们主要用于强制复杂的业务规则及数据完整性。创建触发器创建触发器时需指定：l 名称。l 在其上定义触发器的表。l 触发器将何时激发。l 激活触发器的数据修改语句。有效选项为INSERT、UPDATE或DELETE。多个数据修改语句可激活同一个触发器。例如，触发器可由INSERT或UPDATE语句激活。l 执行触发操作的编程语句。语法：CREATE TRIGGER 触发器名 ON 表名 | 视图名 FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS SQL语句块 RETURN 使用inserted和deleted表触发器语句中使用了两种特殊的表：deleted表和inserted表。Microsoft SQL Server 2000 自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。inserted 和 deleted 表主要用于触发器中： l 扩展表间引用完整性。l 在以视图为基础的基表中插入或更新数据。l 检查错误并基于错误采取行动。l 找到数据修改前后表状态的差异，并基于此差异采取行动。 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 支持两种函数类型： l 内置函数 按Transact-SQL参考中定义的方式运行且不能修改。只有使用Transact-SQL参考中所定义语法的Transact-SQL语句才能引用这类函数。l 用户定义函数 可以用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 NULLgocreate 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 personWhere no=pno)Select num=0 Else Select num=-1 Return num END调用函数Fun_CheckNo，如果返回0，则向表pay中插入一行该员工的工资记录。参考：Declare num IntSelect 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 OUTPUTAs 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,tod