SQL存储过程和触发器.pptx

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