[计算机]第10章 存储过程与触发器

数据库中的存储过程与触发器数据库是一个用于存储和管理大量数据的集合，而存储过程和触发器作为数据库中的两种重要对象，在实际的数据库应用中发挥着重要的作用。

本文将详细介绍数据库中的存储过程和触发器的定义、作用以及使用方式，并对它们在实际应用中的优势进行探讨。

存储过程是一组预编译的SQL语句集合，这些语句经过编译并且存储在数据库中，以便后续的重用。

存储过程可以接受参数，并且通过执行一系列SQL语句来实现复杂的操作。

存储过程的主要作用包括提高数据库的性能、减少网络流量、实现封装和重用性。

首先，存储过程可以提高数据库的性能。

当执行一组SQL语句时，存储过程会将这些语句一次性发送给数据库服务器，并且在服务器上进行预编译和优化。

相比于每次发送单独的SQL语句，存储过程能够减少网络往返的时间，提高执行效率。

其次，存储过程能够减少网络流量。

由于存储过程的执行过程在数据库服务器上完成，它只需要将执行结果返回给客户端，而不需要将整个SQL语句和数据传输回客户端。

这样不仅减少了网络传输的数据量，还减少了网络请求的次数，有效降低了网络流量。

此外，存储过程实现了封装和重用性的特点。

通过将一系列SQL语句封装在一个存储过程中，可以减少代码的重复性，提高代码的可维护性。

同时，存储过程可以在不同的应用程序中被调用，实现了代码的重用性，提高了开发效率。

在实际应用中，存储过程常用于完成复杂的业务逻辑。

例如，在某电商网站的订单系统中，存储过程可以用于完成下单流程的各个环节，包括生成订单、更新库存、计算订单总价等。

通过使用存储过程，可以确保这些操作的原子性，避免了在应用层面上进行多个SQL语句的事务管理。

另一个重要的数据库对象是触发器。

触发器是数据库中的一类特殊对象，它与表相关联，并且在特定的事件发生时自动执行一些操作。

触发器的主要作用包括数据完整性的维护、业务规则的实施以及数据审计等。

首先，触发器能够维护数据的完整性。

通过在数据操作之前或之后触发相应的操作，触发器可以保证数据库中的数据满足特定的约束条件。

存储过程与触发器存储过程和触发器是关系型数据库中非常重要的概念和工具。

存储过程是一组预编译的SQL语句集合，经编译后存储在数据库中，可以被反复调用执行。

而触发器是数据库中一种特殊的对象，它与表相关联，当特定的事务操作（如INSERT、UPDATE、DELETE）在关联的表上执行时，触发器会自动执行相应的操作。

在本文中，将详细介绍存储过程和触发器的应用场景和使用方法。

存储过程的优势主要体现在以下几个方面：1.提高性能：存储过程可以减少网络传输的开销，将数据库操作逻辑集中在数据库服务器上执行，减少了网络延迟时间。

此外，存储过程可以预先编译和优化，提高了执行效率。

2.简化开发：存储过程可以将常用的业务逻辑封装在一起，减少了代码的重复编写。

开发人员只需调用存储过程，而不必重复编写相同的SQL语句。

3.减少安全风险：存储过程可以对数据库操作进行权限控制，通过给用户分配不同的执行权限，提高了数据库的安全性。

4.数据库事务管理：存储过程可以将一系列数据库操作封装在一个事务中，保证了数据的一致性和完整性。

触发器的主要优势在于：1.强制数据完整性：触发器可以通过在特定操作之前或之后执行代码，强制执行特定的条件和限制，确保数据库中的数据始终保持一致性和完整性。

2.隐藏复杂逻辑：触发器可以将复杂的业务逻辑隐藏在数据库中，使应用程序的逻辑更加简洁和清晰。

3.自动化处理：触发器可以自动执行一些操作，如更新相关表的数据，发送电子邮件等，减少了人工操作的需要，提高了工作效率。

下面以一个具体的例子来说明存储过程和触发器的使用。

假设有一个订单管理系统，包括订单表和订单明细表。

当插入一个订单时，触发器会自动计算订单的总金额，并更新到订单表中的"total_amount"字段中。

首先创建一个计算订单总金额的存储过程：CREATE PROCEDURE calculateTotalAmountASBEGINFROM order_detailsUPDATE ordersEND然后创建一个触发器，当插入新的订单明细时，自动调用存储过程计算订单的总金额：CREATE TRIGGER updateTotalAmountON order_detailsAFTERINSERTASBEGINFROM inserted;END通过以上的存储过程和触发器，当插入新的订单明细时，触发器会自动调用存储过程计算订单的总金额，并更新到订单表中。

触发器与存储过程触发器（Trigger）是数据库中的一种特殊对象，它与一些特定的数据库事件相关联，并且当这个事件发生时，触发器可以在自动执行的过程中被触发。

而存储过程（Stored Procedure）则是一段预先编译好的SQL 语句的集合，它可以被保存在数据库服务器端，通过调用来执行。

触发器和存储过程都是数据库中的重要组件，它们都可以用于实现数据的自动化处理和一些复杂的业务逻辑。

但是它们在功能和使用方法上有一些不同之处。

首先，触发器的触发条件是事先设置好的，当该条件满足时才会被触发执行，而存储过程是主动调用执行的。

触发器通常与数据库中的表相关联，并且在表上的插入、更新或删除等事件发生时触发。

存储过程可以在任何时候被调用执行，无论是否有其他数据库事件发生。

其次，触发器通常用于实现数据的自动化处理，比如在插入新纪录时通过触发器自动计算一些字段的值，或者在删除记录时触发器做一些相关操作。

而存储过程则更倾向于实现业务逻辑的封装和复用，比如在一个库存管理系统中，可以使用存储过程来实现添加商品、修改商品信息、删除商品等操作。

此外，触发器由数据库引擎直接管理，它是与数据库表密切相关的一种对象，所以当表被删除或者修改时，相关联的触发器也会相应地被删除或修改。

而存储过程则是作为独立于表的对象存在，当数据库表被删除或修改时，存储过程不受影响。

在使用上，触发器和存储过程都可以用于实现一些相同的功能，但触发器更适合于在特定的数据库事件发生时执行自动化的操作，而存储过程更适合于实现复杂的业务逻辑和一些需要主动调用的场景。

总之，触发器和存储过程都是数据库中的重要组件，它们可以用于实现一些自动化的处理和复杂的业务逻辑。

它们在功能和使用方法上有一些不同之处，需要根据具体的需求来选择和使用。

存储过程和触发器存储过程和触发器是SQL Server的数据库对象。

使用存储过程可以提高应用程序的效率。

触发器可以大大增强应用程序的健壮性、数据库的可恢复性和可管理性。

一、存储过程存储过程是一组T-SQL语句，它们只需编译一次，以后即可多次执行。

存储过程是在SQL Server中定义的子过程，是数据库对象之一。

存储过程可以执行范围很宽的各种操作与业务功能。

比如可以插入、更新或删除表中的数据。

通过传递参数值，存储过程可以判断是选择数据还是执行其他操作。

由于存储过程可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；存储过程是包含用于在数据库中执行操作（包括调用其他过程）的编程语句。

此外存储过程可以向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。

故SQL Server 中的存储过程与其他语言中的过程（有时也称：函数）类似。

可以使用 T-SQL EXECUTE 语句来运行存储过程。

存储过程作为 SQL Server 数据库系统中很重要的概念之一，合理的使用存储过程，可以有效地提高程序的性能；并且将商业逻辑封装在数据库系统中的存储过程中，可以大大提高整个软件系统的维护性。

当商业逻辑发生了改变的时候，不再需要修改并编译客户端的应用程序以及重新分发它们到为数众多的用户手中，只需要修改位于服务器端的实现相应商业逻辑的存储过程即可。

使用 SQL Server 创建应用程序时，T-SQL编程语言是应用程序和 SQL Server 数据库之间的主要编程接口。

使用T-SQL程序时，可用两种方法存储和执行程序；一种是将程序存储在本地，然后创建向SQL Server发送命令并处理结果的应用程序；另一种是将程序作为存储过程存储在SQL Server中，然后创建执行过程并处理结果的应用程序。

在SQL Server中使用存储过程而不使用存储在客户端计算机本地的T-SQL程序，原因在于存储过程具有以下的好处：✓存储过程已在服务器注册。

数据库存储过程与触发器1. 引言数据库存储过程和触发器是数据库中常用的两种功能，它们可以通过在特定情况下自动执行一系列的操作，极大地提高了数据库系统的灵活性和功能性。

本文将介绍数据库存储过程和触发器的概念、作用和用法，并且以示例的方式详细展示它们在实际应用中的应用场景。

2. 数据库存储过程2.1 概念数据库存储过程是一组预定义的操作序列，它们以原子的方式执行，可以被多个应用程序调用。

存储过程通常用于处理复杂的业务逻辑、数据处理和数据验证等任务。

存储过程可以在数据库系统中被创建、编辑和执行，可以接受参数来灵活地适应不同的需求。

2.2 作用数据库存储过程具有以下几个重要的作用：•提高性能：存储过程在数据库服务器上执行，可以减少网络传输开销，提高数据库的响应速度。

•简化开发：存储过程将一些常用的操作封装起来，开发者可以通过简单的调用存储过程来完成复杂的业务逻辑，减少了开发工作量。

•保证数据的一致性：存储过程可以通过事务控制来确保数据的一致性和完整性。

2.3 用法数据库存储过程的用法如下：1.创建存储过程：使用CREATE PROCEDURE语句来创建存储过程，并定义输入参数、输出参数和过程体。

CREATE PROCEDURE procedure_name [ (parameter1, parameter2, ...)][RETURNS return_type]BEGIN-- 过程体END;2.执行存储过程：使用CALL语句来执行存储过程，并传递参数。

CALL procedure_name (parameter1, parameter2, ...);3.删除存储过程：使用DROP PROCEDURE语句来删除存储过程。

DROP PROCEDURE procedure_name;4.查看存储过程的定义：使用SHOW PROCEDURE STATUS语句来查看数据库中的存储过程。

SHOW PROCEDURE STATUS;3. 数据库触发器3.1 概念数据库触发器是与表相关联的特殊类型的存储过程，它们在表上的特定操作（插入、更新、删除）发生时自动执行。

从MySQL5.0版本开始就对存储过程和触发器进行了支持，在MySQL进行学习前，先查看您所使用的版本吧，方法有：1.$mysql -V //linux终端下2.select version(); //mysql下3. mysql --help | grep Distrib //linux终端下在了解您所使用的版本支持情况下再下一步存储过程sql语句执行的时候要先编译，然后执行。

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。

用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

一、存储过程介绍存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。

在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。

存储过程是利用SQL Server所提供的Tranact-SQL语言所编写的程序。

Tranact-SQL语言是SQL Server提供专为设计数据库应用程序的语言，它是应用程序和SQL Server数据库间的主要程序式设计界面。

它好比Oracle数据库系统中的Pro-SQL和Informix的数据库系统能够中的Informix- 4GL语言一样。

这类语言主要提供以下功能，让用户可以设计出符合引用需求的程序：1)、变量说明2)、ANSI兼容的SQL命令(如Select,Update….)3)、一般流程控制命令(if…else…、while….)4)、内部函数二、使用存储过程有以下的优点：* 存储过程的能力大大增强了SQL语言的功能和灵活性。

存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

* 可保证数据的安全性和完整性。

# 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。

第10章存储过程和触发器学习目标本章将要学习存储过程和触发器的基本概念、作用和基本操作;本章学习要点：◆存储过程的概念、作用、分类；◆存储过程的创建、查看、修改和执行；◆触发器的主要作用、类型；◆inserted表和deleted表的作用和使用；◆触发器的创建方法、查看、修改和执行;学习导航存储过程Store Procedure和触发器Trigger是SQL Server 数据库系统重要的数据库对象,在以SQL Server 2005 为后台数据库创建的应用程序中具有重要的应用价值;本章主要内容见图10-1所示的学习导航;图10-1 本章内容学习导航存储过程概述Transact-SQL语言是应用程序与SQL Server数据库之间的主要编程接口,大量的时间将花费在Transact-SQL语句和应用程序代码上;在很多情况下,许多代码被重复使用多次,每次都输入相同的代码不但繁琐,更由于在客户机上的大量命令语句逐条向SQL Server 发送,将降低系统运行效率;因此,SQL Server提供了一种方法,它将一些固定的操作集中起来由SQL Server数据库服务器来完成,应用程序只需调用它的名称,就可实现某个特定任务,这种方法就是存储过程;下面将详细介绍存储过程的概念、特点、创建、执行等内容;10.1.1 存储过程的概念SQL SERVER 中T-SQL语言为了实现特定任务而将一些需要多次调用的固定的操作编写成子程序并集中以一个存储单元的形式存储在服务器上,由SQL Server数据库服务器通过子程序名来调用它们,这些子程序就是存储过程;存储过程是一种数据库对象,存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行,具有很强的编程功能;存储过程可以使用EXECUTE语句来运行;在SQL Server中使用存储过程而不使用存储在客户端计算机本地的T-SQL程序有以下几个方面的好处;1加快系统运行速度;存储过程只在创建时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行依次就编译一次,所以使用存储过程可提高数据库执行速度;2封装复杂操作;当对数据库进行复杂操作时如对多个表进行Update Insert,Query,Delete时,可用存储过程将此复杂操作封装起来与数据库提供的事务处理结合一起使用;3实现代码重用;可以实现模块化程序设计,存储过程一旦创建,以后即可在程序中调用任意多次,这可以改进应用程序的可维护性,并允许应用程序统一访问数据库;4增强安全性;可设定特定用户具有对指定存储过程的执行权限而不具备直接对存储过程中引用的对象具有权限；可以强制应用程序的安全性；参数化存储过程有助于保护应用程序不受SQL注入式攻击;5减少网络流量;因为存储过程存储在服务器上,并在服务器上运行;一个需要数百行T-SQL代码的操作可以通过一条执行过程代码的语句来执行；而不需要在网络中发送数百行代码,这样就可以减少网络流量;10.1.2 存储过程的分类在SQL Server 2005中存储过程可以分为两类：系统存储过程和用户存储过程;1．系统存储过程系统存储过程是由SQL Server系统提供的存储过程,可以作为命令执行各种操作;系统存储过程主要用来从系统表中获取信息,为系统管理员管理SQL Server 提供帮助,为用户查看数据库对象提供方便;例如,执行sp_helptext系统存储过程可以显示规则、默认值、未加密的存储过程、用户函数、触发器或视图的文本信息；执行sp_depends系统存储过程可以显示有关数据库对象相关性的信息；执行sp_rename系统存储过程可以更改当前数据库中用户创建对象的名称;SQL Server中许多管理工作是通过执行系统存储过程来完成的,许多系统信息也可以通过执行系统存储过程而获得;系统存储过程定义在系统数据库master中,其前缀是sp_;在调用时不必在存储过程前加上数据库名;有关系统存储过程的详细介绍请参考SQL Server联机丛书;除了以sp_为前缀的系统存储过程,我们还常见到以xp_为前缀的存储过程,这种存储过程为扩展存储过程;扩展存储过程主要用于扩展SQL Server的功能;2．用户存储过程用户存储过程是指用户根据自身需要,为完成某一特定功能,在用户数据库中创建的存储过程;用户创建存储过程时,存储过程名的前面加上“”,是表示创建全局临时存储过程；在存储过程名的前面加上“”,是表示创建局部临时存储过程;局部临时存储过程只能在创建它的会话中可用,当前会话结束时除去；全局临时存储过程可以在所有会话中使用,即所有用户均可以访问该过程;它们都保存在tempdb数据库中;10.1.3存储过程的创建与管理在SQL Server 2005 中通常可以使用两种方法创建存储过程：一种是使用图形化管理工具SQL Server Management Studio 创建存储过程；另一种是使用T-SQL 语句创建存储过程;创建存储过程时,需要注意下列事项：●只能在当前数据库中创建存储过程;●创建存储过程时,应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值;●在用户存储过程的定义中不能使用下列对象创建语句：CREATE VIEW、CREATE DEFAULT、CREATERULE、CREATE PROCEDURE、CREATE TRIGGER;即在存储过程的创建中不能嵌套创建以上这些对象;存储过程创建后,可以使用EXECUTE语句来执行可以简写为EXEC,如果它是一个批处理中的第一条语句,则关键字EXECUTE或EXEC也可省略;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-3所示;3用户在模版文档窗口中根据相应提示输入存储过程名称和T-SQL语句;创建存储过程“stu_softjs”,如图10-4所示;4单击执行按钮,完成存储过程的创建;提示·在模板文档窗口中可以把不必要的参数去掉;·在第2步骤右键单击“存储过程”节点,选择“刷新”,即可看到刚刚创建好的存储过程;图10-3 创建存储过程模版文档窗口图10-4 创建存储过程“stu_softjs”案例10-3使用SSMS执行上面例子中创建的存储过程“stu_softjs”;⑴启动SQL Server Management Studio,在对象资源管理器中依次展开数据库→student→可编程性→存储过程节点;⑵右键单击stu_softjs存储过程,选择执行存储过程命令,如图10-5所示;⑶打开执行过程对话框,再单击确定按钮即可;图10-5 选择执行存储过程命令10.1.5 使用SSMS查看、修改和删除存储过程案例10-4使用SSMS,查看上例中创建的存储过程“stu_softjs”的属性;1在如图10-5所示的右键菜单中,选择属性菜单,打开存储过程属性对话框;2选择常规选项卡：可以查看到该存储过程属于哪个数据库、创建如期和属于男个数据库用户等信息;3选择权限选项卡：可以为该存储过程添加用户并授予其权限;4选择扩展属性选项卡：可以了解排序规则等扩展属性;提示在如图10-4所示的右键菜单中,选择“删除”菜单命令可以删除指定的存储过程；选择“修改”命令进入存储过程文本修改状态,可对存储过程进行修改；选择“重命名”可以实现存储过程的名称的更改;10.1.6 使用T-SQL语句创建和执行存储过程1.创建存储过程使用T-SQL语句CREATE PROC可以创建存储过程,其基本语句格式如下所示;CREATE PROCEDURE 存储过程名{参数1 数据类型}=默认值 OUTPUT, nWHTI ENCRYPTION│RECOMPILEASSQL语句参数含义：◆存储过程名：要符合标识符规则,少于128个字符;◆参数：过程中的参数;在CREATE PROCEDURE语句中可以声明一个或多个参数;◆OUTPUT：表明该参数是一个返回参数;◆AS：用于指定该存储过程要执行的操作;◆SQL语句：是存储过程中要包含的任意数目和类型的T-SQL语句;◆ENCRYPTION：用于加密存储过程文本;本加密的存储过程,其图标上有“加锁”标志,其定义内容不可修改,也不可通过系统存储过程sp_helptext查看;◆RECOMPILE：设置该选项后,存储过程将在运行时重新编译;案例10-5在“stu”数据库中,创建一个存储过程“stu_softxs”,通过该存储过程可以查询软件技术专业的学生信息;1 在“查询编辑器”窗口中输入如下代码：USE stuGOCREATE PROC stu_softxsASSelectFrom studentWhere s_classid inSELECT c_id FROM classWHERE sp_id inSELECT sp_idFROM specialityWHERE sp_name=’软件技术’GO2 单击“分析”按钮,进行语法检查；再单击“执行”按钮,创建存储过程;2.执行存储过程执行存储过程的基本语句格式：EXEC procedure_name Value_List参数含义:●procedure_name：要执行的存储过程的名称;●Value_List：输入参数值;案例10-6执行以上存储过程“stu_softxs”;代码如下:USE STUGOEXEC stu_softxsGO执行以上代码,结果如图10-6所示;图10-6 执行存储过程“stu_softxs”结果10.1.7 创建和执行带参数的存储过程带参数的存储过程可以扩展存储过程的功能;使用输入参数,可以将外部信息输入到存储过程；使用输出参数,可以将存储过程内的信息转到外部;创建带参数的存储过程时,参数可以是一个,也可以是多个,多个参数时,参数之间用逗号分隔;所有数据类型均可以作为存储过程的参数,一般情况下,参数的数据类型要与它相关的字段的数据类型一致;1.带有输入参数的存储过程案例10-7在“stu”数据库中,创建存储过程“stu_zymc”,该存储过程带有一个用于接收用户输入专业名称的输入参数;该存储过程要求被执行时,它将根据用户输入的专业名称列出该专业的所有班级信息;代码如下：CREATE PROC stu_zymc zymc varchar20/变量参数的数据类型与长度都要和表中相关字段的定义一致/ASSELECT FROM classWHERE sp_id inSELECT sp_idFROM specialityWHERE sp_name=zymcGO执行该存储过程“stu_zymc”,代码如下：Use stuGoEXEC stu_zymc 软件技术go执行结果如图10-7所示;提示执行带参数的存储过程,有两种方式;·按位置转递;在调用存储过程时,直接给出参数值;如果多于一个参数,给出的参数值要与定义的参数顺序一致;例如：EXEC stu_zymc 软件技术;·使用参数名称转递;在调用存储过程时,按“参数名=参数值”的形式给出参数值;采用此方式,参数如果多于一个时,给出的参数顺序可以与定义的参数的顺序不一致;例如：EXEC stu_zymc zymc=软件技术;图10-7 执行存储过程显示出相应专业的班级信息2.带有参数默认值的存储过程案例10-8如果要求上例中创建的存储过程stu_zymc在被执行时不给出参数值将默认显示软件技术专业的班级信息,则创建该存储过程的代码为：/变量参数的默认值是“软件技术”专业/CREATE PROC stu_zymc zymc varchar20 =’软件技术’ASSELECT FROM classWHERE sp_id inSELECT sp_idFROM specialityWHERE sp_name=zymcGO不带参数值执行该存储过程,代码：EXEC stu_zymc3.带有输出参数的存储过程输出参数用于在存储过程中返回值,使用OUTPUT声明输出参数;案例10-9在stu数据库中,创建一个带有输出参数的存储过程stu_xspjf,其中输出参数用于返回学号为02的学生的平均成绩;创建该存储过程代码：USE stuGO /输出参数的数据类型要与它接收的值的类型一致/create proc stu_xspjf xspjf tinyint outputasselect xspjf=avgsc_gradefrom stucoursewhere s_num='02'go执行该存储过程并输出显示的代码如下,执行结果如图10-8所示;use stugodeclare avg tinyintexec stu_xspjf avg outputprint '学号为02的同学的平均分为：'+ltrimstravg+'分'提示执行带有输出参数的存储过程时,需要声明变量来接收存储过程中由输出参数返回来的返回值;一般情况下,声明的变量的数据类型要与存储过程的输出参数的数据类型一致;在使用该变量时,还必须为它加上OUTPUT 声明;图10-8 执行带有输出参数的存储过程并输出显示信息10.1.8 使用T-SQL语句查看、修改和删除存储过程1 使用SSMS查看、修改和删除存储过程请参阅案例10-4、提示和图10-5;2.使用系统存储过程查看存储过程信息1使用sp_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.使用语句修改存储过程使用ALTER PROCEDURE语句可以更改先前通过执行CREATE PROCEDURE语句创建的过程,ALTER PROCEDURE基本语句格式如下;ALTER PROCEDURE 存储过程名{参数1 数据类型}=默认值OUTPUT,…….{参数 n 数据类型}=默认值OUTPUTASSQL语句…….各参数含义与CREATE PROCDURE语句相同,只是把创建时的CREATE 改为了 ALTER;因为修改和创建时的过程方法一样,在这里不再另外举例说明;4.使用语句删除存储过程当存储过程没有存在的意义时,可以使用DROP PROCEDURE 语句将其删除;用于删除存储过程的基本语句格式：DROP PROCEDURE 存储过程名,…n案例10-11删除“stu”数据库中的存储过程“stu_xspjf”;代码如下：USE stuGODROP PROCEDURE stu_xspjfGO触发器概述10.2.1 触发器的概念1.触发器的作用触发器是一种特殊类型的存储过程,它在指定的表中的数据发生变化时自动生效,实现表间的数据完整性和复杂的业务规则;与前面介绍过的存储过程不同,存储过程可以通过存储过程名字被直接调用,而触发器不能,触发器主要通过事件进行触发而自动执行的;当对某一表进行诸如INSERT、UPDATE或DELETE操作时,如果在这些操作上定义了触发器,SQL Server就会自动执行触发器执行触发器中所定义的SQL语句,从而确保对数据的处理必须符合由这些SQL语句所定义的规则;触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性,除此之外,触发器还有其他许多不同的功能;①强化约束：触发器能够实现比CHECK语句更为复杂的约束;CHECK约束不允许引用其他表中的列来完成检查工作,而触发器可以引用其他表中的列;②跟踪变化：触发器可以侦测数据库的操作,从而不允许数据库中未经许可的指定更新和变化;③级联运行：触发器可以侦测数据库内的操作,并自动地级联影响整个数据库的相关内容;例如,某个表上的触发器中包含有对另外一个表的数据操作如插入、更新、删除,而该操作又导致该表上触发器被触发;2.触发器类型在SQL Server2005中,触发器分为DML触发器和DDL触发器两大类;当数据库中发生数据操作语言DML事件时将调用DML触发器,当服务器或数据库中发生数据定义语言DDL事件时将调用DDL触发器;DML触发器是当数据库服务器中发生数据库操作语言DML事件时要执行的操作;DML事件包括对表或视图发出的UPDATE、INSERT或DELETE 语句;DML触发器用于在数据库修改时强制执行业务规则,以及扩展SQL Server2005约束、默认值和规则的完整性检查逻辑;根据DML触发器被激活的时机不同又可以分为AFTER触发器和INSTEAD OF 触发器;①AFTER触发器又称为后触发器;在执行了INSERT、UPDATE 或DELETE语句操作之后执行AFTER触发器;指定AFTER触发器与指定FOR相同,它是Microsoft SQL Server早期版本中唯一可用的选项,但AFTER触发器只能在表上指定;②INSTEAD OF触发器又称为替代触发器;该类触发器代替触发器操作执行,即触发器在数据发生变动之前被触发,取代变动数据的操作INSERT、UPDATE或DELETE操作,执行触发器定义的操作;该类触发器既可在表上定义,也可在视图上定义;对于每个触发器操作INSERT、UPDATE和DELETE只能定义一个INSTEAD OF触发器;DDL触发器是SQL Server2005的新增功能;DDL触发器是一种特殊的触发器,它不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发;相反,它在响应数据定义语言DDL语句时触发,这些语句主要是以CREATE、ALTER和DROP开头的语句;它们可以用于数据库中执行管理任务,例如,审核以及规范数据库操作;因为DDL触发器和DML触发器可以使用相似的SQL语法进行创建、修改和删除,它们还具有其他相似的行为;所以这里只介绍DML触发器的创建与使用;10.2.2 inserted表和deleted表系统为每个触发器创建两个特殊临时表：inserted表和deleted表;这两个表都是逻辑表,由系统管理存储在内存中,它们在结构上与该触发器作用的表相同;这两个表是只读,用户不能对其修改和写入内容,但可以在触发器执行过程中引用这两个表中的数据;当触发器工作完成后,与该触发器相关的这两个表也被删除;Inserted表用于存储INSERT和UPDATE语句所影响的行的副本;如果表存在INSERT 触发器,向表中插入数据时,系统将自动创建一个与触发器具有相同表结构的Inserted临时表,新的记录被添加到触发器表和Inserted表中;Inserted表就是用来存储向原表插入的纪录副本;Deleted表用于存储DELETE和UPDATE语句所影响的行的副本;在执行DELETE或UPDATE语句时,从触发器表中删除原记录,并把删除的记录的副本临时存放到daleted表中;这样做的目的是：一旦触发器遇到了强迫它中止的语句被执行时,删除的那些行可以从deleted表中得以恢复;提示修改表中的数据,相当于删除一条旧的记录,添加一条新的记录;其中,被删除的记录放在Deleted表中,添加的新的记录放在Inserted表中;10.2.3 创建与管理触发器1．使用T-SQL创建与管理触发器T-SQL语言使用CREATE TRIGGER命令创建触发器;创建DML触发器的基本语句格式：CREATE TRIGGER 触发器名ON 表| 视图FOR|AFTER|INSTEAD OFINSERT|UPDATE|DELETEASDML语句案例10-12在stu数据库的学生表student中创建一个触发器tr_scxs,当学生表student有记录被删除时,显示“XXX同学信息已被你成功删除”;创建该触发器代码如下：CREATE TRIGGER tr_scxsON studentFOR DELETEASBEGINDECLARE xsxx CHAR10SELECT xsxx=s_name FROM DELETEDPRINT xsxx +‘同学信息已被你成功删除’END创建触发器后,删除一条记录,验证该触发器,代码如下：Use stuGoDelete student where s_num='01'执行以上删除记录语句后,结果如图10-10所示;图10-10 删除记录激活触发器返回信息提示·虽然DML触发器可以引用临时表,但不能对临时表或系统表创建DML触发器;·对含有用DELETE或UPDATE操作定义的外键的表,不能定义INSTEAD OF DELETE和INSTEAD OF UPDATE触发器;·TRUNCATE TABLB 语句不会触发DELETE触发器,因为TRUNCATE TABLB语句没有执行记录;案例10-13在stu数据库中创建一个删除触发器tr_delxs,当表student中的记录要被删除时,激活该触发器,显示“不能删除本表中的数据请与管理员联系”的提示信息;创建该触发器代码如下：USE stuGOCREATE TRIGGER tr_delxsON studentINSTEAD OF DELETEASPRINT ‘不能删除本表中的数据请与管理员联系’GO创建该触发器后,删除一条记录,验证触发器,代码如下：Use stuGoDelete student where s_num='02'执行以上删除记录语句后,结果如图10-11所示;再重新打开student表时发现学号为02的学生记录还在,没有被删除;图10-11 要删除记录时激活触发器并返回信息2．使用SSMS创建触发器案例10-14为“stu”数据库的stucourse表创建一个更新触发器tr_upsc,当更新了该表中的X 条记录信息时,显示“你已经成功更新的记录信息有X条”;操作步骤如下：1启动SQL Server Management Studio,在对象资源管理器中依次展开数据库→stu→表节点;2展开stucourse表,右键单击触发器,选择新建触发器,如图10-12所示;3打开新建触发器模板文档窗口,根据相应提示输入创建触发器的文本,创建代码如下;4执行创建触发器的语句,语句成功执行后,则创建好触发器;创建该触发器代码如下：USE stuGOCREATE TRIGGER tr_upscON stucourseAFTER UPDATEASBEGINdeclare num tinyintselect num=count from insertedprint /你已经成功更新的记录信息有'+ltrimstrnum+'条/ENDGO使用UPDATE语句更新表stucourse中学号为03的学生成绩,每门成绩都减少5分,验证该触发器的功能,如图10-13所示;图10-12 选择新建触发器图10-13 更新信息激发触发器返回信息10.2.4 查看触发器信息1.使用系统存储过程查看触发器使用系统存储过程sp_helptrigger和sp_helptext可以查看触发器,但作用有所差异：使用sp_helptrigger返回的是触发器的类型,而使用sp_helptext则显示触发器的定义文本;使用系统存储过程sp_helptrigger查看触发器的基本语句格式如下：sp_helptrigger 表名 ,触发器类型使用系统存储过程sp_helptext查看触发器的基本语句格式如下：sp_helptext 触发器名案例10-15查看student表中所有触发器的相关信息,同时也显示触发器tr_delxs的定义文本;代码如下：Use stugosp_helptrigger studentGOsp_helptext tr_delxsGO2 使用SSMS查看触发器信息使用SSMS查看触发器的相关信息的步骤如下;1 启动SQL Server Management Studio,在对象资源管理器窗口中,依次展开数据库→stu→表如student表→触发器节点;2 在触发器节点中,右击需要查看的触发器,在快捷菜单中选择查看依赖关系命令,在对象依赖关系对话框中,可以查看该触发器和相关表的依赖关系;在快捷菜单中选择修改命令,可以查看触发器的定义文本信息;提示在第2步骤的右键快捷菜单中选择“修改”命令,也可以对触发器重新修改定义；选择“删除”命令可以删除该触发器;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-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触发器,基本语句格式如下：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 Management Studio禁用、启用和删除触发器的步骤如下：1启动SQL Server Management Studio,在对象资源管理器中依次展开数据库节点、触发器所在的数据库节点和表节点、触发器节点;2右键单击相应的触发器,弹出右键快捷菜单;3选择禁用命令,即可禁用选定的触发器；选择启用命令,即可恢复触发器为活动应用状态；选择删除命令,即刻删除所选的触发器;案例应用提示在各题案例中,在创建存储过程或触发器之前,可以先使用如下代码检测是否已存在该对象,如果存在则先删除再创建,这里各题案例都是假设之前不存在的情况;删除语法如下：IF EXISTSSELECT NAME FROM SYSOBJECTS WHERE NAME = ‘对象名’AND TYPE = ‘类型’DROP PROCEDURE│TRIGGER 对象名对象名：创建的存储过程名或触发器名；类型：存储过程是P,触发器是TR;一、存储过程综合案例案例10-19在stu数据库中,创建一个加密的存储过程Sc_xs,通过该存储过程查询选修课成绩都及格的学生的信息;Use stuGoCreate proc sc_xsWith EncryptionAsSelectFrom student where s_num not in /成绩及格的学生/Select s_numFrom stucourse where sc_grade <60 /子查询中是成绩不资格的学号/Go执行以下代码,验证存储过程;Exec sc_xs /执行该存储过程/。