第10章 存储过程和触发器课件.ppt

《第10章 存储过程和触发器课件.ppt》由会员分享，可在线阅读，更多相关《第10章 存储过程和触发器课件.ppt（30页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、第第10章章 存储过程、触发器存储过程、触发器n存储过程、触发器和游标是存储过程、触发器和游标是SQL Server数据库的三个重要组成部数据库的三个重要组成部分。分。SQL Server 2008使用它们从不同方面提高数据处理能力。使用它们从不同方面提高数据处理能力。n在在SQL Server 2008中，可以像其他程序设计语言一样定义子程中，可以像其他程序设计语言一样定义子程序，称为存储过程。存储过程是序，称为存储过程。存储过程是SQL Server 2008提供的最强大提供的最强大的工具之一。理解并运用它，可以创建健壮、安全且具有良好性的工具之一。理解并运用它，可以创建健壮、安全且具有良

2、好性能的数据库，可以为用户实现最复杂的商业事务。能的数据库，可以为用户实现最复杂的商业事务。n触发器是一种特殊类型的存储过程：它通过事件触发而被自动执触发器是一种特殊类型的存储过程：它通过事件触发而被自动执行。自动执行意味着更少的手工操作以及更小的出错机率。触发行。自动执行意味着更少的手工操作以及更小的出错机率。触发器用于强制复杂的完整性检查，审核更改，维护不规范的数据等器用于强制复杂的完整性检查，审核更改，维护不规范的数据等等。等。SQL Server 2008允许允许DML语句和语句和DDL语句创建触发器，可语句创建触发器，可以引发以引发AFTER或者或者INSTEAD OF触发事件。触发

3、事件。n游标主要用于实现一些不能使用面向集合的语句实现的操作。通游标主要用于实现一些不能使用面向集合的语句实现的操作。通过游标，过游标，SQL Server提供了一个对结果集进行逐行处理的能力。提供了一个对结果集进行逐行处理的能力。可以把游标看为一种特殊的指针，它可以指向结果集中的任意位可以把游标看为一种特殊的指针，它可以指向结果集中的任意位置，在查询数据的同时对数据进行处理。置，在查询数据的同时对数据进行处理。本章学习目标：本章学习目标：n了解存储过程、触发器和游标的基本概念与特了解存储过程、触发器和游标的基本概念与特点点n掌握存储过程的基本类型和相关操作掌握存储过程的基本类型和相关操作n掌

4、握触发器的类型与相关操作掌握触发器的类型与相关操作10.1 存储过程存储过程n通过前面的学习，我们能够编写并运行通过前面的学习，我们能够编写并运行T-SQL程序以完成各种不同的应用。保存程序以完成各种不同的应用。保存T-SQL程序程序的方法有两种：一种是在本地保存程序的源文的方法有两种：一种是在本地保存程序的源文件，运行时先打开源文件再执行程序；另一种件，运行时先打开源文件再执行程序；另一种方法即将程序存储为存储过程，运行时调用存方法即将程序存储为存储过程，运行时调用存储过程执行。储过程执行。n因为存储过程是由一组因为存储过程是由一组T-SQL语句构成的，要语句构成的，要使用存储过程，我们必需

5、熟悉前面几章所讨论使用存储过程，我们必需熟悉前面几章所讨论的基本的的基本的T-SQL语句，并且需要了解掌握一些语句，并且需要了解掌握一些关于函数、过程的概念。关于函数、过程的概念。10.1.1 存储过程的基本概念存储过程的基本概念n存储过程是事先编好的、存储在数据库中一组被编译存储过程是事先编好的、存储在数据库中一组被编译了的了的T-SQL命令集合，这些命令用来完成对数据库的命令集合，这些命令用来完成对数据库的指定操作：存储过程可以接收用户的输入参数、向客指定操作：存储过程可以接收用户的输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言户端返回表格或标量结果和消息、调用数据定义语言

6、(DDL) 和数据操作语言和数据操作语言 (DML) 语句，然后返回输出语句，然后返回输出参数。参数。n通过定义可以看到，存储过程起到了我们在其他语言通过定义可以看到，存储过程起到了我们在其他语言中所说的子程序的作用，我们可以将经常执行的管理中所说的子程序的作用，我们可以将经常执行的管理任务或者复杂的业务规则，预先用任务或者复杂的业务规则，预先用T-SQL语句写好并语句写好并保存为存储过程保存为存储过程, 当需要数据库提供与该存储过程的当需要数据库提供与该存储过程的功能相同的服务时，只需要使用功能相同的服务时，只需要使用EXECUTE命令，即命令，即可调用存储过程完成命令。可调用存储过程完成命

7、令。储过程的优点：储过程的优点：n1. 减少网络流量：存储过程在数据库服务器端执行，只向客户端返回执行结果。减少网络流量：存储过程在数据库服务器端执行，只向客户端返回执行结果。因此可以将在网络中要发送的数百行代码，编写为一条存储过程，这样客户端只因此可以将在网络中要发送的数百行代码，编写为一条存储过程，这样客户端只需要提交存储过程的名称和参数，即可实现相应功能，节省了网络流量，提高了需要提交存储过程的名称和参数，即可实现相应功能，节省了网络流量，提高了执行的效率。此外，由于所有的操作都在服务器端完成，避免了在客户端和服务执行的效率。此外，由于所有的操作都在服务器端完成，避免了在客户端和服务器端

8、之间的多次往返。存储过程只需要将最终结果通过网络传输到客户端。器端之间的多次往返。存储过程只需要将最终结果通过网络传输到客户端。n2. 提高系统性能：一般提高系统性能：一般T-SQL语句每执行一次就需要编译一次，而存储过程只在语句每执行一次就需要编译一次，而存储过程只在创建时进行编译，被编译后存放在数据库服务器的过程高速缓存中，当使用时，创建时进行编译，被编译后存放在数据库服务器的过程高速缓存中，当使用时，服务器不必再重新分析和编译它们。因此，当对数据库进行复杂操作时（如对多服务器不必再重新分析和编译它们。因此，当对数据库进行复杂操作时（如对多个表进行个表进行UPDATE、INSERT或或DE

9、LETE操作时），可将这些复杂操作用存储过操作时），可将这些复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用，节省了分析、解析和优化代程封装起来与数据库提供的事务处理结合一起使用，节省了分析、解析和优化代码所需的码所需的CPU资源和时间。资源和时间。n3. 安全性高：使用存储过程可以完成所有数据库操作，并且可授予没有直接执行安全性高：使用存储过程可以完成所有数据库操作，并且可授予没有直接执行存储过程中语句的权限的用户，也可执行该存储过程的权限。另外可以防止用户存储过程中语句的权限的用户，也可执行该存储过程的权限。另外可以防止用户直接访问表，强制用户使用存储过程执行特定的任务。直接访

10、问表，强制用户使用存储过程执行特定的任务。n4. 可重用性：存储过程只需创建并存储在数据库中，以后即可任意在程序中调用可重用性：存储过程只需创建并存储在数据库中，以后即可任意在程序中调用该过程。存储过程可独立于程序源代码而单独修改，减少数据库开发人员的工作该过程。存储过程可独立于程序源代码而单独修改，减少数据库开发人员的工作量。量。n5. 可自动完成需要预先执行的任务：存储过程可以在系统启动时自动执行，完成可自动完成需要预先执行的任务：存储过程可以在系统启动时自动执行，完成一些需要预先执行的任务，而不必在系统启动后再进行人工操作。一些需要预先执行的任务，而不必在系统启动后再进行人工操作。10.

11、1.2 存储过程的类型存储过程的类型1. 系统存储过程系统存储过程2. 扩展存储过程扩展存储过程3.用户存储过程用户存储过程n用户存储过程在用户数据库中创建，通常与数据库对象进行交互，用户存储过程在用户数据库中创建，通常与数据库对象进行交互，用于完成特定数据库操作任务，可以接受和返回用户提供的参数，用于完成特定数据库操作任务，可以接受和返回用户提供的参数，名称不能以名称不能以sp_为前缀。为前缀。n在在SQL Server 2008中，用户存储过程有两种类型：中，用户存储过程有两种类型：Transact-SQL存储过程和存储过程。存储过程和存储过程。nTransact-SQL存储过程保存存储过

12、程保存T-SQL语句的集合，可以接受和返回语句的集合，可以接受和返回用户提供的参数，也可以从数据库向客户端应用程序返回数据；用户提供的参数，也可以从数据库向客户端应用程序返回数据；nCLR存储过程是指对存储过程是指对Microsoft.NET Framework公共语言运行时公共语言运行时方法的引用，可以接受和返回用户提供的参数。它们在方法的引用，可以接受和返回用户提供的参数。它们在.NET Framework程序集中是作为类的公共静态方法实现的程序集中是作为类的公共静态方法实现的10.1.3 用户存储过程的创建与执行用户存储过程的创建与执行1. 创建和执行用户存储过程实例创建和执行用户存储过

13、程实例n创建用户存储过程是通过编辑代码实现的。下面通过一个实例介绍创建创建用户存储过程是通过编辑代码实现的。下面通过一个实例介绍创建用户存储过程的一般步骤。用户存储过程的一般步骤。n【例【例10-1】创建名为】创建名为snoquery的存储过程：通过用户输入学生学号来查的存储过程：通过用户输入学生学号来查询学生的姓名、年龄、性别和所属院系。询学生的姓名、年龄、性别和所属院系。（1）启动）启动SSMS，展开服务器。，展开服务器。（2）展开所需的）展开所需的“数据库数据库”文件夹，展开要在其中创建存储过程的数据库。本例文件夹，展开要在其中创建存储过程的数据库。本例中，我们展开中，我们展开stuin

14、fo数据库。数据库。（3）展开）展开“可编程性可编程性”文件夹，在文件夹，在“存储过程存储过程”文件夹上右击鼠标，在弹出的快捷文件夹上右击鼠标，在弹出的快捷菜单中选择菜单中选择“新建存储过程新建存储过程”项。项。（4）系统弹出）系统弹出T-SQL语句编写窗口，其中的代码是创建存储过程的格式说明。语句编写窗口，其中的代码是创建存储过程的格式说明。我们输入以下我们输入以下T-SQL代码：代码：CREATE PROCEDURE snoqueryxuehao char(10)ASSELECT sno 学号学号,sname 学生姓名学生姓名,sage 年龄年龄,ssex 性别性别,sdept 所属院系所

15、属院系FROM studentWHERE sno=xuehao（5）代码输入结束后，只要将以上代码在）代码输入结束后，只要将以上代码在“查询分析器查询分析器”里执行一次，系统就会里执行一次，系统就会在当前数据库中创建一个名为在当前数据库中创建一个名为snoquery的存储过程。点击刷新按钮，选择的存储过程。点击刷新按钮，选择stuinfo数据库，在左边的树型列表中选择数据库，在左边的树型列表中选择“存储过程存储过程”，就可以看到属于，就可以看到属于dbo（database owner）的存储过程）的存储过程dbo.snoquery。【例【例10-2】使用存储过程】使用存储过程snoquery查

16、询学号为查询学号为“20070102”学生的信息。学生的信息。nT-SQL语句为：语句为： EXECUTE snoquery 200701022. 创建存储过程的创建存储过程的T-SQL语句语句n基本语法格式为：基本语法格式为：CREATE PROCEDURE - 定义存储过程名称定义存储过程名称参数名称参数名称 数据类型数据类型-定义参数及其数据类型定义参数及其数据类型=defaultOUTPUT ,n1 -定义参数的属性定义参数的属性ASSQL语句语句,n2-执行的操作执行的操作3. 运行存储过程的运行存储过程的T-SQL语句语句n存储过程创建完成后，可以使用存储过程创建完成后，可以使用E

17、XECUTE语语句调用它。句调用它。n基本语法格式如下：基本语法格式如下：EXECUTE存储过程名称存储过程名称参数名称参数名称= value| variable OUTPUT| DEFAULT ,n110.1.4 存储过程的查看、修改和删除存储过程的查看、修改和删除n展开所选数据库数据库展开所选数据库数据库“可编程性可编程性”文件夹文件夹“存储存储过程过程”文件夹，即可以看到数据库的系统存储过程和文件夹，即可以看到数据库的系统存储过程和用户存储过程；系统存储过程方便用户管理数据库的用户存储过程；系统存储过程方便用户管理数据库的有关对象。有关对象。qsp_help：用于查看有关存储过程的名称列

18、表。向用户报告：用于查看有关存储过程的名称列表。向用户报告有关数据库对象、用户定义数据类型或所提供的数据类型的有关数据库对象、用户定义数据类型或所提供的数据类型的摘要信息；摘要信息；qsp_helptext：用于显示规则、默认值、未加密的存储过程、：用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的过程定义代码。用户定义函数、触发器或视图的过程定义代码。n我们可以利用下面的语句查看存储过程的信息：我们可以利用下面的语句查看存储过程的信息：EXECUTE sp_help 存储过程名称存储过程名称 用于查看存储过程的对象信息用于查看存储过程的对象信息EXECUTE sp_help

19、text 存储过程名称存储过程名称 用于查看存储过程的代码文本信息用于查看存储过程的代码文本信息【例【例10-3】查看存储过程】查看存储过程snoquery的对象信息和的对象信息和T-SQL代码。代码。n查看对象信息的查看对象信息的T-SQL语句为：语句为：USE stuinfoEXECUTE sp_help snoqueryn待查看的存储过程必须在当前数据库中，因此，要使待查看的存储过程必须在当前数据库中，因此，要使用用USE stuinfo语句打开数据库。可以看到存储过程语句打开数据库。可以看到存储过程的相关信息及其中的参数信息。的相关信息及其中的参数信息。n查看代码信息的查看代码信息的T

20、-SQL语句为：语句为：USE stuinfoEXECUTE sp_helptext snoqueryn可以看到存储过程可以看到存储过程snoquery的详细的详细T-SQL代码。代码。2.修改存储过程修改存储过程n基本语法格式：基本语法格式：ALTER PROCEDURE 参数名称参数名称 数据类型数据类型=defaultOUTPUT ,n1ASSQL语句语句,n2各参数的操作与创建存储过程相同。各参数的操作与创建存储过程相同。 【例【例10-4】修改存储过程】修改存储过程snoquery：通过用户：通过用户输入学生姓名来查询学生的姓名、年龄、性别输入学生姓名来查询学生的姓名、年龄、性别和所

21、属院系。修改完成后查询学生王小华的信和所属院系。修改完成后查询学生王小华的信息。息。nT-SQL语句为：语句为：ALTER PROCEDURE snoqueryname nchar(10)ASSELECT sno 学号学号,sname 学生姓名学生姓名,sage 年龄年龄,ssex 性性别别,sdept 所属院系所属院系FROM studentWHERE sname=nameGOEXECUTE snoquery N王小华王小华3. 删除存储过程删除存储过程n当不再使用存储过程时，可以在当不再使用存储过程时，可以在SSMS中选择对应的中选择对应的数据库和存储过程，单击数据库和存储过程，单击“删除

22、删除”按钮，也可以使用按钮，也可以使用DROP PROCEDURE语句可以将其永久从数据库中语句可以将其永久从数据库中删除。在删除之前，需要确认该存储过程没有任何函删除。在删除之前，需要确认该存储过程没有任何函数依赖关系。数依赖关系。n语法格式为：语法格式为：DROP PROCEDURE ,n【例【例10-5】删除存储过程】删除存储过程snoquerynT-SQL语句为：语句为：USE stuinfoDROP PROCEDURE snoquery10.2 触发器触发器nSQL Server 2008提供两种主要机制来强制使提供两种主要机制来强制使用业务规则和数据完整性：约束和触发器。用业务规则

23、和数据完整性：约束和触发器。n我们使用我们使用ALTER TABLE和和CREATE TABLE语句声明字段的域完整性，使用语句声明字段的域完整性，使用PRIMARY KEY 和和 FOREIGN KEY约束实现表之间的参约束实现表之间的参照完整性。对于数据库中约束所不能保证的复照完整性。对于数据库中约束所不能保证的复杂的参照完整性和数据的一致性我们使用触发杂的参照完整性和数据的一致性我们使用触发器来实现。器来实现。10.2.1 触发器概述触发器概述n1. 触发器的功能触发器的功能n在在SQL Server内部，触发器被看作是存储过程，它与存储过程所经历的处理过程内部，触发器被看作是存储过程，

24、它与存储过程所经历的处理过程类似。但是触发器没有输入参数和输出参数，因而不能被显示调用。它作为语句类似。但是触发器没有输入参数和输出参数，因而不能被显示调用。它作为语句的执行结果自动引发，而存储过程则是通过存储过程名称而被直接调用。的执行结果自动引发，而存储过程则是通过存储过程名称而被直接调用。n触发器与表格紧密相连，当用户对表进行诸如触发器与表格紧密相连，当用户对表进行诸如UPDATE、INSERT和和DELETE这这些操作时，系统会自动执行触发器所定义的些操作时，系统会自动执行触发器所定义的SQL语句，从而确保对数据的处理符语句，从而确保对数据的处理符合由这些合由这些SQL 语句所定义的规

25、则。语句所定义的规则。 n除此之外，触发器还有其它许多不同的功能：除此之外，触发器还有其它许多不同的功能： n强化约束：触发器能够实现比强化约束：触发器能够实现比CHECK 语句更为复杂的约束：语句更为复杂的约束：q触发器可以很方便地引用其他表的列，去进行逻辑上的检查；触发器可以很方便地引用其他表的列，去进行逻辑上的检查；q触发器是在触发器是在CHECK之后执行的；之后执行的；q触发器可以插入，删除，更新多行。触发器可以插入，删除，更新多行。n跟踪变化：触发器可以侦测数据库内的操作从而禁止数据库中未经许可的更新和跟踪变化：触发器可以侦测数据库内的操作从而禁止数据库中未经许可的更新和变化，确保输

26、入表中的数据的有效性。例如在库存系统中，触发器可以检测到当变化，确保输入表中的数据的有效性。例如在库存系统中，触发器可以检测到当实际库存下降到了需要再进货的临界量，就给出管理员相应提示信息或自动生成实际库存下降到了需要再进货的临界量，就给出管理员相应提示信息或自动生成给供应商的订单；给供应商的订单；n级联运行：触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的不级联运行：触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的不同表中的各项内容。如：设置一个触发器，当同表中的各项内容。如：设置一个触发器，当student表中删除一个学号信息时，表中删除一个学号信息时，对应的对应的sc

27、表中相应的学号信息也被改写为表中相应的学号信息也被改写为NULL或删除相关学生记录；或删除相关学生记录；n调用存储过程：为了响应数据库更新，触发器可以调用一个或多个存储过程。调用存储过程：为了响应数据库更新，触发器可以调用一个或多个存储过程。2. 触发器的种类触发器的种类nSQL Server 2008支持两种类型的触发器：支持两种类型的触发器：DML触发器和触发器和DDL触触发器。发器。nDML触发器：如果用户要通过数据操作语言触发器：如果用户要通过数据操作语言 (DML)编辑数据，则编辑数据，则执行执行 DML 触发器。触发器。DML 事件是针对表或视图的事件是针对表或视图的 INSERT

28、、UPDATE和和DELETE 语句，即语句，即DML触发器在数据修改时被执行。触发器在数据修改时被执行。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务自对待。如果检测到错误（例如，磁盘空间不足），则整个事务自动回滚；动回滚；nDDL触发器为了响应各种数据定义语言触发器为了响应各种数据定义语言 (DDL) 事件而激发。事件而激发。DDL事件主要与以关键字事件主要与以关键字 CREATE、ALTER 和和 DROP 开头的开头的 T-SQL 语句对应。它们可以用于在数据库中执行

29、管理任务，例如，审核语句对应。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作以及规范数据库操作10.2.2 DML触发器的创建和应用触发器的创建和应用n1. DML触发器的分类触发器的分类n触发器有很多用途，对于触发器有很多用途，对于DML触发器来说，最常见的用途就是强触发器来说，最常见的用途就是强制业务规则。例如，当客户下订单时，制业务规则。例如，当客户下订单时，DML触发器可用于检查是触发器可用于检查是否有充足的资金。如果检查完成，就可以完成进一步的操作，或否有充足的资金。如果检查完成，就可以完成进一步的操作，或者返回错误信息，对更新进行回滚。者返回错误信息，对更新进行回

30、滚。n在实际应用中，在实际应用中，DML触发器分为两类：触发器分为两类：（1）AFTER触发器：这类触发器是在记录已经被改变完，相关触发器：这类触发器是在记录已经被改变完，相关事务提交后，才会被触发执行。主要是用于记录变更后的处理或事务提交后，才会被触发执行。主要是用于记录变更后的处理或检查，一旦发现错误，可以用检查，一旦发现错误，可以用ROLLBACK TRANSACTION语句语句来回滚本次的操作。对同一个表达操作，可定义多个来回滚本次的操作。对同一个表达操作，可定义多个AFTER触发触发器，并定义各种触发器执行的先后顺序。器，并定义各种触发器执行的先后顺序。（2）INSTEAD OF触发

31、器：这类触发器并不去执行其所定义的操触发器：这类触发器并不去执行其所定义的操作（作（INSERT、UPDATE、DELETE），而去执行触发器本身所定），而去执行触发器本身所定义的操作。这类触发器一般是用来取代原本的操作，在记录变更义的操作。这类触发器一般是用来取代原本的操作，在记录变更之前被触发的。之前被触发的。2. 触发器中的逻辑（虚拟）表触发器中的逻辑（虚拟）表n当表被修改，无论是插入、修改还是删除，在数据行中所操作确切的记当表被修改，无论是插入、修改还是删除，在数据行中所操作确切的记录，都保存在两个系统的逻辑表中，这两个逻辑表是录，都保存在两个系统的逻辑表中，这两个逻辑表是insert

32、ed（插入）（插入）表和表和deleted（删除）表。（删除）表。n这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。当触发器的工作完成之后，这两个表将会从内存限，没有修改的权限。当触发器的工作完成之后，这两个表将会从内存中删除。中删除。ninserted表里存放的是更新前的记录：对于表里存放的是更新前的记录：对于INSERT操作来说，操作来说，INSERT触发器执行，新的记录插

33、入到触发器表和触发器执行，新的记录插入到触发器表和inserted表中。很显然，只在表中。很显然，只在进行进行INSERT和和UPDATE触发器时，触发器时，inserted表中有数据，而在表中有数据，而在DELETE触发器中触发器中inserted表是空的。表是空的。nDeleted表里存放的是已从表中删除的记录：对于表里存放的是已从表中删除的记录：对于DELETE操作来说，操作来说，DELETE触发器执行，被删除的旧记录存放到触发器执行，被删除的旧记录存放到Deleted表中。表中。nUPDATE操作等价于插入一条新记录，同时删除旧记录。对于操作等价于插入一条新记录，同时删除旧记录。对于U

34、PDATE操作来说，操作来说，UPDATE触发器执行，表中原记录被移动到触发器执行，表中原记录被移动到Deleted表中（更表中（更新完后即被删除），修改过的记录插入到新完后即被删除），修改过的记录插入到Inserted表中。表中。ninserted和和deleted表的结构与触发器所在数据表的结构是完全一致的。表的结构与触发器所在数据表的结构是完全一致的。它们的操作和普通表的操作也一致。例如，若要检索它们的操作和普通表的操作也一致。例如，若要检索 deleted 表中的所表中的所有值，则使用语句：有值，则使用语句：nSELECT * FROM deleted3. 创建创建DML触发器的语法规

35、则触发器的语法规则n创建创建DML触发器的语法规则如下：触发器的语法规则如下：CREATE TRIGGER 触发器名称触发器名称ON table | view -指定操作的对象为表或视图，视图只能被指定操作的对象为表或视图，视图只能被 INSTEAD OF 触触发器引用发器引用 FOR |AFTER | INSTEAD OF -触发器的类型触发器的类型 INSERT , UPDATE , DELETE -指定数据修改操作，指定数据修改操作，AS SQL语句语句,n【例【例10-6】创建触发器】创建触发器stu_delete，实现如下功能：当按照学号删除，实现如下功能：当按照学号删除studen

36、t表中的某学生记录后，对应的该学生在表中的某学生记录后，对应的该学生在sc表中的记录也被表中的记录也被自动删除。自动删除。nT-SQL语句为：语句为：USE stuinfoGOCREATE TRIGGER stu_Delete ON studentFOR DELETEASDELETE FROM sc WHERE sno=(SELECT sno FROM deleted)n执行后，我们查询执行后，我们查询student表和表和sc表，如图表，如图10-6所示，可以看到两所示，可以看到两个表中均存在学号为个表中均存在学号为20070102的学生记录。的学生记录。n在在student表中执行数据删除

37、语句：表中执行数据删除语句：DELETE FROM student WHERE sno=20070102nstudent表中有一行受影响而表中有一行受影响而sc表中有三行数据受影响。说明设定表中有三行数据受影响。说明设定的触发器被触发，的触发器被触发，sc表中的相应数据被自动删除。表中的相应数据被自动删除。n通过以上通过以上AFTER触发器的例子我们可以验证，只有在成功执行触触发器的例子我们可以验证，只有在成功执行触发发T-SQL 语句之后，才会激活语句之后，才会激活AFTER触发器。判断执行成功的标触发器。判断执行成功的标准是：执行了所有与已更新对象或已删除对象相关联的引用级联准是：执行了所

38、有与已更新对象或已删除对象相关联的引用级联操作和约束检查。操作和约束检查。n以删除表中记录为例，整个执行分为以下步骤：以删除表中记录为例，整个执行分为以下步骤：n（1）当系统接收到一个要执行）当系统接收到一个要执行student表删除操作的表删除操作的T-SQL语句语句时，系统将要删除的记录存放在删除表时，系统将要删除的记录存放在删除表Deleted中；中；n（2）把数据表）把数据表student中的相应记录删除；中的相应记录删除；n（3）删除操作激活了事先编制的）删除操作激活了事先编制的AFTER触发器，系统执行触发器，系统执行AFTER触发器中触发器中AS定义后的定义后的T-SQL语句；语

39、句；n（4）触发器执行完毕之后，删除内存中的）触发器执行完毕之后，删除内存中的Deleted表，退出整个表，退出整个操作。若干触发器语句执行失败，则整个过程回滚，恢复到初始操作。若干触发器语句执行失败，则整个过程回滚，恢复到初始状态。状态。10.2.3 DDL触发器的创建和应用触发器的创建和应用nDDL 触发器可用于回滚违反规则的结构更改、审核触发器可用于回滚违反规则的结构更改、审核结构更改或以合适的形式响应结构更改。结构更改或以合适的形式响应结构更改。DDL 触发触发器同器同DML触发器一样，在响应事件时执行。触发器一样，在响应事件时执行。n可以使用与可以使用与DML触发器相似的触发器相似的

40、 T-SQL 语法创建语法创建DDL 触发器，他们具有以下区别：触发器，他们具有以下区别：nDML触发器响应触发器响应INSERT、UPDATE和和DELETE语句语句的操作，而的操作，而DDL触发器响应触发器响应CREATE、ALTER和和DROP语句的操作；语句的操作；n只有在执行完只有在执行完T-SQL 语句后才会触发语句后才会触发DDL触发器，触发器，即即SQL Server仅支持仅支持AFTER类型的类型的DDL触发器；触发器；n系统不会为系统不会为DDL触发器创建触发器创建Inserted表和表和Deleted表。表。1. 创建创建DDL触发器的语法规则触发器的语法规则CREATE

41、 TRIGGER 触发器名称触发器名称ON ALL SERVER| DATABASE-指定触发器的作用域指定触发器的作用域 FOR |AFTER -触发器的类型触发器的类型事件类型事件类型|事件组事件组,n -指定数据修改操作指定数据修改操作AS SQL语句语句 ,n2. DDL触发器的应用触发器的应用【例【例10-7】创建服务器范围的】创建服务器范围的DDL触发器，当创建数据触发器，当创建数据库是，系统返回提示信息：库是，系统返回提示信息：“DATABASE CREATED”。nT-SQL语句为：语句为：CREATE TRIGGER trig_createON ALL SERVERFOR C

42、REATE_DATABASEASPRINT DATABASE CREATEDn运行创建触发器后，使用语句：运行创建触发器后，使用语句：CREATE DATABASE demon运行结果，消息栏内出现我们设定的运行结果，消息栏内出现我们设定的“DATABASE CREATED”10.2.4 查看、修改和删除触发器查看、修改和删除触发器1. 查看数据库中已有触发器查看数据库中已有触发器n查看表中已有哪些触发器，这些触发器究竟对表有哪查看表中已有哪些触发器，这些触发器究竟对表有哪些操作，我们需要能够查看触发其信息。查看触发器些操作，我们需要能够查看触发其信息。查看触发器有两种常用方法：有两种常用方法

43、：n（1）使用）使用SQL Server 2008的的SSMS查看触发器信查看触发器信息息n在在SQL Server 2008中，展开服务器和数据库，此处中，展开服务器和数据库，此处我们选择展开我们选择展开stuinfo数据库。选择表数据库。选择表student，展开，展开触发器选项，即看到我们在例触发器选项，即看到我们在例10-6中建立的触发器中建立的触发器stu_delete。右击触发器。右击触发器stu_delete，从弹出的快捷，从弹出的快捷菜单中选择修改，即可看到触发器的源代码。菜单中选择修改，即可看到触发器的源代码。 （2）使用系统存储过程查看触发器）使用系统存储过程查看触发器n由

44、于触发器为一种特殊的存储过程，我们可以使用前由于触发器为一种特殊的存储过程，我们可以使用前面介绍的系统存储过程面介绍的系统存储过程sp_help和和sp_helptext来查来查看触发器信息。看触发器信息。qsp_help：用于查看触发器的一般信息，如触发器的名称、：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间等。格式为：属性、类型和创建时间等。格式为：EXECUTE sp_help 触触发器名称；发器名称；qsp_helptext：用于查看触发器的：用于查看触发器的T-SQL代码信息。格式为：代码信息。格式为：EXECUTE sp_helptext 触发器名称触发器名称n查

45、看数据库中所有触发器信息要使用查看数据库中所有触发器信息要使用sysobjects表来表来辅助完成，语句为：辅助完成，语句为：SELECT *FROM sysobjects WHERE xtype=TR2. 修改数据库中已有触发器修改数据库中已有触发器n修改触发器也可以在修改触发器也可以在SQL Server 2008的的SSMS中完成，步骤与查看触发中完成，步骤与查看触发器信息一致。器信息一致。n使用使用T-SQL语句修改触发器要区分是语句修改触发器要区分是DML类触发器还是类触发器还是DDL类触发器，类触发器，修改格式分别为：修改格式分别为：n（1）修改）修改DML触发器触发器ALTER

46、TRIGGER 触发器名称触发器名称ON table | view FOR |AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS SQL语句语句,nn（2）修改）修改DDL触发器触发器ALTER TRIGGER 触发器名称触发器名称ON ALL SERVER| DATABASE FOR |AFTER 事件类型事件类型|事件组事件组,nAS SQL语句语句,n3. 删除触发器：删除触发器：系统提供三种方法来删除触发器：系统提供三种方法来删除触发器：（1）在）在SQL Server 2008的的SSMS中完成，右中完成，右击待删除的触发器，从弹出的快捷菜单中选择击待删除的触发器，从弹出的快捷菜单中选择删除命令。删除命令。（2）删除触发器所在的表。在删除表时，系统）删除触发器所在的表。在删除表时，系统会自动删除与该表相关的触发器。会自动删除与该表相关的触发器。（3）使用）使用T-SQL语句语句DROP TRIGGER删除触删除触发器。发器。n基本语句格式为：基本语句格式为：DROP TRIGGER 触发器名称触发器名称,n