第09章 存储过程与触发器

1、2022-1-25SQL Server 20051 2SQL Server 2005第第9 9章章 存储过程与触发器存储过程与触发器* * 存储过程和触发器都是存储过程和触发器都是SQL ServerSQL Server的数据库对象。的数据库对象。 存储过程的存在独立于表，存放在服务器上，供客户端调用；存储过程的存在独立于表，存放在服务器上，供客户端调用； 触发器的使用则和表的更新操作紧密结合，是一种特殊的存储触发器的使用则和表的更新操作紧密结合，是一种特殊的存储过程，使用触发器可以大大提高数据库应用程序的灵活性和健过程，使用触发器可以大大提高数据库应用程序的灵活性和健壮性，可以利用触发器来实

2、现复杂的业务规则，更有效地实施壮性，可以利用触发器来实现复杂的业务规则，更有效地实施数据完整性。数据完整性。 存储过程和触发器是大型、复杂、高性能要求的数据库应用系存储过程和触发器是大型、复杂、高性能要求的数据库应用系统所必需要的技术围绕存储过程和触发器。统所必需要的技术围绕存储过程和触发器。 主要内容：存储过程概述、设计存储过程、实现和管理存储过主要内容：存储过程概述、设计存储过程、实现和管理存储过程、程、DML DML 触发器概述、设计触发器概述、设计DML DML 触发器、实现和管理触发器、实现和管理DML DML 触发触发器、器、DDLDDL触发器概述、设计触发器概述、设计DDL DD

3、L 触发器、实现触发器、实现 DDL DDL 触发器等。触发器等。 3SQL Server 2005目录目录 9.1 存储过程存储过程 9.2 DML 触发器触发器 9.3 DDL 触发器触发器练习题练习题9 9 上机实习上机实习8 8 4SQL Server 20059.1 9.1 存储过程存储过程 9.1.1 存储过程概述 9.1.2 设计存储过程 9.1.3 实现和管理存储过程返回本节首页5SQL Server 2005返回本节首页使用使用T-SQLT-SQL程序时，可用两种方法存储和执行程序：程序时，可用两种方法存储和执行程序：1 1）可以将程序存储在本地，并设计向）可以将程序存储在本

4、地，并设计向SQL ServerSQL Server发发送命令并处理结果的应用程序。送命令并处理结果的应用程序。2 2）也可以将程序以存储过程形式存储在）也可以将程序以存储过程形式存储在SQL ServerSQL Server服务器中，并创建执行存储过程并处理结果的应用程服务器中，并创建执行存储过程并处理结果的应用程序。序。 9.1.1 9.1.1 存储过程概述存储过程概述6SQL Server 2005返回本节首页 1 1、存储过程基本知识、存储过程基本知识 SQL Server中的存储过程与其它编程语言过程：1）接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；2）包含用于在数据

5、库中执行操作（包括调用其它过程）的编程语句；3）向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。 可以使用可以使用 T-SQL EXECUTE T-SQL EXECUTE 语句来运行存储过程。存储过程语句来运行存储过程。存储过程与函数不同，因为存储过程不返回取代其名称的值，也不能与函数不同，因为存储过程不返回取代其名称的值，也不能直接在表达式中使用。直接在表达式中使用。9.1.1 9.1.1 存储过程概述存储过程概述7SQL Server 2005返回本节首页在在SQL ServerSQL Server中使用存储过程的好处包括：中使用存储过程的好处包括：1）存储过程已在服务器

6、注册；2）存储过程具有安全特性（例如权限）。用户可以被授予权限来执行存储过程而不必拥有其引用对象的权限；3）存储过程可以强制应用程序的安全性，保护应用程序不受SQL Injection攻击（可将恶意代码插入到以后将传递给SQL Server供分析和执行的字符串中，后将执行并遭到攻击）；4）存储过程允许模块化程序设计，一次创建多次调用，可维护性好，以一致的方式访问数据库；5）存储过程是命名代码，允许延迟绑定。这提供了一个用于简单代码演变的间接级别；6）存储过程可以减少网络通信流量。一个需要数百行T-SQL代码的操作可以通过一条执行过程代码的语句来执行。9.1.1 9.1.1 存储过程概述存储过程

7、概述8SQL Server 2005返回本节首页2 2、存储过程的类型、存储过程的类型（1 1）用户定义的存储过程）用户定义的存储过程存储过程是指封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言(DDL)和数据操作语言(DML)语句，然后返回输出参数。在SQL Server 2005中，存储存储过程有两种类型：过程有两种类型：T-SQLT-SQL或或CLRCLR。1 1）T-SQL T-SQL 存储过程是指保存的存储过程是指保存的T-SQLT-SQL语句集合，可以接受和返回用户提语句集合，可以接受和返回用户提供的参数。供的参数。2 2）

8、CLRCLR存储过程是指对存储过程是指对.NET Framework.NET Framework公共语言运行时公共语言运行时(CLR)(CLR)方法的引用，方法的引用，可以接受和返回用户提供的参数。它们在可以接受和返回用户提供的参数。它们在.NET Framework.NET Framework程序集中是作为程序集中是作为类的公共静态方法实现的。类的公共静态方法实现的。9.1.1 9.1.1 存储过程概述存储过程概述9SQL Server 2005返回本节首页（2 2）扩展存储过程）扩展存储过程 扩展存储过程允许您使用编程语言（例如C）创建自己的外部例程。扩展存储过程是指SQL Server的

9、实例可以动态加载和运行的DLL。注意：CLR集成提供了更为可靠和安全的替代方法来编写扩展存储过程。（3 3）系统存储过程）系统存储过程SQL Server 2005中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。例如，sys.sp_changedbowner。 SQL Server支持在SQL Server和外部程序之间提供一个接口以实现各种维护活动的系统存储过程。这些扩展存储程序使用xp_前缀。9.1.1 9.1.1 存储过程概述存储过程概述10SQL Server 2005返回本节首页1 1、存储过程的设计规则、存储过程的设计规则1）CREATE PROC

10、EDURE定义自身可以包括任意数量和类型的SQL语句，除了：CREATE AGGREGATE 、CREATE RULE、CREATE DEFAULT、CREATE SCHEMA、CREATE或ALTER FUNCTION、CREATE或ALTER TRIGGER、CREATE或ALTER PROCEDURE、CREATE或ALTER VIEW、SET PARSEONLY、SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、SET SHOWPLAN_XML、USE database_name。2）其它数据库对象均可在存储过程中创建并被引用。3）可以在存储过程内引用临时表。4）存

11、储过程内创建的本地临时表，退出该存储过程后，临时表将消失。5）存储过程A调用存储过程B，则B可以访问A创建的所有对象。6）远程存储过程不参与事务处理，不能回滚远程存储过程的更改。7）存储过程中的参数的最大数目为 2100；存储过程中的局部变量的最大数目仅受可用内存的限制；根据可用内存的不同，存储过程最大可达 128 MB。9.1.2 9.1.2 设计存储过程设计存储过程11SQL Server 2005返回本节首页2 2、限定存储过程内的名称、限定存储过程内的名称l 在存储过程内，如果用于语句（例如SELECT或INSERT）的对象名没有限定架构，则架构将默认为该存储过程的架构。l在存储过程内

12、，如果创建该存储过程的用户没有限定 SELECT、INSERT、UPDATE或DELETE语句中引用的表名或视图名，则默认情况下，通过该存储过程对这些表进行的访问将受到该过程创建者的权限的限制。l 如果有其他用户要使用存储过程，则用于所有数据定义语言(DDL)语句（例如CREATE、ALTER或DROP语句，DBCC语句，EXECUTE和动态SQL语句）的对象名应该用该对象架构的名称来限定。l如果没有指定架构名称，SQL Server将首先尝试使用调用方的默认架构或用户在EXECUTE AS子句中指定的架构来解析对象名称，然后尝试使用dbo架构。9.1.2 9.1.2 设计存储过程设计存储过程

13、12SQL Server 2005返回本节首页3 3、加密过程定义、加密过程定义WITH ENCRYPTION子句加密存储过程定义，任何人将无法解密。4 4、SETSET语句选项语句选项 当创建或更改T-SQL存储过程后，数据库引擎将保存SET QUOTED_IDENTIFIERQUOTED_IDENTIFIER和SET ANSI_NULLSSET ANSI_NULLS的设置。执行存储过程时，将使用这些原始设置。因此，在执行存储过程时，将忽略任何客户端会话的SET QUOTED_IDENTIFIER和SET ANSI_NULLS设置。在存储过程中出现的SET QUOTED_IDENTIFIER

14、和SET ANSI_NULLS语句不影响存储过程的功能。9.1.2 9.1.2 设计存储过程设计存储过程13SQL Server 2005返回本节首页CREATE PROCEDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n创建存储过程前，请考虑下列事项：创建存储过程前，请考虑下列事项：1）CREATE PROCED

15、URE 只能单独在一个批处理中使用；2）存储过程是架构作用域内的对象，其名必须遵守标识符规则；3）只能在当前数据库中创建存储过程。创建存储过程时，应指定：创建存储过程时，应指定：1）所有输入参数和向调用过程或批处理返回的输出参数；2）执行数据库操作（包括调用其它过程）的编程语句；3）返回至调用过程或批处理以表明成功或失败的状态值。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程14SQL Server 2005返回本节首页临时存储过程：临时存储过程：n专用和全局临时存储过程与临时表类似。n#表示本地临时存储过程，n#表示全局临时存储过程，nSQL Serv

16、er关闭后，这些过程将不复存在。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程15SQL Server 2005返回本节首页nT-SQL T-SQL 语句或批处理可以包含嵌入参数。语句或批处理可以包含嵌入参数。nsp_executesqlsp_executesql用于执行可以多次重复使用或动态生成的用于执行可以多次重复使用或动态生成的 T-T-SQL SQL 语句或批处理。语句或批处理。n其语法:sp_executesql stmt=stmt ,params=Nparameter_name data_type OUTPUT,.n,param1=value

17、1,.n9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程16SQL Server 2005返回本节首页例例9-1 9-1 建立含参数的建立含参数的SQLSQL命令字符串，并指定参数值并执行。命令字符串，并指定参数值并执行。DECLARE IntVariable int;SQLString nvarchar(500),ParmDefinition nvarchar(500);SET SQLString =NSELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = Manage

18、rID; - 建立SQL命令字符串SET ParmDefinition=NManagerID tinyint;SET IntVariable=197; /SET IntVariable=197; /* *用参数值执行用参数值执行SQLSQL命令字符串命令字符串* */ /EXECUTE sp_executesql SQLString,ParmDefinition,ManagerID=IntVariable;SET IntVariable = 109; /SET IntVariable = 109; /* *用另一个参数值执行用另一个参数值执行SQLSQL命令字符串命令字符串* */ /EXEC

19、UTE sp_executesql SQLString, ParmDefinition,ManagerID=IntVariable;9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程17SQL Server 2005返回本节首页例例9-2 9-2 使用带有复杂使用带有复杂 SELECT SELECT 的简单过程，返回某个视图中的所有雇员的简单过程，返回某个视图中的所有雇员（提供了姓名）、他们的职务和部门名称。该存储过程不使用任何参数。（提供了姓名）、他们的职务和部门名称。该存储过程不使用任何参数。IF OBJECT_ID(HumanResources.usp

20、_GetAllEmployees,P) IS NOT NULLDROP PROCEDURE HumanResources.usp_GetAllEmployees;CREATE PROCEDURECREATE PROCEDURE HumanResources.usp_GetAllEmployeesHumanResources.usp_GetAllEmployees AS AS SELECT LastName,FirstName,JobTitle,Department FROM HumanResources.vEmployeeDepartment;GO - usp_GetAllEmployees

21、存储过程可通过以下方法执行：EXECUTE HumanResources.usp_GetAllEmployees;GO -或如下HumanResources.usp_GetAllEmployees; -如SP是批处理中的第一条命令9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程18SQL Server 2005返回本节首页（1 1）指定参数）指定参数n存储过程通过其参数与调用程序通信。n当程序执行存储过程时，可通过存储过程的参数向该存储过程传递值。n这些值可用作 T-SQL 编程语言中的标准变量。n存储过程也可通过 OUTPUT 参数将值返回至调用程序。n

22、一个存储过程可有多达 2100 个参数，每个参数都有名称、数据类型、方向和默认值。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程19SQL Server 2005返回本节首页（2 2）从存储过程中返回数据：）从存储过程中返回数据：1 1）使用）使用 OUTPUT OUTPUT 参数返回数据参数返回数据 例例9-79-7 CREATE PROCEDURE Sales.usp_GetEmployeeSalesYTD CREATE PROCEDURE Sales.usp_GetEmployeeSalesYTD SalesPerson nvarchar(50),

23、SalesYTD money OUTPUT AS AS SELECT SalesYTD=SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.EmployeeID = sp.SalesPersonID WHERE LastName=SalesPerson;9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程20SQL Server 2005返回本节首页DECLARE SalesYTDBySalesPerson money; - 定义接收存储过程输出值的变量EX

24、ECUTE Sales.usp_GetEmployeeSalesYTD NBlythe, SalesYTD=SalesYTDBySalesPerson OUTPUTOUTPUT; -调用存储过程得到输出值PRINT 该雇员本年度至尽的销售总额为+convert(varchar(10),SalesYTDBySalesPerson);9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程21SQL Server 2005返回本节首页2 2）使用返回代码返回数据）使用返回代码返回数据n存储过程可返回一个整数的“返回代码”，表明执行状态。n使用 RETURNRETURN

25、 语句指定存储过程的返回代码。n返回代码保存到变量中，才能在调用程序时使用返回代码值。n例如，使用int数据类型的赋值变量result保存存储过程my_proc的返回代码： DECLARE result int; EXECUTE result = my_proc; DECLARE result int; EXECUTE result = my_proc;n返回代码通常用在存储过程内的控制流块中，为每种可能的错误情况设置返回代码值。n可以在T-SQL语句后使用ERROR函数，来检测该语句执行过程中是否有错误发生。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过

26、程22SQL Server 2005返回本节首页3 3）在）在OUTPUTOUTPUT参数中使用参数中使用cursor cursor 数据类型数据类型T-SQLT-SQL存储过程只能将存储过程只能将cursorcursor用于用于VARYINGVARYING和和OUTPUTOUTPUT参数。参数。CREATE PROCEDURE dbo.currency_cursor CREATE PROCEDURE dbo.currency_cursor currency_cursor CURSOR VARYING OUTPUTAS SET currency_cursor=CURSOR FORWARD_ON

27、LY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency;OPEN currency_cursor;DECLARE MyCur CURSOR;EXEC dbo.currency_cursor currency_cursor=MyCur OUTPUT; -执行并返回Cursor变量WHILE (FETCH_STATUS = 0) FETCH NEXT FROM MyCur;-没有真正取出值并加以处理CLOSE MyCur; DEALLOCATE MyCur;9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建

28、存储过程23SQL Server 2005返回本节首页（3 3）嵌套存储过程）嵌套存储过程n当一个存储过程通过引用CLR例程、类型或聚合来调用另一个存储过程或执行托管代码时，存储过程将被嵌套。n嵌套存储过程和托管代码引用最高可达32级。n每当调用的存储过程或托管代码引用开始执行，嵌套级别就增加一级；执行完成后，嵌套级别就减少一级。n试图超过最高 32 级的嵌套将导致整个调用链失败。n正在执行的存储过程的当前嵌套级别存储在NESTLEVEL函数中。n尽管嵌套限制为32级，但SQL Server对在给定的存储过程中可以调用的存储过程数量没有限制。因而只要从属存储过程不调用其它从属存储过程且不超过最

29、大嵌套级别即可。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建存储过程创建存储过程24SQL Server 2005返回本节首页在在SQL Server 2005SQL Server 2005中，可以基于中，可以基于.NET Framework.NET Framework公共语言公共语言运行时运行时(CLR)(CLR)创建中的程序集使用编程方法创建存储过程：具创建中的程序集使用编程方法创建存储过程：具体步骤为：体步骤为：1）使用.NET Framework支持的语言将存储过程定义为类的静态方法。然后，使用适当的语言编译器编译该类，在.NET Framework中生成程序集；

30、2）使用CREATE ASSEMBLY语句在SQL Server中注册程序集；3）通过使用CREATE PROCEDURE语句创建引用注册程序集的存储过程。创建、修改或删除程序集命令：创建、修改或删除程序集命令：CREATE ASSEMBLYCREATE ASSEMBLY、ALTER ALTER ASSEMBLYASSEMBLY、DROP ASSEMBLYDROP ASSEMBLY；创建；创建CLRCLR存储过程也通过存储过程也通过CREATE CREATE PROCEDUREPROCEDURE命令。命令。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-创建创建CLRCLR存储过

31、程存储过程25SQL Server 2005返回本节首页n若要执行存储过程，可以使用若要执行存储过程，可以使用T-SQL EXECUTET-SQL EXECUTE语句。如果存储过程是批处语句。如果存储过程是批处理中的第一条语句，那么不使用理中的第一条语句，那么不使用EXECUTEEXECUTE关键字也可以执行存储过程。关键字也可以执行存储过程。n系统存储过程以字符sp_开头。它们物理上存储于资源数据库中，但逻辑上出现在 SQL Server实例的每个系统定义和用户定义数据库的sys架构中。n建议使用建议使用 sys sys 架构名称对所有系统存储名称进行限定，以防止名称冲突。架构名称对所有系统

32、存储名称进行限定，以防止名称冲突。以下示例说明执行系统存储过程的推荐方法。以下示例说明执行系统存储过程的推荐方法。EXEC sys.sp_who;EXEC sys.sp_who; 下列示例说明执行系统存储过程的向后兼容方法。 EXEC sp_who; EXEC master.dbo.sp_who; EXEC mydatabase.sp_who;EXEC dbo.sp_who; EXEC mydatabase.dbo.sp_who;9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-执行存储过程执行存储过程26SQL Server 2005返回本节首页（1 1）数据库排序规则匹配）数据

33、库排序规则匹配 SQL Server 2005 与系统过程名称匹配时调用数据库排序规则。如果在具有区分大小写的排序规则的数据库上下文中执行，以下代码将失败： exec SP_heLP; - 因为SP_heLP与sp_help不同，执行失败。 使用sys.system_objects和sys.system_parameters目录视图可以显示确切的系统存储过程名称。（2 2）执行系统扩展存储过程）执行系统扩展存储过程 系统扩展存储过程以字符xp_开头。它们物理上存储在资源数据库中，但逻辑上出现在SQL Server实例的每个系统定义和用户定义数据库的sys架构中。例如：EXEC sys.xp_s

34、ubdirs c:;（3 3）执行用户定义存储过程）执行用户定义存储过程 执行用户定义存储过程，强烈建议至少用架构名称限定存储过程名称。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-执行存储过程执行存储过程27SQL Server 2005返回本节首页（4 4）自动执行存储过程）自动执行存储过程nSQL Server 2005启动时将执行标记为要自动执行的存储过程。n作用：定期执行操作，或者作为后台进程运行存储过程。使该存储过程完成tempdb中的系统或维护任务，如创建全局临时表。n只有系统管理员只有系统管理员(sa)(sa)可以将存储过程标记为自动执行。另外，可以将存储过程标

35、记为自动执行。另外，该存储过程必须在该存储过程必须在master master 数据库中并由数据库中并由 sa sa 所有，而且不能有所有，而且不能有输入或输出参数。输入或输出参数。使用sp_procoption可以：1）将现有存储过程指定为启动过程；2）阻止过程在SQL Server启动时执行。使用sp_configure设置SQL Server scan for startup procs配置选项以防止在SQL Server启动时自动执行所有存储过程。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-执行存储过程执行存储过程28SQL Server 2005返回本节首页n删除并

36、重新创建存储过程时，与其关联的所有权限都将丢失。删除并重新创建存储过程时，与其关联的所有权限都将丢失。n更改存储过程或参数定义，但为该存储过程定义的权限将保留，更改存储过程或参数定义，但为该存储过程定义的权限将保留，并且不会影响任何相关的存储过程或触发器。并且不会影响任何相关的存储过程或触发器。n修改存储过程以加密其定义或使其在每次执行时重新编译。修改存储过程以加密其定义或使其在每次执行时重新编译。n更改过程名称或定义可能导致所有相关对象在执行时失败。更改过程名称或定义可能导致所有相关对象在执行时失败。 ALTER PROCEDURE procedure_name ;number parame

37、ter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-修改存储过程修改存储过程29SQL Server 2005返回本节首页系统存储过程和目录视图可提供有关存储过程的信息：系统存储过程和目录视图可提供有关存储过程的信息：1）查看存储过程的定义。即查看用于创建存储过程的 T-SQL 语句。这对于没有用于创建存储过程的 T-SQL

38、 脚本文件的用户是很有用的。 2）获得有关存储过程的信息（如存储过程的架构、创建时间及其参数）。3）列出指定存储过程所使用的对象及使用指定存储过程的过程。此信息可用来识别那些受数据库中某个对象的更改或删除影响的过程。n若要查看存储过程的定义：若要查看存储过程的定义：sys.sql_modules、OBJECT_DEFINITION、sp_helptext。n查看有关存储过程的信息：查看有关存储过程的信息：sys.objects 、cedures 、sys.parameters 、sys.numbered_procedures 、sys.numbered_procedure_par

39、ameters 、sp_help 。n查看存储过程的依赖关系：查看存储过程的依赖关系：sys.sql_dependencies 、sp_depends。n查看有关扩展存储过程的信息：查看有关扩展存储过程的信息：sp_helpextendedproc。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-查看存储过程查看存储过程30SQL Server 2005返回本节首页删除存储过程命令为：删除存储过程命令为：DROP PROC|PROCEDURE schema_cedure,.n；删除扩展存储过程的命令为：删除扩展存储过程的命令为：sp_dropextendedpro

40、c functname=procedure。9.1.3 9.1.3 实现和管理存储过程实现和管理存储过程-删除存储过程删除存储过程31SQL Server 20059.2 DML 9.2 DML 触发器触发器 9.2.1 DML 触发器概述 9.2.2 设计DML触发器 9.2.3 实现和管理 DML 触发器 返回本节首页32SQL Server 2005返回本节首页1 1、了解、了解 DMLDML触发器触发器n两种机制强制执行业务规则和数据完整性：约束和触发器。n触发器是特殊的存储过程，由DBMS自动执行。nSQL ServerSQL Server包括两大类触发器：包括两大类触发器：DDL触

41、发器是SQL Server 2005的新增功能。当服务器或数据库中发生数据定义语言(DDL)事件时将调用这些触发器。当数据库中发生数据操作语言(DML)事件时将调用 DML 触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。nDML触发器可以查询其它表，还可以包含复杂的T-SQL语句。n将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，错误发生，则整个事务即自动回滚。9.2.1 DML 9.2.1 DML 触发器概述触发器概述33SQL Server 2005返回本节首页DMLDML触发器在以下方面非常有用：触发器在以下方面非常有用：

42、 DML触发器可通过数据库中的相关表实现级联更改。不过，通过级联引用完整性约束可以更有效地进行这些更改； DML触发器可以防止恶意或错误的INSERT、UPDATE以及DELETE操作，并强制执行比CHECK约束定义的限制更为复杂的其它限制。与CHECK约束不同，DML触发器可以引用其它表中的列。 DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施；1.一个表中的多个同类DML触发器（INSERT、UPDATE或DELETE）允许采取多个不同的操作来响应同一个修改语句。9.2.1 DML 9.2.1 DML 触发器概述触发器概述34SQL Server 2005返回本节首页2 2、

43、DML DML 触发器的类型触发器的类型lAFTER/FORAFTER/FOR触发器：触发器：在INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器，只能在表上指定该类触发器。l INSTEAD OFINSTEAD OF触发器：触发器：执行INSTEAD OF触发器代替通常的触发动作。可为一个或多个基表的视图定义INSTEAD OF触发器，而这些触发器能够扩展视图可支持的更新类型。 CLRCLR触发器：触发器：CLR触发器可以是AFTER触发器或INSTEAD OF触发器。CLR触发器还可以是DDL触发器。CLR触发器将执行在托管代码（在.NET Framework 中创建

44、并在SQL Server中上载的程序集的成员）中编写的方法，而不用执行T-SQL存储过程。9.2.1 DML 9.2.1 DML 触发器概述触发器概述- DML - DML 触发器的类型触发器的类型35SQL Server 2005返回本节首页1 1、DMLDML触发器计划指南触发器计划指南 SQL Server 2005提供了两个用于设计DML触发器的选项：1）执行INSTEAD OF触发器代替通常的触发操作，可对带有一个或多个基表的视图定义INSTEAD OF触发器，以扩展视图可支持的更新类型；2）在执行INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。2 2、指定

45、、指定DMLDML触发器何时激发触发器何时激发1）AFTER 触发器在处理触发操作（INSERT、UPDATE或DELETE）、INSTEAD OF 触发器和约束之后激发。可通过指定AFTER或FOR来请求AFTER触发器。2）INSTEAD OF将在处理约束前激发，以替代触发操作。如果表有AFTER触发器，它们将在处理约束之后激发。如果违反了约束，将回滚INSTEAD OF触发器操作并且不执行 AFTER 触发器；3）每个表或视图针对每个触发操作（UPDATE、DELETE和INSERT）可有一个相应的 INSTEAD OF 触发器，可有多个相应的AFTER触发器。9.2.2 9.2.2 设

46、计设计DMLDML触发器触发器36SQL Server 2005返回本节首页3 3、DMLDML触发器执行触发器执行n如果违反了约束，则永远不会执行如果违反了约束，则永远不会执行AFTERAFTER触发器；因此，触发器；因此，这些触发器不能用于任何可能防止违反约束的处理。这些触发器不能用于任何可能防止违反约束的处理。n执行执行INSTEAD OFINSTEAD OF触发器，而不执行触发操作。触发器，而不执行触发操作。 nDMLDML触发器的性能开销通常很低。触发器的性能开销通常很低。运行DML触发器所花时间大都用于引用其它表，这些表可能位于内存中，也可能位于数据库设备上。删除的表(delete

47、d)和插入的表(inserted)始终位于内存中。触发器所引用的其它表的位置将确定操作所需的时间。9.2.2 9.2.2 设计设计DMLDML触发器触发器37SQL Server 2005返回本节首页4 4、设计、设计INSTEAD OF INSTEAD OF 触发器触发器nINSTEAD OF触发器主要优点是可使不能更新的视图支持更新。n基于多个基表的视图必须使用INSTEAD OF触发器来支持引用多个表中数据的插入、更新和删除操作。nINSTEAD OF触发器另一个优点是可编写这样的逻辑代码：在允许批处理的其它部分成功的同时拒绝批处理中的某些部分。n对于含有使用DELETE或UPDATE级

48、联操作定义的外键的表，不能定义INSTEAD OF DELETE和INSTEAD OF UPDATE触发器。9.2.2 9.2.2 设计设计DMLDML触发器触发器38SQL Server 2005返回本节首页这里先说明，创建触发器命令为create trigger，其语法为： CREATE TRIGGERCREATE TRIGGER trigger_name ON ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION ASAS IF UP

49、DATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n 9.2.2 9.2.2 设计设计DMLDML触发器触发器39SQL Server 2005返回本节首页CREATE TABLE PersonCREATE TABLE Person(SSN char(11) PRIMARY KEY,Name nvarchar(100),Add

50、ress nvarchar(100),Birthdate datetime)CREATE TABLE EmployeeTableCREATE TABLE EmployeeTable(EmployeeID int PRIMARY KEY,SSN char(11) UNIQUE, Department nvarchar(10),Salary money,CONSTRAINT FKEmpPer FOREIGN KEY (SSN) REFERENCES Person (SSN)CREATE TABLE PersonDuplicatesCREATE TABLE PersonDuplicates (SSN

51、 char(11),Name nvarchar(100),Address nvarchar(100),Birthdate datetime,InsertSNAME nchar(100),WhenInserted datetime)CREATE VIEW EmployeeCREATE VIEW Employee AS SELECT P.SSN as SSN,Name,Address,Birthdate, EmployeeID,Department,Salary FROM Person P,EmployeeTable E WHERE P.SSN=E.SSN 9.2.2 9.2.2 设计设计DMLD

52、ML触发器触发器40SQL Server 2005返回本节首页INSTEAD OF INSTEAD OF 触发器将行插入到单个视图的多个基表中。在触发器将行插入到单个视图的多个基表中。在PersonDuplicatesPersonDuplicates表中记录了插入具有重复社会保障号的行的尝试。表中记录了插入具有重复社会保障号的行的尝试。EmployeeTable EmployeeTable 中的重复行中的重复行将更改为更新语句。将更改为更新语句。CREATE TRIGGERCREATE TRIGGER IO_Trig_INS_Employee ON Employee INSTEAD OF IN

53、SERT ASASBEGINBEGINSET NOCOUNT ON - 检查人员重复情况，不重复则插入IF (NOT EXISTS (SELECT P.SSN FROM Person P,inserted I WHERE P.SSN=I.SSN) INSERT INTO Person SELECT SSN,Name,Address,Birthdate FROM inserted ELSE -重复则日志记录于“人员重复表”中 INSERT INTO PersonDuplicates SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()

54、 FROM inserted9.2.2 9.2.2 设计设计DMLDML触发器触发器41SQL Server 2005返回本节首页- 检查雇员重复情况，不重复则插入IF (NOT EXISTS (SELECT E.SSN FROM EmployeeTable E, inserted WHERE E.SSN=inserted.SSN) INSERT INTO EmployeeTable SELECT EmployeeID,SSN, Department, Salary FROM insertedinsertedELSE -重复则改为对表EmployeeTable做修改操作 UPDATE Empl

55、oyeeTable SET EmployeeID = I.EmployeeID, Department = I.Department,Salary = I.Salary FROM EmployeeTable E, inserted I WHERE E.SSN = I.SSNENDEND9.2.2 9.2.2 设计设计DMLDML触发器触发器42SQL Server 2005返回本节首页5 5、管理触发器安全性、管理触发器安全性n默认情况下，在调用触发器的用户的上下文中执行DML和DDL触发器。n触发器的调用方是执行使触发器运行的语句的用户。例如，如果用户Mary执行可以使DML触发器 DML_

56、trigMary 运行的DELETE 语句，则 DML_trigMary中的代码将在Mary的用户特权上下文中执行。希望向数据库或服务器实例中引入恶意代码的用户可以使用此默认行为。 下面由用户下面由用户JohnDoeJohnDoe创建的创建的DDL DDL 触发器：触发器： CREATE TRIGGER DDL_trigJohnDoe ON DATABASE FOR ALTER_TABLE AS GRANT CONTROL SERVER TO JohnDoe; 9.2.2 9.2.2 设计设计DMLDML触发器触发器43SQL Server 2005返回本节首页 创建创建DMLDML触发器前应

57、考虑下列问题：触发器前应考虑下列问题：lCREATE TRIGGER语句必须是批处理中的第一个语句，该语句后面的所有其它语句被解释为CREATE TRIGGER语句定义的一部分；l创建DML触发器的权限默认分配给表的所有者，不能转给其他用户；lDML触发器为数据库对象，其名称必须遵循标识符的命名规则；lDML触发器可引用当前DB以外对象，但只能在当前DB中创建它；l虽然DML触发器可以引用临时表，但不能对临时表或系统表创建 DML 触发器。不应引用系统表，而应使用信息架构视图；l对于含有用DELETE或UPDATE操作定义的外键的表，不能定义INSTEAD OF DELETE和INSTEAD

58、OF UPDATE触发器；l虽然 TRUNCATE TABLE 语句类似于不带 WHERE 子句的 DELETE 语句（用于删除所有行），但它并不会触发DELETE触发器，因为TRUNCATE TABLE语句没有记录；1.WRITETEXT语句不会触发INSERT或UPDATE触发器。9.2.3 9.2.3 实现和管理实现和管理 DML DML 触发器触发器 44SQL Server 2005返回本节首页创建创建DMLDML触发器时需指定：触发器时需指定：l名称；l定义触发器时所基于的表；l触发器被触发的时间；l激活触发器语句为INSERT、UPDATE或DELETE的任意组合。l执行触发器操

59、作的编程语句。n一个表中可有多个给定类型的AFTER触发器，每个触发器只能应用于一个表。n一个表只能具有一个给定类型的INSTEAD OF触发器。n用定义触发器时所基于的表或视图的名称架构创建触发器。nCREATE TRIGGER权限默认授予定义触发器的表所有者、sysadmin固定服务器角色以及db_owner和db_ddladmin固定数据库角色的成员，并且不可转让。1.如果对某个视图创建INSTEAD OF触发器，如果视图所有者不同时拥有视图和触发器所引用的基表，所有权链将断开。9.2.3 9.2.3 实现和管理实现和管理 DML DML 触发器触发器 45SQL Server 2005

60、返回本节首页 1 1、对、对DMLDML触发器进行编程触发器进行编程n几乎所有可以编写成批处理的 T-SQL 语句都可用于创建 DML 触发器，下列语句除外：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。n此外，在对作为触发操作的目标的表或视图使用下列T-SQL语句时，将不允许在DML触发器的主体内使用这些语句：CREATE INDEX、ALTER INDEX、DROP INDEX、DBCC DBREINDEX、ALTER PART