《数据库应用技术第八章存储过程和触发器》由会员分享，可在线阅读，更多相关《数据库应用技术第八章存储过程和触发器（45页珍藏版）》请在金锄头文库上搜索。
1、第8章 存储过程和触发器1复习1、视图和索引的作用？2、索引的分类？2答案1、Group by，Order by。2、where，having。3教学目标掌握：存储过程和触发器的创建和执行以及管理；理解：存储过程和触发器的概念和作用；了解：存储过程与触发器的特点和分类，系统存储过程和 扩展存储过程的使用。4教学重点： 存储过程和触发器的创建、查看、修改和删除教学难点： 存储过程和触发器的概念和作用5存储过程和触发器的作用？6教学内容教学内容8.8 触发器使用注意事项8.7 管理触发器8.6 创建触发器8.5 触发器简介8.4 系统存储过程和扩展存储过程8.3 管理存储过程8.2 存储过程的创建
2、和执行8.1 存储过程概述78.1 存储过程概述存储过程概述存储过程81增强代码的可重用性，提高开发效率4可以提高数据的安全性3可以减少网络流量2执行速度快9系统存储过程扩展存储过程临时存储过程系统存储过程临时存储过程扩展存储过程系统存储过程临时存储过程本地存储过程扩展存储过程系统存储过程临时存储过程108.2 存储过程的创建和执行格式文本加密参数为输出类型11【例8.1】在“图书管理”数据库中建立一个名为“图书出版情况”的存储过程，该存储过程从“图书”、“作者”和“出版社”表中检索所有图书的“图书名”、“作者名”和相应的“出版社名”。USE 图书管理GOCREATE PROCEDURE 图书
3、出版情况AS SELECT 图书名,作者名,出版社名 FROM 图书 AS A INNER JOIN 作者 AS B ON A.作者号=B.作者号 INNER JOIN 出版社 AS C ON A.出版社号=C.出版社号GO-执行存储过程EXEC 图书出版情况GO12【例8.2】在“图书管理”数据库中建立一个名为“作者查询”的存储过程，该存储过程从“作者”、“图书”和“出版社”表中检索指定作者的“作者名”，以及该作者出版图书的“图书名”和相应的“出版社名”。要求将作者姓名通过参数传递给存储过程。USE 图书管理GOCREATE PROCEDURE 作者查询 name varchar(10)AS
4、 SELECT 作者名,图书名,出版社名 FROM 作者 AS A INNER JOIN 图书 AS B ON A.作者号=B.作者号 INNER JOIN 出版社 AS C ON B.出版社号=C.出版社号 WHERE 作者名=nameGO-执行存储过程EXEC 作者查询 任明GO13执行存储过程格式：EXECUTE 存储过程名 参数=值 | 变量 OUTPUT ，【例8.3】执行例7.20建立的存储过程“图书出版情况”EXEC 图书出版情况GO14 查看存储过程的定义格式：sp_helptext 存储过程名【例8.5】查看存储过程“图书出版情况”的定义。USE 图书管理-下一行语句不是批处
5、理中的第一条语句，因此必须加上“EXECUTE”EXEC sp_helptext 图书出版情况GO15 查看存储过程的参数格式：sp_help 存储过程名【例8.6】 查看存储过程“作者查询”的参数。USE 图书管理GOsp_help 作者查询GO 查看存储过程的相关性格式：sp_depends 存储过程名16 查看存储过程的相关性格式：sp_depends 存储过程名【例8.6】 查看存储过程“作者查询”的相关性。USE 图书管理GOsp_depends 作者查询GO172使用T-SQL语句修改存储过程格式：ALTER PROCEDURE 存储过程名 参数 数据类型=默认值 OUTPUT ，
6、 WITH ENCRYPTIONASSQL语句块【例8.8】修改例7.21建立的存储过程“作者查询”，要求对此存储过程进行加密，其他不变。ALTER PROCEDURE 作者查询 name varchar(10) WITH ENCRYPTIONAS SELECT 作者名,图书名,出版社名 FROM 作者 AS A INNER JOIN 图书 AS B ON A.作者号=B.作者号 INNER JOIN 出版社 AS C ON B.出版社号=C.出版社号 WHERE 作者名=nameGOEXEC 作者查询 任明GO18重命名存储过程格式：sp_rename 存储过程原名，存储过程新名【例8.9】
7、将存储过程“图书出版情况”更名为“图书情况”。USE 图书管理GOsp_rename 图书出版情况,图书情况GO19删除存储过程格式：DROP PROCEDURE 存储过程名，【例8.10】使用T-SQL命令删除存储过程“图书情况”。USE 图书管理GODROP PROCEDURE 图书情况GO20系统存储过程系统存储过程功能sp_addlogin在服务器增加标准登录名，使用户可以访问整个服务器sp_databases列出服务器可用的数据库名，用于寻找数据库sp_depends列出引用或依赖于指定对象的所有对象sp_help列出指定对象的的一般信息sp_helptext显示指定对象的文本sp_
8、help *是指以sp_help开头的一组系统存储过程，这些系统存储过程提供指定对象的某种特定信息sp_password改变标准登录的口令sp_stored_procedures列出所有可用的存储过程名和自定义函数名sp_tables列出所有可用的表名和视图名sp_who列出使用数据库的用户sp_rename改变指定对象的名称21扩展存储过程扩展存储过程功能xp_CmdShell运行在命令提示符下执行的命令xp_FileExist测试文件是否存在xp_FixedDrives显示硬盘的盘符和容量228.5 触发器简介触发器简介存储过程231与一个表或视图相联系4是一个事务3不能直接调用2自动激活
9、1与一个表或视图相联系3不能直接调用1与一个表或视图相联系4是一个事务3不能直接调用1与一个表或视图相联系2自动激活4是一个事务3不能直接调用1与一个表或视图相联系24系统存储过程扩展存储过程临时存储过程系统存储过程临时存储过程扩展存储过程系统存储过程临时存储过程本地存储过程扩展存储过程系统存储过程临时存储过程258.2 存储过程的创建和执行格式文本加密参数为输出类型2627触发器的类型AFTER触发器INSTEAD OF触发器这种类型的触发器在数据变动（INSERT、DELETEUPDATE操作）完成以后才被触发。AFTER触发器只能在表上定义，每个表可以创建多个AFTER触发器。这种类型的
10、触发器在数据变动以前被触发，并取代变动数据的操作（INSERT、DELETE、UPDATE操作），即变动数据的操作并不执行，而是执行触发器定义的操作。INSTEAD OF触发器可以在表或视图上定义，每个表或视图的每INSERT、DELETE、UPDATE操作只能定义一个INSTEAD OF触发器。288.6 创建触发器创建触发器格式：CREATE TRIGGER 触发器名 ON 表名|视图名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT，DELETE，UPDATE AS SQL语句块29系统存储过程系统存储过程系统存储过程DELETE触发器INSERT
11、触发器UPDATE触发器3031AFTER触发器 INSERT触发器INSERT触发器的工作过程如下：当向触发器表插入数据时，INSERT触发器被激活；新数据行被插入到触发器表和临时表Inserted中；执行触发器中定义的语句。【例8.16】请在“借阅”表中创建一个触发器“Ins_borrower_tr1”，当向“借阅”表中插入记录时，检查“图书”表中是否存在相应的图书，即检查是否存在相应的“图书号”， 如果不存在则不允许向“借阅”表中插入记录，即只有图书存在才可以借阅此图书。32-创建INSERT触发器USE 图书管理IF EXISTS (SELECT * FROM sysobjects W
12、HERE name=Ins_borrower_tr1 and type=TR ) DROP TRIGGER Ins_borrower_tr1GOCREATE TRIGGER Ins_borrower_tr1 ON 借阅 FOR INSERTAS IF EXISTS (SELECT 图书号 FROM Inserted WHERE 图书号 not IN (SELECT 图书号 FROM 图书) ) BEGIN PRINT 抱歉，该图书不存在，不能借阅！ ROLLBACK TRAN ENDGO33 DELETE触发器 对触发器表执行DELETE操作时，DELETE触发器被激活； 删除触发器表中的记录
13、，并将被删除记录放入临时表Deleted表中； 执行触发器中定义的语句。【例8.18】请在“读者”表中创建一个触发器“Del_reader_tr”，当删除“读者”表中的某个记录时，检查在“借阅”表中是否存在相应的读者，即检查是否存在相应的“借书证号”，如果存在则不允许删除“读者”表中相应的记录，即如果读者借书未还则不能被注销。34-建立DELETE触发器USE 图书管理IF EXISTS (SELECT * FROM sysobjects WHERE name=Del_reader_tr and type=TR ) DROP TRIGGER Del_reader_trGOCREATE TRIG
14、GER Del_reader_tr ON 读者 FOR DELETEAS IF EXISTS (SELECT 借书证号 FROM Deleted WHERE 借书证号 IN (SELECT 借书证号 FROM 借阅) ) BEGIN PRINT 抱歉，该读者借书未还，不能注销！ ROLLBACK TRAN ENDGO35 UPDATE触发器对触发器表执行UPDATE操作时，UPDATE触发器被激活；更新触发器表中的记录，并将原始记录放入临时表Deleted中，将更新后的记录放入临时表Inserted表中；执行触发器中定义的语句。36INSTEAD OF触发器37 查看触发器的定义信息格式：sp
15、_helptext 触发器名【例8.25】查看触发器“Update_publishID_tr”的定义信息。USE 图书管理-下一行语句不是批处理中的第一条语句，因此必须加上“EXEC”EXEC sp_helptext Update_publishID_trGO管理触发器管理触发器38 查看触发器的一般信息格式：sp_help 触发器名【例8.26】查看触发器“Update_publishID_tr”的名称、所有者、类型以及建立时间。USE 图书管理GOsp_help Update_publishID_trGO39修改触发器格式：ALTER TRIGGER 触发器名 ON 表名|视图名 WITH
16、 ENCRYPTION FOR|AFTER|INSTEAD OF INSERT，DELETE，UPDATE AS SQL语句块40【例8.23】修改例8.4建立的触发器“Del_au_tr”，要求对此触发器进行加密，其他要求不变。USE 图书管理GOALTER TRIGGER Del_au_tr ON 作者 WITH ENCRYPTION FOR DELETEAS IF EXISTS (SELECT * FROM Deleted WHERE 作者号 IN (SELECT 作者号 FROM 图书) ) DELETE 图书 WHERE 作者号 IN (SELECT 作者号 FROM Deleted
17、)GO41触发器使用注意事项触发器使用注意事项 创建触发器的语句CREATE TRIGGER必须为批处理中的第一条语句，且在该批处理中，CREATE TRIGGER语句之后不能有其他的语句，否则SQL会将这些语句作为触发器定义的一部分，因此创建触发器的语句CREATE TRIGGER应作为一个独立的批处理。 触发器不能定义在临时表和系统表中，但是触发器可以引用临时表。 由于TRUNCATE TABLE语句没有日志，因此TRUNCATE TABLE语句不会激活DELETE触发器。 CREATE TRIGGER语句只能在当前数据库中建立触发器，但在触发器内可以引用其他数据库中的数据库对象。 在一个
18、触发器中可以使用各种SET语句设置连接选项，但它们所设置的连接选项只在触发器内有效，当触发器执行之后，各连接选项恢复到触发器激活前的状态值。42 触发器的主要用途是检查或修改数据，而不是与用户交互，因此在触发器内不要使用能够返回结果集合的SELECT等语句。 如果在一个触发器内包含有ROLLBACK TRANSACTION语句，则：如果激活触发器的语句处于一个用户定义事务之内，触发器内的 ROLLBACK TRANSACTION语句执行后将回滚整个事务。当批处理中的某个语句激活触发器后，将导致批处理中该语句后的所有语句被取消执行。 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用
19、户。触发器创建者必须拥有在所受影响表上执行触发器所定义的所有语句的权限，如果触发器中任何部分的T-SQL语句的权限被拒绝，则回滚整个事务。43推荐的触发器实现方案 只在必要时使用触发器，在使用触发器之前应考虑其限制条件。 触发器应尽可能定义得简单。 在递归触发器定义中必须包含递归结束检查，以防止触发器无穷循环。 应尽可能少用ROLLBACK语句，因为在回滚事务时，系统必须取消在该点之前所做的全部动作。44本章介绍了存储过程的开发和触发器的实现。存储过程和触发器都是SQL Server的数据库对象。 存储过程独立于表存在，它是存放在服务器上的、预先编译好的T-SQL语句的集合，它以一个名称存储在数据库中，并作为一个单元来处理，其可以由应用程序通过一个调用来执行。本章介绍了存储过程的建立、执行、查看、修改、重命名以及删除。 触发器是一种特殊类型的存储过程，其在特定的表或视图上定义，当表中的数据被修改时，SQL Server自动执行触发器。对表中的数据进行修改的操作包括INSERT、DELETE、UPDATE操作，如果对某个表的INSERT、DELETE、UPDATE操作定义了触发器，则对该表执行这些操作时，相应的触发器就会被自动执行。使用触发器可以实现更为复杂的数据完整性约束，可以完成使用普通约束无法实现的复杂功能。本章小结45
