《最新存储过程与触发器PPT课件》由会员分享，可在线阅读，更多相关《最新存储过程与触发器PPT课件（29页珍藏版）》请在金锄头文库上搜索。
1、存储过程与触发器存储过程与触发器目目 录录教学目标教学目标 了解存储过程的特点、类型和作用了解存储过程的特点、类型和作用 理解和掌握理解和掌握存储过程的执行方式存储过程的执行方式 了解了解DML触发器触发器的工作原理的工作原理 理解和掌握理解和掌握DDL触发器的特点和创建方式触发器的特点和创建方式重点重点重点重点n*7.2.2 *7.2.2 创建参数化存储过程创建参数化存储过程n1.1.使用输入参数的准则使用输入参数的准则n 若要若要定义定义接受参数的接受参数的存储过程存储过程，应在，应在CREATE PROCEDURECREATE PROCEDURE语句中语句中声明声明参数参数。使用使用输入
2、参数时，应输入参数时，应考虑考虑以下以下准则准则：n（1 1）根据情况为参数）根据情况为参数提供提供默认值默认值。如果定义了默认值，则。如果定义了默认值，则用户无需为该参数用户无需为该参数指定指定值即可值即可执行执行存储过程。存储过程。n（2 2）在存储过程的开头）在存储过程的开头验证验证所有传入的所有传入的参数值参数值，以尽早查，以尽早查出缺少的值和无效值。出缺少的值和无效值。7.2 存储过程的实现存储过程的实现n2. 2. 使用输入参数的示例使用输入参数的示例n【案例案例7-47-4】修改修改存储过程存储过程LongLeadProducts,LongLeadProducts,添加添加参数参
3、数MinimumLength,MinimumLength,并并指定指定其为其为intint数据类型和其默认值为数据类型和其默认值为1 1。nALTER PROC Production.LongLeadProductsn MinimumLength int = 1 - default valuenASn IF (MinimumLength = 1n ORDER BY DaysToManufacture DESC, Namen GO7.2 存储过程的实现存储过程的实现n 7.2.5 7.2.5 更名或删除存储过程更名或删除存储过程n1. 1. 更名存储过程更名存储过程n 修改修改存储过程的名称存储
4、过程的名称可以可以使用使用系统存储过程系统存储过程sp_renamesp_rename，其，其语语法形式法形式如下：如下：n sp_renamesp_rename原存储过程名，新存储过程名原存储过程名，新存储过程名n2. 2. 删除存储过程删除存储过程n 若要从当前数据库中若要从当前数据库中删除删除用户定义的存储过程用户定义的存储过程，应使用应使用DROP DROP PROCEDUREPROCEDURE语句。其语句。其语法格式语法格式为：为：n DROP PROCEDURE DROP PROCEDURE 存储过程名存储过程名7.2 存储过程的实现存储过程的实现*7.3 触发器应用触发器应用 1
5、. 触发器的概念及类型触发器的概念及类型 触发器触发器是一种是一种特殊类型特殊类型的的存储过程存储过程，包括了大量的，包括了大量的T-SQL语句。但是触发器又与存储过程语句。但是触发器又与存储过程不同不同，例如存，例如存储过程可以由用户直接调用执行，但是触发器储过程可以由用户直接调用执行，但是触发器不能被不能被直接直接调用执行调用执行，它，它只能只能自动执行自动执行。 在语言事件执行时自动运行在语言事件执行时自动运行的一种特殊类型的存储过程的一种特殊类型的存储过程7.3 触发器应用触发器应用 1. 触发器的概念及类型触发器的概念及类型 按照按照触发事件触发事件的的不同不同，可以把，可以把SQL
6、 Server系统提供系统提供的触发器的触发器分成分成两大两大类型类型，即，即DML触发器和触发器和DDL触发器。触发器。 在在SQL Server中，可以中，可以创建创建CLR触发器触发器，既可以是，既可以是DML触发器，也可以是触发器，也可以是DDL触发器。触发器。 由于SQLServer 与.NET Framework 公共语言运行库公共语言运行库 (CLR) 相集成，可用任何 .NET Framework 语言创建 CLR 触发器。 7.3 触发器应用触发器应用 1. 触发器的概念及类型触发器的概念及类型 当数据库中当数据库中发生发生数据操纵语言数据操纵语言(data manipula
7、tion language，DML)事件事件时将时将调用调用DML触发器。触发器。DML事事件包括在指定表或视图中件包括在指定表或视图中修改修改数据的数据的INSERT语句、语句、UPDATE语句或语句或DELETE语句。语句。 按照按照触发器事件类型的不同，可以触发器事件类型的不同，可以把把SQL Server 系统系统提供提供的的DML触发器触发器分成分成3种类型种类型，即，即INSERT类型、类型、UPDATE类型和类型和DELETE类型。这也是类型。这也是DML触发器的基触发器的基本类型。本类型。 7.3 触发器应用触发器应用 1. 触发器的概念及类型触发器的概念及类型 任何触发器都可
8、以包含影响另外一个表的任何触发器都可以包含影响另外一个表的INSERT、UPDATE或或DELETE语句。当允许触发器语句。当允许触发器嵌套嵌套时，一个时，一个触发器可以修改触发第二个触发器的表，第二个触发器触发器可以修改触发第二个触发器的表，第二个触发器又可以触发第三个触发器。在默认情况下，系统允许触又可以触发第三个触发器。在默认情况下，系统允许触发器嵌套。发器嵌套。 7.3 触发器应用触发器应用 2. 触发器的用途触发器的用途 触发器触发器是一种特殊的是一种特殊的存储过程存储过程，在，在INSERT，UPDATE或或DELETE语句修改指定表中的数据时执语句修改指定表中的数据时执行。触发器
9、可查询其他表并且可包含复杂的行。触发器可查询其他表并且可包含复杂的T-SQL语句。人们通常创建触发器以往不同表中的逻辑相语句。人们通常创建触发器以往不同表中的逻辑相关数据之间实施引用完整性或一致性。关数据之间实施引用完整性或一致性。 7.3 触发器应用触发器应用 7.3.2 7.3.2 创建触发器创建触发器可使用可使用CREATE TRIGGER语句语句创建创建触发器，其触发器，其语法语法如下：如下： CREATE TRIGGER trigger_name ON table | view FOR |AFTER| INSTEAD OF| INSTERT|UPDATE|DELETE WITH AP
10、PEND AS sql_statement7.3 触发器应用触发器应用 7.3.2 7.3.2 创建触发器创建触发器 触发器的类型触发器的类型，有两类触发器，有两类触发器DML和和DDL，其中，其中，DML触发器也有触发器也有两类两类： （1） AFTER触发器。该触发器在执触发器。该触发器在执INSERT, UPDATE, DELETE语句的之后执行。语句的之后执行。 （2）INSTEAD OF触发器，该触发器替代常规触发操作触发器，该触发器替代常规触发操作执行执行,还可以在基于一个或多个基表的视图上定义。还可以在基于一个或多个基表的视图上定义。 7.3 触发器应用触发器应用 7.3.3 I
11、NSERT7.3.3 INSERT触发器的工作方式触发器的工作方式 当执行当执行INSERT语句将要数据插入表或视图时，语句将要数据插入表或视图时，如果该表或视图配置了如果该表或视图配置了INSERT触发器，就会激发该触发器，就会激发该INSERT触发器来执行特定的操作。触发器来执行特定的操作。 当当INSERT触发器触发时，新行将插入触发器和触发器触发时，新行将插入触发器和inserted表。表。inserted表是一个逻辑表，保留已插入表是一个逻辑表，保留已插入行的副本。行的副本。inserted表包含由表包含由INSERT语句引起的已语句引起的已记入日志的插入活动。记入日志的插入活动。
12、7.3 触发器应用触发器应用 7.3.4 DELETE7.3.4 DELETE触发器的工作方式触发器的工作方式 DELETE触发器是一种特殊的存储过程，它在每次触发器是一种特殊的存储过程，它在每次DELETE语句从配置了该触发器的表或者视图中删除数语句从配置了该触发器的表或者视图中删除数据时执行。据时执行。 当当DELETE触发器激发时，被删除的行将放置在特殊触发器激发时，被删除的行将放置在特殊的的deleted表中。表中。Deleted表是一个逻辑表，它保留已删表是一个逻辑表，它保留已删除行的副本。除行的副本。 7.3 触发器应用触发器应用 7.3.5 UPDATE7.3.5 UPDATE触
13、发器的工作方式触发器的工作方式 UPDATE触发器是在每次触发器是在每次UPDATE语句配置了语句配置了UPDATE触发器的表或视图中的数据进行更改时执行的触发器的表或视图中的数据进行更改时执行的触发器。触发器。UPDATE触发器的触发器的工作过程工作过程可视为可视为两步两步：（1）数据前映像的）数据前映像的DELETE步骤。步骤。（2）捕获数据后映像是）捕获数据后映像是INSERT语句。语句。 当当UPDATE语句在已定义了触发器的表上执行时，原语句在已定义了触发器的表上执行时，原始行（前映像）移入始行（前映像）移入deleted表，而更新行（后映像）插表，而更新行（后映像）插入入inser
14、ted表中。表中。7.3 触发器应用触发器应用 7.3.6 INSTEAD OF7.3.6 INSTEAD OF触发器的工作方式触发器的工作方式 INSTEAD OF触发器替代常规触发器操作执行。触发器替代常规触发器操作执行。INSTEAD OF触发器还可以在基于一个或多个基表的视触发器还可以在基于一个或多个基表的视图上定义。图上定义。INSTEAD OF触发器触发器主要具有主要具有优点优点：（1）允许由多个基表组成的视图支持引用表中数据的插）允许由多个基表组成的视图支持引用表中数据的插入、更新和删除操作。入、更新和删除操作。（2）允许编写逻辑语句以拒绝执行批处理的某些命令，）允许编写逻辑语句
15、以拒绝执行批处理的某些命令，同时不影响批处理其他部分的成功执行。同时不影响批处理其他部分的成功执行。（3）允许针对符合指定条件情况的指定备选数据库操作。）允许针对符合指定条件情况的指定备选数据库操作。7.3 触发器应用触发器应用 现在可以来现在可以来总结总结一下，触发器可用来维持数据完整一下，触发器可用来维持数据完整性，它防止了对数据的不正确、未授权的、和不一致的性，它防止了对数据的不正确、未授权的、和不一致的改变。当触发器激发对改变。当触发器激发对INSERT，DELETE，或，或UPDATE语句的响应时，语句的响应时， 两个特殊的表被创建。这些是插入和删除表：两个特殊的表被创建。这些是插入和删除表：（1）inserted表包含插入在触发器表中的所有记录的拷贝表包含插入在触发器表中的所有记录的拷贝.（2）deleted表包含了已从触发器表中被删除的所有记录。表包含了已从触发器表中被删除的所有记录。讨论思考讨论思考讨论思考讨论思考：（1）什么是存储过程？用途是什么？）什么是存储过程？用途是什么？ （2）什么是触发器？用途是什么？）什么是触发器？用途是什么？（3）建立及查看存储过程的语句是什么？）建立及查看存储过程的语句是什么？3.3 SQL Server结构及数据库种类结构及数据库种类数据库原理应用与实践数据库原理应用与实践数据库原理应用与实践数据库原理应用与实践
