SQL课件存储过程和触发器

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,上页,返回,第10章 存储过程和触发器,教学提示：开发中编写的一些SQL语句会占用程序的很大篇幅，而且不便于在其他地方重用，且由于这些SQL语句通常还要跨越传输途径从外部不仅会导致程序的运行效率低，还会产生安全隐患，而存储过程则能克服以上的缺陷。,触发器可以大大增强应用程序的健壮性、数据库可恢复性和数据库的可管理性。,存储过程和触发器都是SQL Server的数据库对象。存储过程的存在独立于表，它存放在服务器上，供客户端调用。,1,教学目标：本章主要包含以下内容。,存储过程概述,设计存储过程,实现和管理存储过程,触发器概述,设计触发器,实现和管理触发器,通过本章的学习，使读者掌握存储过程和触发器的基础知识，并基本学会编写简单的存储过程和触发器，为以后在实际应用中不断提高自己编写存储过程和触发器的技能打下良好的基础。,2,第10章 存储过程和触发器,10.1 存储过程概述,10.2 设计存储过程,10.3 实现和管理存储过程,10.4 触发器概述,10.5 触发器设计规则,10.6 实现和管理触发器,3,10.1 存储过程概述,10.1.1 存储过程的特点,能够在单个存储过程中执行一系列的Transact-SQL语句，能够以在一个存储过程中调用其他的存储过程。,存储过程是保存在服务器端的已经编译的Transact-SQL语句。,存储过程可以使用控制流语句和变量，大大增强了SQL的功能。,存储过程在提交前会自动检查语法。,存储过程是管理员放在服务器端的Transact-SQL语句。,4,10.1.2 存储过程的分类,1. 用户存储过程,2. 系统存储过程,3. 扩展存储过程,5,10.2 设计存储过程,10.2.1 一些设计规则,用户在设计数据库(的存储过程)时，应遵守以下规则：,在SQL Server 2005中，存储过程可以使用Transact-SQL中的任何语句，但是表10.1中的语句除外。,略,6,10.2.2 存储过程的内部命名规范化,存储过程中的Transact-SQL语句使用的对象名称必须符合规范的模式，如果未指明对象所属的模式，存储过程将使用当前默认的模式。,每个存储过程必须有惟一的名称，存储过程中使用的变量类型必须符合Transact-SQL的要求，变量名必须以开头，并且存储过程的名称和变量的命名要符合Transact-SQL标识符规则。,7,10.2.3 存储过程的加密定义,如果用户想定义一个不让其他用户看到具体操作的存储过程，可以使用WITH ENCRYPTION参数，那么存储过程将会以不可读的状态存在，并且存储过程拥有者、创建者和数据库管理员都不可见。,8,10.3 实现和管理存储过程,10.3.1 创建存储过程,由于必须使用Transact-SQL语句创建存储过程，所以用户必须首先掌握创建存储过程的Transact-SQL语法结构，然后再学习如何使用Management Studio创建存储过程。,9,10.3.2 执行存储过程,执行存储过程有多种方式，比较常用的有以下几种。,1. 通过Execute或Exec语句执行,2. 通过设置，使存储过程自动执行,3. 作为批处理的第一行，直接输入存储过程名,10,10.3.3 修改存储过程,1. 修改存储过程的语法,注意：修改存储过程的名称会影响已关联对象对此存储过程的调用。,2. 使用Management Studio修改存储过程,11,10.3.4 重新编译存储过程,1.sp_recompile系统存储过程可以强制指定的存储过程在下次调用时重新编译。其调用的语法结构为：sp_recompile objname = object，其中的object为存储过程的名称。,2.在创建存储过程时使用WITH RECOMPILE参数项，SQL Server将不会把此存储过程放在缓冲存储器中，并且每次调用此存储过程时都会重新编译。,3.在调用存储过程时，可以使用参数选项WITH RECOMPILE强制重新编译。使用这个参数项要求存储过程的参数必须是非典型的或者数据发生了很大的变化，否则一般不用它。,12,10.3.5 删除存储过程,如果一个存储过程不再需要，用户就可以删除它，但是如果此存储过程有关联存储过程调用，系统会返回错误信息。但是删除存储过程后，用户重新定义的同名称同参数的存储过程可以被原来关联的对象使用。,删除存储过程的Transact-SQL语法结构为：,DROP PROCEDURE schema_name. procedure ,.n ,13,10.4 触发器概述,触发器是一种特殊类型的存储过程，这种存储过程能够在某个规定的事件发生时触发执行。触发器通常可以强制执行一定的业务规则，以保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加的功能。,触发器是数据库中比较高级的应用，灵活使用触发器可以大大增强应用程序的健壮性、数据库的可恢复性和数据库的可管理性。同时可以使开发人员和数据库管理员实现一些复杂的功能，简化开发步骤，降低开发成本，增加开发效率，提高数据库的可靠性。,14,10.4.1 触发器的特点,触发器是一种特殊的存储过程，除了存储过程的特点外，它还另外有以下特点：,触发器是自动执行的，可以在一定条件下触发。,触发器可以同步数据库的相关表，进行级联更改。,触发器可以实现更复杂的安全检查。它可以实现比CHECK更复杂的业务规则，还可以引用其他表中的列。,触发器可以实现数据库的管理任务。如DDL触发器，在DDL语句执行后触发，可以实现一些统一的数据库管理策略。,15,10.4.2 触发器的种类,1. DML触发器,DML触发器是在执行数据操作语言事件时被调用的触发器，其中数据操作语言事件包括：INSERT、UPDATE和DELETE语句。触发器中可以包含复杂的Transact-SQL语句，触发器整体被看作一个事务，可以回滚。,2. DDL触发器,与DML触发器类似，与DML不同的是，它相应的触发事件是由数据定义语言引起的事件，包括：CREATE、ALTER和DROP语句，DDL触发器用于执行数据库管理任务，如调节和审计数据库运转。DDL触发器只能在触发事件发生后才会调用执行，即它只能是AFTER类型的。,16,10.4.3 inserted表和deleted表,触发器中两个比较特殊的对象：inserted表和deleted表。在使用触发器过程中，SQL Server使用到了两张特殊的临时表，分别是inserted表和deleted表。这两张表都存在于高速缓存中(当然，如果内存不够用，也可能存储在硬盘上)，实际上是事务日志的视图，它们与创建了触发器的表有着相同的结构。,17,10.5 触发器设计规则,10.5.1 确定触发器的种类,首先用户应根据相应的触发事件确定到底是DML触发器还是DDL触发器，如果是DML触发器，用户在要根据数据操作类型确定是INSERT、UPDATE还是DELETE触发器，然后再根据约束规则，是希望触发事件发生后还是发生前来触发执行触发器，从而确定是AFTER还是INSTEAD OF类型的触发器。,对于DDL触发器，它只有AFTER一种类型，只需根据触发事件的类型确定触发器，来做出相应的响应。,18,10.5.2 确定触发器的响应范围,对于DML触发器，其响应范围是在数据库范围中，我们无需讨论。而对于DDL触发器，就需要确定它的响应范围是数据库级的还是服务器级的响应。在此只列出了SQL Server 2005中服务器级的DDL触发事件，如表10.2所示，其他的DDL事件都是数据库级的。,19,10.5.3 一些设计规则,在设计触发器时，用户可以参考以下的设计规则：,DML触发器的实现者是表的默认拥有者，权限不能转移给别的用户。,DML触发器必须是在当前数据库上创建，尽管它可以引用别的数据库。,不能对系统表和临时表创建触发器。,每个表可以有多个不同名称的AFTER触发器，但每种触发事件只能有一个INSTEAD OF类型触发器。,触发器只能创建在表或者视图的模式中。,20,10.6 实现和管理触发器,10.6.1 创建触发器,1. 创建DML触发器的语法,2. 创建DDL触发器的语法,3. 创建DML触发器的步骤,4. 创建作用范围为数据库的DDL触发器的步骤,5. 创建作用范围为服务器的DDL触发器,21,10.6.2 更改触发器,当一个触发器必须修改时，用户可以先删除此触发器然后重建新的触发器，也可以使用Transact-SQL语句对它进行修改。修改DML和DDL触发器的语法结构与创建它们的语法结构类似，除了使用的开始关键词变为ALTER和在修改DML触发器时不能使用WITH APPEND参数选项外，其他语法结构都相同，在此我们不再给出详细的语法列表。,在Microsoft SQL Server Management Studio中更改触发器的具体步骤如下。略,22,10.6.3 删除触发器和停止触发器,当一个触发器不再使用时，用户可以删除它，也可以停止使用它，停止使用的触发器没有被删除，仍然保存在数据库中，当用户想要使用的时候还可以激活它，新创建的触发器默认为激活状态。,1. 使用Management Studio删除触发器,2. 使用Transact-SQL删除触发器和停止(disable)触发器,23,10.6.4 激活触发器,对于停止的触发器，用户可以重新激活。激活后的触发器就会在触发条件下正常运行。,激活触发器的Trasact-SQL语法如下：,ENABLE TRIGGER schema_name . trigger_name ,.n | ALL ,ON object_name | DATABASE | ALL SERVER ; ,24,