《第10章存储过程和触发器.doc》由会员分享，可在线阅读，更多相关《第10章存储过程和触发器.doc（19页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、,第10章 存储过程和触发器【学习目标】本章将要学习存储过程和触发器的基本概念、作用和基本操作。本章学习要点：u 存储过程的概念、作用、分类；u 存储过程的创建、查看、修改和执行；u 触发器的主要作用、类型；u inserted表和deleted表的作用和使用；u 触发器的创建方法、查看、修改和执行。【学习导航】存储过程（Store Procedure）和触发器（Trigger）是SQL Server 数据库系统重要的数据库对象，在以SQL Server 2005 为后台数据库创建的应用程序中具有重要的应用价值。本章主要内容见图10-1所示的学习导航。图10-1 本章内容学习导航10.1 存储
2、过程概述Transact-SQL语言是应用程序与SQL Server数据库之间的主要编程接口，大量的时间将花费在Transact-SQL语句和应用程序代码上。在很多情况下，许多代码被重复使用多次，每次都输入相同的代码不但繁琐，更由于在客户机上的大量命令语句逐条向SQL Server 发送，将降低系统运行效率。因此，SQL Server提供了一种方法，它将一些固定的操作集中起来由SQL Server数据库服务器来完成，应用程序只需调用它的名称，就可实现某个特定任务，这种方法就是存储过程。下面将详细介绍存储过程的概念、特点、创建、执行等内容。10.1.1 存储过程的概念SQL SERVER 中T-
3、SQL语言为了实现特定任务而将一些需要多次调用的固定的操作编写成子程序并集中以一个存储单元的形式存储在服务器上，由SQL Server数据库服务器通过子程序名来调用它们，这些子程序就是存储过程。存储过程是一种数据库对象，存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行，具有很强的编程功能。存储过程可以使用EXECUTE语句来运行。在SQL Server中使用存储过程而不使用存储在客户端计算机本地的T-SQL程序有以下几个方面的好处。（1）加快系统运行速度。存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行依次就编译一次，所以使
4、用存储过程可提高数据库执行速度。（2）封装复杂操作。当对数据库进行复杂操作时（如对多个表进行Update Insert，Query，Delete时），可用存储过程将此复杂操作封装起来与数据库提供的事务处理结合一起使用。（3）实现代码重用。可以实现模块化程序设计，存储过程一旦创建，以后即可在程序中调用任意多次，这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。（4）增强安全性。可设定特定用户具有对指定存储过程的执行权限而不具备直接对存储过程中引用的对象具有权限；可以强制应用程序的安全性；参数化存储过程有助于保护应用程序不受SQL注入式攻击。（5）减少网络流量。因为存储过程存储在服务器上
5、，并在服务器上运行。一个需要数百行T-SQL代码的操作可以通过一条执行过程代码的语句来执行；而不需要在网络中发送数百行代码，这样就可以减少网络流量。10.1.2 存储过程的分类在SQL Server 2005中存储过程可以分为两类：系统存储过程和用户存储过程。1系统存储过程系统存储过程是由SQL Server系统提供的存储过程，可以作为命令执行各种操作。系统存储过程主要用来从系统表中获取信息,为系统管理员管理SQL Server 提供帮助,为用户查看数据库对象提供方便。例如，执行sp_helptext系统存储过程可以显示规则、默认值、未加密的存储过程、用户函数、触发器或视图的文本信息；执行sp
6、_depends系统存储过程可以显示有关数据库对象相关性的信息；执行sp_rename系统存储过程可以更改当前数据库中用户创建对象的名称。SQL Server中许多管理工作是通过执行系统存储过程来完成的，许多系统信息也可以通过执行系统存储过程而获得。系统存储过程定义在系统数据库master中，其前缀是sp_。在调用时不必在存储过程前加上数据库名。有关系统存储过程的详细介绍请参考SQL Server联机丛书。除了以sp_为前缀的系统存储过程，我们还常见到以xp_为前缀的存储过程，这种存储过程为扩展存储过程。扩展存储过程主要用于扩展SQL Server的功能。2用户存储过程用户存储过程是指用户根据
7、自身需要，为完成某一特定功能，在用户数据库中创建的存储过程。用户创建存储过程时，存储过程名的前面加上“#”，是表示创建全局临时存储过程；在存储过程名的前面加上“#”，是表示创建局部临时存储过程。局部临时存储过程只能在创建它的会话中可用，当前会话结束时除去；全局临时存储过程可以在所有会话中使用，即所有用户均可以访问该过程。它们都保存在tempdb数据库中。10.1.3存储过程的创建与管理在SQL Server 2005 中通常可以使用两种方法创建存储过程：一种是使用图形化管理工具SQL Server Management Studio 创建存储过程；另一种是使用T-SQL 语句创建存储过程。创建
8、存储过程时，需要注意下列事项： 只能在当前数据库中创建存储过程。 创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。 在用户存储过程的定义中不能使用下列对象创建语句：CREATE VIEW、CREATE DEFAULT、CREATE RULE、CREATE PROCEDURE、CREATE TRIGGER。即在存储过程的创建中不能嵌套创建以上这些对象。存储过程创建后，可以使用EXECUTE语句来执行（可以简写为EXEC），如果它是一个批处理中的第一条语句，则关键字EXECUTE（或EXEC）也可省略。
9、10.1.4 使用SSMS创建和执行存储过程【案例10-1】执行系统存储过程sp_help查看教务管理数据库stu中class表的信息。程序清单：use stugoexec sp_help class 运行以上程序，结果如图10-2所示。图10-2 执行系统存储过程sp_help查看class表信息【案例10-2】在数据库stu中，创建一个名称为“stu_softjs”的存储过程,通过该存储过程可查询出软件工程系所有教授的信息。操作步骤如下：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，依次展开【数据库】【stu】【可编程性】节点。（2）右键单
10、击【存储过程】节点，选择【新建存储过程】命令，打开【创建存储过程模版】文档窗口，如图10-3所示。（3）用户在模版文档窗口中根据相应提示输入存储过程名称和T-SQL语句。创建存储过程“stu_softjs”，如图10-4所示。（4）单击【执行】按钮，完成存储过程的创建。【提示】在模板文档窗口中可以把不必要的参数去掉。在第（2）步骤右键单击“存储过程”节点，选择“刷新”，即可看到刚刚创建好的存储过程。图10-3 【创建存储过程模版】文档窗口图10-4 创建存储过程“stu_softjs”【案例10-3】使用SSMS执行上面例子中创建的存储过程“stu_softjs”。 启动SQL Server
11、Management Studio,在【对象资源管理器】中依次展开【数据库】【student】【可编程性】【存储过程】节点。 右键单击【stu_softjs】存储过程，选择【执行存储过程】命令，如图10-5所示。 打开【执行过程】对话框，再单击【确定】按钮即可。图10-5 选择【执行存储过程】命令10.1.5 使用SSMS查看、修改和删除存储过程【案例10-4】使用SSMS，查看上例中创建的存储过程“stu_softjs”的属性。（1）在如图10-5所示的右键菜单中，选择【属性】菜单，打开【存储过程属性】对话框。（2）选择【常规】选项卡：可以查看到该存储过程属于哪个数据库、创建如期和属于男个数
12、据库用户等信息。（3）选择【权限】选项卡：可以为该存储过程添加用户并授予其权限。（4）选择【扩展属性】选项卡：可以了解排序规则等扩展属性。【提示】在如图10-4所示的右键菜单中，选择“删除”菜单命令可以删除指定的存储过程；选择“修改”命令进入存储过程文本修改状态，可对存储过程进行修改；选择“重命名” 可以实现存储过程的名称的更改。10.1.6 使用T-SQL语句创建和执行存储过程1.创建存储过程使用T-SQL语句CREATE PROC可以创建存储过程，其基本语句格式如下所示。CREATE PROCEDURE 存储过程名参数1 数据类型=默认值 OUTPUT， nWHTI ENCRYPTIONR
13、ECOMPILEASSQL语句参数含义：u 存储过程名：要符合标识符规则，少于128个字符。u 参数：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。u OUTPUT：表明该参数是一个返回参数。u AS：用于指定该存储过程要执行的操作。u SQL语句：是存储过程中要包含的任意数目和类型的T-SQL语句。u ENCRYPTION：用于加密存储过程文本。本加密的存储过程，其图标上有“加锁”标志，其定义内容不可修改，也不可通过系统存储过程sp_helptext查看。u RECOMPILE：设置该选项后，存储过程将在运行时重新编译。【案例10-5】在“stu”数据库中，创
14、建一个存储过程“stu_softxs”,通过该存储过程可以查询软件技术专业的学生信息。(1) 在“查询编辑器”窗口中输入如下代码：USE stu GOCREATE PROC stu_softxsAS Select *From studentWhere s_classid in(SELECT c_id FROM classWHERE sp_id in (SELECT sp_id FROM specialityWHERE sp_name=软件技术)GO(2) 单击“分析”按钮，进行语法检查；再单击“执行”按钮，创建存储过程。2.执行存储过程执行存储过程的基本语句格式：EXEC procedure_
15、name Value_List参数含义: procedure_name：要执行的存储过程的名称。 Value_List：输入参数值。【案例10-6】执行以上存储过程“stu_softxs”。代码如下:USE STUGOEXEC stu_softxsGO执行以上代码，结果如图10-6所示。图10-6 执行存储过程“stu_softxs”结果10.1.7 创建和执行带参数的存储过程带参数的存储过程可以扩展存储过程的功能。使用输入参数，可以将外部信息输入到存储过程；使用输出参数，可以将存储过程内的信息转到外部。创建带参数的存储过程时，参数可以是一个，也可以是多个，多个参数时，参数之间用逗号分隔。所有
16、数据类型均可以作为存储过程的参数，一般情况下，参数的数据类型要与它相关的字段的数据类型一致。1. 带有输入参数的存储过程【案例10-7】在“stu”数据库中，创建存储过程“stu_zymc”,该存储过程带有一个用于接收用户输入专业名称的输入参数。该存储过程要求被执行时，它将根据用户输入的专业名称列出该专业的所有班级信息。代码如下：CREATE PROC stu_zymc zymc varchar(20)/*变量参数的数据类型与长度都要和表中相关字段的定义一致*/ASSELECT * FROM classWHERE sp_id in(SELECT sp_id FROM specialityWHE
17、RE sp_name=zymc)GO执行该存储过程“stu_zymc”，代码如下：Use stuGoEXEC stu_zymc 软件技术go执行结果如图10-7所示。【提示】执行带参数的存储过程，有两种方式。按位置转递。在调用存储过程时，直接给出参数值。如果多于一个参数，给出的参数值要与定义的参数顺序一致。例如：EXEC stu_zymc 软件技术。使用参数名称转递。在调用存储过程时，按“参数名=参数值”的形式给出参数值。采用此方式，参数如果多于一个时，给出的参数顺序可以与定义的参数的顺序不一致。例如：EXEC stu_zymc zymc=软件技术。图10-7 执行存储过程显示出相应专业的班级
18、信息2. 带有参数默认值的存储过程【案例10-8】如果要求上例中创建的存储过程stu_zymc在被执行时不给出参数值将默认显示软件技术专业的班级信息，则创建该存储过程的代码为：/*变量参数的默认值是“软件技术”专业*/ CREATE PROC stu_zymc zymc varchar(20) =软件技术 AS SELECT * FROM class WHERE sp_id in(SELECT sp_id FROM specialityWHERE sp_name=zymc)GO 不带参数值执行该存储过程，代码：EXEC stu_zymc3. 带有输出参数的存储过程输出参数用于在存储过程中返回值
19、，使用OUTPUT声明输出参数。【案例10-9】在stu数据库中，创建一个带有输出参数的存储过程stu_xspjf,其中输出参数用于返回学号为0501010102的学生的平均成绩。创建该存储过程代码：USE stuGO/*输出参数的数据类型要与它接收的值的类型一致*/create proc stu_xspjf xspjf tinyint outputasselect xspjf=avg(sc_grade)from stucoursewhere s_num=0501010102go执行该存储过程并输出显示的代码如下,执行结果如图10-8所示。use stugodeclare avg tinyin
20、texec stu_xspjf avg outputprint 学号为0501010102的同学的平均分为：+ltrim(str(avg)+分【提示】执行带有输出参数的存储过程时，需要声明变量来接收存储过程中由输出参数返回来的返回值。一般情况下，声明的变量的数据类型要与存储过程的输出参数的数据类型一致。在使用该变量时，还必须为它加上OUTPUT 声明。图10-8 执行带有输出参数的存储过程并输出显示信息10.1.8 使用T-SQL语句查看、修改和删除存储过程1 使用SSMS查看、修改和删除存储过程请参阅【案例10-4】、【提示】和图10-5。2.使用系统存储过程查看存储过程信息（1）使用sp_
21、help 查看存储过程的一般信息,包含存储过程的名称、拥有者、类型和创建时间,其语法格式为:Exec Sp_help 存储过程名（2）使用sp_helptext查看存储过程的定义信息,其语法格式为:Exec Sp_helptext 存储过程名【案例10-10】分别使用系统存储过程sp_help和sp_helptext查看stu数据库中的存储过程“stu_xspjf”的定义、相关性及一般信息。代码如下，结果如图10-9所示。USE stuGOEXEC sp_help stu_xspjfEXEC sp_helptext stu_xspjfGO 图10-9 使用系统存储过程查看存储过程信息3.使用语
22、句修改存储过程使用ALTER PROCEDURE语句可以更改先前通过执行CREATE PROCEDURE语句创建的过程，ALTER PROCEDURE基本语句格式如下。ALTER PROCEDURE 存储过程名参数1 数据类型=默认值OUTPUT,.参数 n 数据类型=默认值OUTPUTASSQL语句.各参数含义与CREATE PROCDURE语句相同，只是把创建时的CREATE 改为了 ALTER。因为修改和创建时的过程方法一样，在这里不再另外举例说明。4.使用语句删除存储过程当存储过程没有存在的意义时，可以使用DROP PROCEDURE 语句将其删除。用于删除存储过程的基本语句格式：DR
23、OP PROCEDURE 存储过程名,n【案例10-11】删除“stu”数据库中的存储过程“stu_xspjf”。代码如下：USE stu GO DROP PROCEDURE stu_xspjfGO10.2 触发器概述10.2.1 触发器的概念1.触发器的作用触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效，实现表间的数据完整性和复杂的业务规则。与前面介绍过的存储过程不同，存储过程可以通过存储过程名字被直接调用，而触发器不能，触发器主要通过事件进行触发而自动执行的。当对某一表进行诸如INSERT、UPDATE或DELETE操作时，如果在这些操作上定义了触发器，SQL Se
24、rver就会自动执行触发器（执行触发器中所定义的SQL语句），从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，除此之外，触发器还有其他许多不同的功能。 强化约束：触发器能够实现比CHECK语句更为复杂的约束。CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列。 跟踪变化：触发器可以侦测数据库的操作，从而不允许数据库中未经许可的指定更新和变化。 级联运行：触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的相关内容。例如，某个表上的触发器中包含有对另外一个表的数据操
25、作（如插入、更新、删除），而该操作又导致该表上触发器被触发。2.触发器类型在SQL Server2005中，触发器分为DML触发器和DDL触发器两大类。当数据库中发生数据操作语言（DML）事件时将调用DML触发器，当服务器或数据库中发生数据定义语言（DDL）事件时将调用DDL触发器。DML触发器是当数据库服务器中发生数据库操作语言（DML）事件时要执行的操作。DML事件包括对表或视图发出的UPDATE、INSERT或DELETE 语句。DML触发器用于在数据库修改时强制执行业务规则，以及扩展SQL Server2005约束、默认值和规则的完整性检查逻辑。根据DML触发器被激活的时机不同又可以分
26、为AFTER触发器和INSTEAD OF 触发器。 AFTER触发器又称为后触发器。在执行了INSERT、UPDATE 或DELETE语句操作之后执行AFTER触发器。指定AFTER触发器与指定FOR相同，它是Microsoft SQL Server早期版本中唯一可用的选项，但AFTER触发器只能在表上指定。 INSTEAD OF触发器又称为替代触发器。该类触发器代替触发器操作执行，即触发器在数据发生变动之前被触发，取代变动数据的操作（INSERT、UPDATE或DELETE操作），执行触发器定义的操作。该类触发器既可在表上定义，也可在视图上定义。对于每个触发器操作（INSERT、UPDATE
27、和DELETE）只能定义一个INSTEAD OF触发器。DDL触发器是SQL Server2005的新增功能。DDL触发器是一种特殊的触发器，它不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。相反，它在响应数据定义语言（DDL）语句时触发，这些语句主要是以CREATE、ALTER和DROP开头的语句。它们可以用于数据库中执行管理任务，例如，审核以及规范数据库操作。因为DDL触发器和DML触发器可以使用相似的SQL语法进行创建、修改和删除，它们还具有其他相似的行为。所以这里只介绍DML触发器的创建与使用。10.2.2 inserted表和deleted表系统为每个触发
28、器创建两个特殊临时表：inserted表和deleted表。这两个表都是逻辑表，由系统管理存储在内存中，它们在结构上与该触发器作用的表相同。这两个表是只读，用户不能对其修改和写入内容，但可以在触发器执行过程中引用这两个表中的数据。当触发器工作完成后，与该触发器相关的这两个表也被删除。 Inserted表用于存储INSERT和UPDATE语句所影响的行的副本。如果表存在INSERT 触发器，向表中插入数据时，系统将自动创建一个与触发器具有相同表结构的Inserted临时表，新的记录被添加到触发器表和Inserted表中。Inserted表就是用来存储向原表插入的纪录副本。Deleted表用于存储
29、DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，从触发器表中删除原记录，并把删除的记录的副本临时存放到daleted表中。这样做的目的是：一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从deleted表中得以恢复。【提示】修改表中的数据，相当于删除一条旧的记录，添加一条新的记录。其中，被删除的记录放在Deleted表中，添加的新的记录放在Inserted表中。10.2.3 创建与管理触发器1使用T-SQL创建与管理触发器T-SQL语言使用CREATE TRIGGER命令创建触发器。创建DML触发器的基本语句格式：CREATE TRIGGER
30、触发器名ON 表| 视图FOR|AFTER|INSTEAD OFINSERT|UPDATE|DELETEAS DML语句【案例10-12】在stu数据库的学生表student中创建一个触发器tr_scxs，当学生表student有记录被删除时，显示“XXX同学信息已被你成功删除！”。创建该触发器代码如下：CREATE TRIGGER tr_scxsON studentFOR DELETEASBEGINDECLARE xsxx CHAR(10)SELECT xsxx=s_name FROM DELETEDPRINT xsxx +同学信息已被你成功删除！ END 创建触发器后，删除一条记录，验证该
31、触发器，代码如下： Use stuGoDelete student where s_num=0806010101执行以上删除记录语句后，结果如图10-10所示。图10-10 删除记录激活触发器返回信息【提示】虽然DML触发器可以引用临时表，但不能对临时表或系统表创建DML触发器。对含有用DELETE或UPDATE操作定义的外键的表，不能定义INSTEAD OF DELETE和INSTEAD OF UPDATE触发器。TRUNCATE TABLB 语句不会触发DELETE触发器，因为TRUNCATE TABLB语句没有执行记录。【案例10-13】在stu数据库中创建一个删除触发器tr_delxs
32、,当表student中的记录要被删除时，激活该触发器，显示“不能删除本表中的数据！请与管理员联系！”的提示信息。创建该触发器代码如下：USE stuGOCREATE TRIGGER tr_delxs ON studentINSTEAD OF DELETE ASPRINT 不能删除本表中的数据！请与管理员联系！GO创建该触发器后，删除一条记录，验证触发器，代码如下：Use stuGoDelete student where s_num=0602010102执行以上删除记录语句后，结果如图10-11所示。再重新打开student表时发现学号为0602010102的学生记录还在，没有被删除。图10-
33、11 要删除记录时激活触发器并返回信息2使用SSMS创建触发器【案例10-14】为“stu”数据库的stucourse表创建一个更新触发器tr_upsc，当更新了该表中的X条记录信息时，显示“你已经成功更新的记录信息有X条！”。操作步骤如下：（1）启动SQL Server Management Studio，在【对象资源管理器】中依次展开【数据库】【stu】【表】节点。（2）展开stucourse表，右键单击【触发器】，选择【新建触发器】，如图10-12所示。（3）打开【新建触发器】模板文档窗口，根据相应提示输入创建触发器的文本，创建代码如下。（4）执行创建触发器的语句，语句成功执行后，则创建
34、好触发器。创建该触发器代码如下： USE stuGOCREATE TRIGGER tr_upsc ON stucourse AFTER UPDATEAS BEGINdeclare num tinyintselect num=count(*) from insertedprint /*你已经成功更新的记录信息有+ltrim(str(num)+条！*/ENDGO使用UPDATE语句更新表stucourse中学号为0501010103的学生成绩，每门成绩都减少5分，验证该触发器的功能，如图10-13所示。图10-12 选择新建触发器图10-13 更新信息激发触发器返回信息10.2.4 查看触发器信息
35、1.使用系统存储过程查看触发器使用系统存储过程sp_helptrigger和sp_helptext可以查看触发器，但作用有所差异：使用sp_helptrigger返回的是触发器的类型，而使用sp_helptext则显示触发器的定义文本。使用系统存储过程sp_helptrigger查看触发器的基本语句格式如下：sp_helptrigger 表名 ,触发器类型使用系统存储过程sp_helptext查看触发器的基本语句格式如下：sp_helptext 触发器名【案例10-15】查看student表中所有触发器的相关信息，同时也显示触发器tr_delxs的定义文本。代码如下：Use stugosp_h
36、elptrigger studentGOsp_helptext tr_delxsGO2 使用SSMS查看触发器信息使用SSMS查看触发器的相关信息的步骤如下。(1) 启动SQL Server Management Studio，在【对象资源管理器】窗口中，依次展开【数据库】【stu】表（如student表）【触发器】节点。(2) 在【触发器】节点中，右击需要查看的触发器，在快捷菜单中选择【查看依赖关系】命令，在【对象依赖关系】对话框中，可以查看该触发器和相关表的依赖关系。在快捷菜单中选择【修改】命令，可以查看触发器的定义文本信息。【提示】在第（2）步骤的右键快捷菜单中选择“修改”命令，也可以对
37、触发器重新修改定义；选择“删除”命令可以删除该触发器。10.2.5 修改触发器1. 使用T-SQL语言修改触发器T-SQL语言使用ALTER TRIGGER命令修改DML触发器，基本语句格式如下。ALTER TRIGGER 触发器名ON 表| 视图FOR | AFTER |INSTEAD OF INSERT |UPDATE |DELETEASSQL语句修改触发器与创建触发器的语法基本相同，只是将创建触发器的CREATE关键字换成了ALTER关键字而已,在这里不再举例说明它的用法。2. 使用SSMS修改触发器请参阅“使用SSMS查看触发器信息”部分。10.2.6 禁用、启用和删除触发器1.使用T
38、-SQL语句禁用、启用和删除触发器（1）禁用触发器可以使用DLSABLE TRIGGER命令禁用DML触发器，基本语句格式如下：DISABLE TRIGGER 触发器名，n|ALLON 对象名 |数据库 | 服务器【案例10-17】禁用student表上的触发器tr_delxs。 代码如下： Use stuGoDISABLE TRIGGER tr_delxs ON student【提示】禁用触发器不会删除该触发器，该触发器仍然作为对象存在于当前数据库中。禁用触发器后，执行相应的T-SQL语句时，不会引发触发器。（2）启用触发器可以使用ENABLE TRIGGER命令启用DML触发器，基本语句格
39、式如下：ENABLE TRIGGER 触发器名，n|ALLON 对象名 |数据库 | 服务器ENABLE TRIGGER 的基本使用同DISABLE TRIGGER，但作用相反。（3）删除触发器可以使用DROP TRIGGER命令删除DML触发器，基本语句格式如下：DROP TRIGGER 触发器，n【案例10-18】删除student表中的tr_delxs触发器。DROP TRIGGER tr_delxsGO【提示】:仅当所有触发器均使用相同的ON 子句创建时，才能使用一个DROP TRIGGER 语句删除多个DDL触发器。2使用SSMS禁用、启用和删除触发器使用SQL Server Man
40、agement Studio禁用、启用和删除触发器的步骤如下：（1）启动SQL Server Management Studio，在【对象资源管理器】中依次展开【数据库】节点、触发器所在的【数据库】节点和【表】节点、【触发器】节点。（2）右键单击相应的触发器，弹出右键快捷菜单。（3）选择【禁用】命令，即可禁用选定的触发器；选择【启用】命令，即可恢复触发器为活动应用状态；选择【删除】命令,即刻删除所选的触发器。10.3案例应用【提示】在各题案例中，在创建存储过程或触发器之前，可以先使用如下代码检测是否已存在该对象，如果存在则先删除再创建，这里各题案例都是假设之前不存在的情况。删除语法如下：IF
41、EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME = 对象名AND TYPE = 类型)DROP PROCEDURETRIGGER 对象名对象名：创建的存储过程名或触发器名；类型：存储过程是P，触发器是TR。一、存储过程综合案例【案例10-19】在stu数据库中，创建一个加密的存储过程Sc_xs，通过该存储过程查询选修课成绩都及格的学生的信息。Use stuGoCreate proc sc_xsWith EncryptionAsSelect *From student where s_num not in /*成绩及格的学生*/(Select s_nu
42、m From stucourse where sc_grade 0 BEGINSELECT * FROM studentWhere s_name = NameENDELSEprint 对不起，你输入的学生姓名不存在，请重新核对再输入！【案例10-21】在stu数据库中，创建一个存储过程stu_xscj，当输入一个存在的学生姓名时，该存储过程将列出该生各门选修课程的总成绩和平均分。 USE stuGO/*创建存储过程*/CREATE PROCEDURE st_xscj/*定义1个接收学生姓名的输入参数，2个输出成绩的输出参数*/xsname varchar(8),sum tinyint OUTP
43、UT,avgcj tinyint OUTPUTASSELECT sum=sum(sc_grade),avgcj =AVG(sc_grade)FROM stucourse WHERE s_num IN (SELECT s_num FROM student WHERE s_name=xsname)GO/*以下执行存储过程的语句*/USE stuGO/*先定义两个变量用于接收返回值*/DECLARE avgcj1 tinyint DECLARE sum1 tinyintEXEC st_xscj 刘一能,sum1 OUTPUT,avgcj1 OUTPUTSELECT 刘一能 as 姓名,sum1 AS
44、 总成绩,avgcj1 AS 平均分GO二、触发器综合案例【案例10-22】在stu数据库中，为class表建立一个名为“del_class”的DELETE触发器，其作用是当删除class表中的记录时，检查student表中是否存在该班级的学生，如果存在则显示“class表正在被student表使用，该班级信息不允许删除！”的提示信息，否则提示“删除成功！” 的信息。CREATE TRIGGER del_class ON classFOR DELETEASIF EXISTS (SELECT * FROM student,deletedWHERE student.s_classid = deleted.c_id)BEGINPRINT class表正在被student表使用，该班级信息不允许删除！ROLLBACK TRANSACTIONENDElsePrint 删除成功!GO【提示】本题中的判断语句也可以使用：IF (SELECT count(*) FROM student, deletedWHERE student.s_classid = deleted.c_id) 0【案例10-23】在stu数据库的class表中创建一个名为“tr_bjdm”的INSERT触发器。当student表有插入记录的操作时，检查该记录