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

实验六触发器、存储过程编程实验一、实验目的学习存储过程和触发器的创建和使用方法。二、实验环境硬件：PC机软件：SQLServer2000三、实验原理1.触发器触发器是一种特殊的过程，它不带参数，不被用户和程序调用，只能由用户对数据库中的表的操作（插入、删除、修改）触发。因此，可以利用触发器来维护表间的数据一致性。触发器只能在表上建立，一张表最多可有3个触发器，即插入触发器、删除触发器、修改触发器，分别由插入、删除、修改操作触发。触发器可以查询其它表，而且可以包含复杂的SQL语句。它们主要用于强制复杂的业务规则及数据完整性。⑴创建触发器创建触发器时需指定：名称。在其上定义触发器的表。触发器将何时激发。激活触发器的数据修改语句。有效选项为INSERT、UPDATE或DELETE。多个数据修改语句可激活同一个触发器。例如，触发器可由INSERT或UPDATE语句激活。执行触发操作的编程语句。语法：CREATETRIGGER触发器名ON{表名|视图名}{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASSQL语句块RETURN⑵使用inserted和deleted表触发器语句中使用了两种特殊的表：deleted表和inserted表。MicrosoftSQLServer2000自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。inserted和deleted表主要用于触发器中：扩展表间引用完整性。在以视图为基础的基表中插入或更新数据。检查错误并基于错误采取行动。找到数据修改前后表状态的差异，并基于此差异采取行动。Deleted表用于存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。Deleted表和触发器表通常没有相同的行。Inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。更新事务类似于在删除之后执行插入；首先旧行被复制到deleted表中，然后新行被复制到触发器表和inserted表中。在设置触发器条件时，应当为引发触发器的操作恰当使用inserted和deleted表。虽然在测试INSERT时引用deleted表或在测试DELETE时引用inserted表不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何行。⑶删除触发器从当前数据库中删除一个或多个触发器。语法：DROPTRIGGER触发器名[,...n]2.自定义数据类型SQLServer2000允许用户定义自己的数据类型。⑴创建用户定义的数据类型语法：sp_addtype‘类型名’,‘系统数据类型名’,‘属性’⑵删除用户定义的数据类型语法：sp_droptype‘类型名’⑶查看用户定义的数据类型语法：sp_help‘类型名’3.函数SQLServer2000支持两种函数类型：内置函数按Transact-SQL参考中定义的方式运行且不能修改。只有使用Transact-SQL参考中所定义语法的Transact-SQL语句才能引用这类函数。用户定义函数可以用CREATEFUNCTION语句定义自己的Transact-SQL函数。⑴创建函数语法：CREATEFUNCTION函数名([{@参数[AS]类型[=default]}[,...n]])RETURNS函数返回值类型|TABLEASBEGIN函数体语句RETURN函数返回值|SELECT语句END⑵函数调用语法：[Declare变量名数据类型][Select@变量名=]函数名([参数表达式][,...])⑶删除函数语法：DROPFUNCTION函数名[,...n]4.存储过程存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。它在建立时由RDBMS编译和优化，其执行代码存储于数据库中的程序中。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。存储过程分为两类：系统存储过程和自定义存储过程。系统存储过程在系统安装时自动装载于系统数据库中，便于用户或数据库管理员管理和维护数据库中的各种数据信息和对象。系统存储过程以“sp_”开头。用户自定义存储过程，由用户定义。⑴创建存储过程语法：CREATEPROC过程名[(@参数变量数据类型[=default][OUTPUT])][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSQL语句[...n][RETURN[存储过程执行状态]]⑵执行存储过程可在命令行或批中调用。如在批中调用，应加EXEC过程名[参数[OUTPUT]][,...n]⑶删除存储过程语法：DROPPROC过程名⑷查看存储过程语法：sp_helptext过程名⑸更改存储过程名称语法：sp_rename旧过程名,新过程名四、实验内容1.利用T-SQL语句创建触发器，并测试其作用要求：⑴在person表上创建一个触发器，当删除表person中的员工信息时，级联删除表pay中该员工的工资信息。参考：CREATETRIGGERperson_delONpersonFORDELETEASIF@@rowcount=0RETURNDELETEpayFROMpayt,deleteddWHEREt.No=d.NoRETURN注：@@rowcount=0是SQLServer提供的系统变量，其值表示表中有几行记录被删除了。⑵在pay表上创建一个触发器，检查在修改该表时是否有不存在于person表中的员工代码出现。参考：CREATETRIGGERpay_updateONpayFORUpdateASDeclare@num_rowsintSelect@num_rows=@@rowcountIF@num_rows=0RETURNIF(Selectcount(*)Frompersonp,insertediWherep.No=i.No)!=@num_rowsBeginraiserror33333'试图修改非法的员工号到pay表中'rollbacktransactionreturnEndRETURN⑶在pay表上创建一个触发器，当向该表插入数据时，必须参考表person中的No。参考：CREATETRIGGERpay_insertONpayFORInsertASDeclare@num_rowsintSelect@num_rows=@@rowcountIF@num_rows=0RETURNIF(Selectcount(*)Frompersonp,insertediWherep.No=i.No)!=@num_rowsBeginraiserror33333'试图插入非法的员工号到pay表中'rollbacktransactionreturnEndRETURN2.利用T-SQL语句自定义数据类型要求：定义一个数据类型d_no，将其长度定义为2B，并以此来重新定义dept表。参考：sp_addtyped_no,'char(2)','NOTNULL'gocreatetabledept1(deptnod_no,deptnamevarchar(10)NotNull)3.利用T-SQL语句创建函数，并调用要求：⑴创建一个函数Fun_CheckNo，检测给定的员工号是否存在，如果存在返回0，否则返回-1。参考：CreatefunctionFun_CheckNo(@pnochar(6))ReturnsintegerasBeginDeclare@numIntIfExists(SelectnofrompersonWhereno=@pno)Select@num=0ElseSelect@num=-1Return@numEND⑵调用函数Fun_CheckNo，如果返回0，则向表pay中插入一行该员工的工资记录。参考：Declare@numIntSelect@num=DBO.Fun_CheckNo('000008')If@num=0Insertpayvalues('000008',2005,2,2200,280,12.4)4.利用T-SQL语句创建存储过程，并调用要求：⑴创建一个存储过程Pro_CalAge，根据person表中的出生日期计算其实际年龄。参考：CreatePROCPro_CalAge@codechar(6),@ageintOUTPUTAsDeclare@birthvarchar(4),@todayvarchar(4)Select@birth=DATENAME(year,birthday)FrompersonWhereno=@codeSelect@today=DATENAME(year,GETDATE())Select@age=CONVERT(INT,@today)-CONVERT(INT,@birth)⑵调用存储过程Pro_CalAge，计算工号为’000001’的员工实际年龄。参考：Declare@ageIntExecutePro_CalAge'000001',@ageoutputPrint@age五、练习⑴利用T-SQL语句，在person表上创建一个触发器，当修改表person中的员工工号时，级联修改表pay中该员工的工号信息。⑵利用企业管理器，完成以上实验内容。