存储过程和触发器

1、存储过程和触发器存储过程和触发器是SQL Server的数据库对象。使用存储过程可以提高应用程序的效率。触发器可以大大增强应用程序的健壮性、数据库的可恢复性和可管理性。一、 存储过程存储过程是一组T-SQL语句，它们只需编译一次，以后即可多次执行。存储过程是在SQL Server中定义的子过程，是数据库对象之一。存储过程可以执行范围很宽的各种操作与业务功能。比如可以插入、更新或删除表中的数据。通过传递参数值，存储过程可以判断是选择数据还是执行其他操作。由于存储过程可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；存储过程是包含用于在数据库中执行操作（包括调用其他过程）的编程语句。

2、此外存储过程可以向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。故SQL Server 中的存储过程与其他语言中的过程（有时也称：函数）类似。可以使用 T-SQL EXECUTE 语句来运行存储过程。存储过程作为 SQL Server 数据库系统中很重要的概念之一，合理的使用存储过程，可以有效地提高程序的性能；并且将商业逻辑封装在数据库系统中的存储过程中，可以大大提高整个软件系统的维护性。当商业逻辑发生了改变的时候，不再需要修改并编译客户端的应用程序以及重新分发它们到为数众多的用户手中，只需要修改位于服务器端的实现相应商业逻辑的存储过程即可。使用 SQL Server 创建应

3、用程序时，T-SQL编程语言是应用程序和 SQL Server 数据库之间的主要编程接口。使用T-SQL程序时，可用两种方法存储和执行程序；一种是将程序存储在本地，然后创建向SQL Server发送命令并处理结果的应用程序；另一种是将程序作为存储过程存储在SQL Server中，然后创建执行过程并处理结果的应用程序。在SQL Server中使用存储过程而不使用存储在客户端计算机本地的T-SQL程序，原因在于存储过程具有以下的好处：ü 存储过程已在服务器注册。ü 存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。ü 存储过程可以强制应用程序的安

4、全性。ü 存储过程允许模块化程序设计。存储过程被创建后可以多次调用。ü 存储过程是命名代码，允许延迟绑定。ü 存储过程可以减少网络流量。对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一个存储过程，那么当在客户间计算机上调用该存储过程时，网络中传送的只是调用存储过程的语句，从而减少网络负担。一） 存储过程类型在SQL Server 2005中有多种可用的存储过程。主要有以下3种：系统存储过程、用户定义的存储过程和扩展存储过程。1. 系统存储过程SQL Server 2005中许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称

5、为系统存储过程。系统存储过程存储在源数据库中，并且带有sp_前缀。从逻辑意义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。在 SQL Server 2005 中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。SQL Server支持在SQL Server和外部程序之间提供一个接口以实现各种维护活动的系统存储过程。这些扩展存储程序使用xp_前缀。在“Microsoft SQL Server Management Studio”中可以查看系统存储过程。查看步骤为：打开“Microsoft SQL Server Management Studio”后，在“

6、对象资源管理器”展开“数据库”选项，打开master系统数据库，打开“可编程的”目录下的“存储过程”目录，就可以看到“系统存储过程目录”，该目录有所有系统存储过程的列表。如下图所示：2. 用户定义的存储过程用户定义的存储过程是指由用户创建并能完成某一特定功能的存储过程，是主要的存储过程类型，用户自定义的存储过程封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，然后返回输出参数。在SQL Server 2005中，存储过程有两种类型：T-SQL和CLR。其中T-SQL存储过程是指保存着T-SQL语句的

7、集合，可以接受和返回用户提供的参数。CLR存储过程是指.NET Framework公共语言运行时（CLR）方法的引用。3. 扩展存储过程扩展存储过程允许使用编程语言（例如C语言）创建自己的外部例程。 扩展存储过程是指SQL Server的实例可以动态加载和运行的DLL。扩展存储过程直接在SQL Server的实例的地址空间中运行，可以使用SQL Server扩展存储过程API完成编程。二） 存储过程的设计1.存储过程的设计规则2. 限定存储过程内的名称3. 存储过程的内部命名规范化4. 加密过程定义三） 存储过程的实现1.创建存储过程的前提2.创建存储过程1） 使用SQL Server Man

8、agement Studio创建存储过程2） 用T-SQL语句创建存储过程【例1】创建一个名为“产品情况”的存储过程USE 商品信息GOCREATE PROCEDURE 产品情况ASSELECT 产品名称, 价格FROM 产品表GO3. 执行存储过程Execute 产品情况【例2】在“商品信息”数据库下创建一个名为“通过编号查询产品情况”的存储过程。USE 商品信息GOCREATE PROCEDURE dbo.通过产品编号查询产品情况Sno VARCHAR(6)ASBEGINSELECT 产品名称, 价格FROM 产品表WHERE 产品编号=SnoENDGO该存储过程的执行方式为：Execut

9、e 通过产品编号查询产品情况 '100010'或Execute 通过产品编号查询产品情况 Sno='100010'4. 存储过程的修改与删除【例3】修改在“商品信息”数据库下的名为“通过编号查询产品情况”的存储过程，使得能够查找到所有产品编号比给定编号大的所有产品信息。ALTER PROCEDURE dbo.通过产品编号查询产品情况Pno VARCHAR(6)ASBEGINSELECT 产品名称, 价格FROM 产品表WHERE 产品编号>PnoENDGO执行语句为：Execute 通过产品编号查询产品情况Sno='100004'【例4】使

10、用DROP语句删除“通过编号查询产品情况”存储过程DROP PROCEDURE 产品情况DROP PROCEDURE 通过产品编号查询产品情况二、触发器触发器是一种特殊的存储过程，它在执行语言事件时自动生效。触发器通过强制执行一定的业务规则，保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加的功能。约束和触发器是SQL Server 2005提供的来强制执行业务规则和数据完整性的两种主要机制。触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。触发器是数据库中比较高级的应用，灵活使用触发器可以大大增强应用程序的健壮性、数据库的可恢复性和数据库的可管理性。同时可

11、以使开发人员和数据库管理员实现一些复杂的功能，简化开发步骤，降低开发成本。增加开发效率，提高数据库的可靠性。触发器是一种特殊的存储过程，除了具有存储过程的特点外，它还另外含有以下特点：1） 自动执行触发器是自动执行的，可以在一定条件下触发。2） 对相关表实现级联更改触发器可以同步数据库的相关表，进行级联更改。3） 触发器可以实现更复杂的安全检查触发器可以实现比CHECK更复杂的业务规则，还可以引用其他表中的列。4） 触发器可以实现数据库的管理任务如DDL触发器，在DDL语句执行后触发，可以实现一些统一的数据库管理策略。一） 触发器类型SQL Server 2005 包括两大类触发器：DML触发

12、器和DDL触发器。1. DML触发器DML触发器是在执行数据操作语言事件时被调用的触发器，其中数据操作语言事件包括：INSERT、UPDATE 和 DELETE 语句。DML触发器用于在数据被修改时强制执行业务规则，以及扩展Microsoft SQL Server 2005约束、默认值和规则的完整性检查逻辑。触发器中可以包含复杂的Transact-SQL语句触发器整体被看作一个事物，可以回滚。约束和DML触发器在特殊情况下各有优点。DML触发器的主要优点在于它们可以包含使用T-SQL代码的复杂处理逻辑。因此，DML支持约束的所有功能；但DML触发器对于给定的并不一定是最好的方法。实体完整性必须

13、在最低级别上通过索引进行强制，这些索引是PRIMARY KEY 和 UNIQUE 约束的一部分，或者是独立于约束而创建的。域完整性应通过CHECK约束进行强制，而引用完整性（RI）则通过FOREIGN KEY约束进行强制，假设这些约束的功能满足应用程序的功能要求。当约束支持的功能无法满足应用程序的功能要求时，DML触发器将变得非常有用。DML触发器在以下方面非常有用：(1) DML触发器可通过数据库中的相关表实现级联更改。不过，通过级联引用完整性约束可以更有效地进行这些更改。(2) DML触发器可以防止恶意或错误的INSERT、UPDATE以及DELETE操作，并强制执行比CHECK约束定义的

14、限制更为复杂的其他限制。(3) DML触发器可以评估修改前后表的状态，并根据该差异采取措施。(4) 一个表中的多个同类DML触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的操作来响应同一个修改语句。在SQL Server 2005 中，可以创建AFTER触发器、INSTEAD OF触发器和CLR触发器。(1) AFTER触发器。在执行INSERT、UPDATE 或 DELETE语句操作之后执行AFTER触发器。指定AFTER与指定FOR相同，它是SQL Server 早期版本中唯一可用的选项。AFTER触发器只能在表上指定。(2) INSTEAD OF 触发器。执行 I

15、NSTEAD OF 触发器代替通常的触发动作。还可为一个或多个基表的视图定义INSTEAD OF 触发器，而这些触发器能够扩展视图可支持的更新类型。(3) CLR触发器。CLR 触发器可以是 AFTER 触发器还可以是DDL 触发器。CLR 触发器将执行在托管代码（在.NET Framework 中创建并在 SQL Server 中上载的程序集的成员）中编写的方法，而不用执行T-SQL存储过程。2. DDL 触发器DDL 触发器是SQL Server 2005 的新增功能，与DML触发器类似，DDL触发器将激发存储过程以响应事件。与DML不同的是，它们不会响应针对表或视图的UPDATE、INS

16、ERT 或 DELETE 语句而激发。它响应的触发事件是由数据定义语言引起的事件，包括：CREATE、ALTER 和 DROP 语句，DDL 触发器用于执行数据库管理任务，如调解和审计数据库运行。DDL触发器只能在触发事件发生后才能调用执行，即它只能是AFTER类型的。如果要执行以下操作，可使用DDL触发器：(1) 要防止对数据库进行某些更改(2) 希望数据库中发生某种情况以响应数据库架构中的更改(3) 要记录数据库架构中的更改或事件仅在运行触发DDL触发器的DDL语句后，DDL触发器才会激发。DDL触发器无法作为INSTEAD OF触发器使用。二） 触发器的设计与实现(1)创建触发器【例1】

17、 创建一个触发器，使得对于“商品信息”数据库，如果在“产品表”中添加数据，则将向客户端显示一条“数据插入成功”的信息。USE 商品信息GOCREATE TRIGGER 产品表_trigger1ON 产品表FOR INSERTASPRINT '数据插入成功'GOUSE 商品信息GOINSERT INTO 产品表VALUES('100015','IPHONE6',5600,20)GO【例2】 创建一个触发器，使得对于“商品信息”数据库，如果在“产品表”中删除数据，则将向客户端显示一条“数据删除成功”的信息。USE 商品信息GOCREATE TRIGG

18、ER 产品表_trigger2ON 产品表FOR DELETEASPRINT '数据删除成功'GOUSE 商品信息GODELETE FROM 产品表WHERE 产品编号='100013'(2)删除触发器【例3】 删除“产品表_triger2”触发器USE 商品信息GODROP TRIGGER 产品表_trigger2(3)修改触发器【例4】 修改“产品表_triger1”触发器，使得对于商品信息数据库，如果在产品表中添加或者更新数据，则将向客户端显示一条信息“操作成功”的信息。USE 商品信息GOALTER TRIGGER 产品表_trigger1ON 产品表F

19、OR INSERT, UPDATEASPRINT '操作成功'GOUSE 商品信息GOINSERT INTO 产品表 VALUES('100013','MP4',650,50)USE 商品信息GOUPDATE 产品表SET 价格=600WHERE 产品编号='100013'三、 事务由于触发器是对其中的某张表进行添加、删除、修改等操作可能引起另一张或多张表中数据的数据的变化，为了保持数据完整性和一致性，触发器和事务是一个强有力的结合。1. 什么是事务事务是一个不可分隔的逻辑单元，在数据库系统上执行并发操作时事务是最小的控制单元来使用。它包含的所有数据库操作命令作为一个整体一起提交或撤销，即这一组数据库操作命令要要么都执行，要么都不执行。2. 事务的4个属性(1) 原子性(Atomicity)。事务中的所有元素作为一个整体提交或回滚，事务的各元素是不可分的，事务是一个完整的操作。(2) 一致性(Consistency)。事务完成时，数据必须是一致的，也就是说，在事务开始之前，数据存储中的数据处于一致状态，保证数据的无损。(3) 隔离性(Isolati