第7章事务、存储过程、触发器和游标.ppt

1、第7章 事务、存储过程、触发器和游标,7.1 事 务 7.2 存储过程 7.3 触发器及其用途 7.4 游标,7.1 事 务,1、事务的概念 事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。,2、事务的特性,原子性：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。 隔离性：一事务的执行不能被其它事务干扰 持续性（永久性）：指事务一旦提交，则其对数据库中数据的改变就应该是永久的,3、事务的分类,SQL Server 的事务模式可分为显式事务、隐式事务和自

2、动事务三种。 1) 显式事务 显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括： BEGIN TRANSACTION:标识一个事务的开始，即启动事务。 COMMIT TRANSACTION、COMMIT WORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。 ROLLBACK TRANSACTION、ROLLBACK WORK：标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。,2) 隐式事务 在隐式事务模式下，在当前事务提交或回滚后，SQL Server自动开始下一个事务。所以，隐式

3、事务不需要使用BEGIN TRANSACTION语句启动事务，而只需要用户使用ROLLBACK TRANSACTION、ROLLBACK WORK、COMMIT TRANSACTION、COMMIT WORK等语句提交或回滚事务。在提交或回滚后，SQL Server自动开始下一个事务。 执行SET IMPLICIT_TRANSACTIONS ON语句可使SQL Server进入隐式事务模式。在隐式事务模式下，当执行下面任意一个语句时，可使SQL Server重新启动一个事务： 所有CREATE语句 ALTER TABLE 所有DROP语句 TRUNCATE TABLE GRANT REVOKE

4、 INSERT UPDATE DELETE SELECT OPEN FETCH 需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONS 连接选项即可。,3) 自动事务模式 在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQL Server的默认事务管理模式，当与SQL Server建立连接后，直接进入自动事务模式，直到使用BEGIN TRANSACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS 连接选项进入隐式事务模式为止。 而当显式事务被提交或IMPLICIT_TR

5、ANSACTIONS 被关闭后，SQL Server又进入自动事务管理模式。,示例：,BEGIN TRAN demo SELECT * FROM Student INSERT INTO Student VALUES(9711112,张三, ) SELECT * FROM Student ROLLBACK - 回滚整个事务 或：COMMIT - 提交事务,下面例子说明自动事务模式下各语句的执行情况和SQL Server对批的处理： -SQL Server处于自动事务管理模式 Use pubs Go Create table TB_transaction1 (col1 INT PRIMARY KE

6、Y, col2 datetime) Go INSERT TB_transaction1 VALUES(1,GETDATE() INSERT TB_transaction1 VALUES(1,GETDATE()-违反约束 Go,SELECT times=1,* from TB_transaction1 Go Begin tran-进入显示事务模式 INSERT TB_transaction1 VALUES(2,GETDATE() SELECT times=2,* from TB_transaction1 INSERT TB_transaction1 VALUE(3,GETDATE()-语法错误

7、Rollback Go SELECT times=2,* from TB_transaction1 -此时，又重新进入自动事务模式 Go,SET IMPLICIT_TRANSACTIONS ON进入隐含事务模式 go insert TB_transaction1 VALUES(3,GETDATE() insert TB_transaction1 VALUES(4,GETDATE() rollback go select * from TB_transaction1 delete from TB_transaction1 rollback select * from TB_transaction

8、1 set implicit_transactions off delete from TB_transaction1 rollback-此时该命令不成功，因为已经进入自动事务模式 go,4、并发问题与事务隔离,在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。SQL Server使用资源锁定的方法管理用户的并发操作。如果在用户并发访问期间没有锁定数据库资源，用户操作相同的数据时可能会产生一些意想不到的问题。这些问题包括： 。丢失修改或被覆盖 。读脏数据 。不能重复读：一个事物多次访问同一行数据而每次所读取的数据是不同的。 。幻影读：是指一个事务多次读取一定范围内的数据

9、行，而前后两次所读取的数据行是不同,为了避免产生并发访问问题，SQL Server使用不同类型的锁对资源进行锁定，从而限制在一个事务读取数据期间其他事务锁执行的操作类型，即对事务进行隔离。不同的并发访问问题可以通过设置不同的事务隔离级别加以解决。事务的隔离级别控制一个事务与其他事务的隔离程度，它决定该事务在读取数据时对资源所使用的锁类型。,SQL-92标准定义了以下4种隔离级别： 未提交读：这是4种隔离级别中限制最低的级别，它仅能保证SQL Server不读取物理损坏的数据。在这种隔离级别下，不发出共享锁，也不接受排它锁，事务可以对数据执行未提交读或脏读；在事务结束前可以更改数据集内的数值，行

10、也可以出现在数据集中或从数据集消失。 提交读：它要求在读取数据时控制共享锁以避免发生脏读，但数据可在事务结束前更改，这可能产生不能重复读或幻影读问题。 可重复读：锁定查询中使用的所有数据以防止其他用户更新，但是其他用户可以将新的幻影行插入到数据集中，新插入的幻影行将出现在当前事物的后续读取结果集中。可重复读能够避免产生脏读和非重复读问题，但仍可能导致幻影读问题。 可串行读：这是事务隔离的最高级别，它使事务之间完全隔离，所以将导致并发级别较低。在这种隔离级别下，SQL Server在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或向数据集内插入数据行，从而避免出现脏读、非重复读或

11、幻影读等并发问题。,调用Transact-SQL中的SET TRANSACTION INOLATION LEVEL语句可以调整事务的隔离级别，以控制由该连接所发出的所有SELECT语句的默认事务锁定行为。该语句的语法格式为： SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED |READ COMMITTED |REPEATED READ |SERIALIZATION ,5. 事务处理语句,SQL Server中有关事务的处理语句有：,关于事务保存点,保存点提供了一种机制，用于回滚部分事务。在应用程序中，使用SAVE TRANSACTION save

12、point_name语句设置保存点，使用ROLLBACK TRANSACTION savepoint_name将事务回滚到保存点。,关于嵌套事务： 说明：在定义一个事务时，BEGIN TRANSACTION语句应与COMMIT TRANSACTION语句或ROLLBACK TRANSACTION成对出现。在SQL Server中，显示事务定义语句可以嵌套.一个嵌套的事务是一系列子事务ti的集合, T=t1,t2,tn,这些子事务中的每一个又可以是拥有它自己的事务.T能够决定子事务ti的启动和终止,反过来,如果T中的一个子事务ti终止,它强制T终止;如拖ti提交,这一动作并不能使ti成为永久的,

13、如果T终止(回滚),那么ti的提交将被撤消. 也就是说，SQL Server忽略内部事务的提交，根据最外部事务结束时采取的操作，将提交或者回滚事务。如果提交外部事务，则内层嵌套的事务也会提交。如果回滚外部事务，则不论此前是否提交过内层事务，所有内层事务都将回滚。,但实际上只有最外层的BEGIN TRANSACTION语句和COMMIT TRANSACTION语句才能建立和提交事务；在回滚事务时，也只能使用最外层定义的事务名或存储点标记，而不能使用内层定义的事务名。事务嵌套常用在存储过程或触发器内，它们可以使用BEGIN TRANSACTION 。COMMIT TRANSACTION对来相互调用

14、。,6. 事务处理实例,例1、 Use pubs go BEGIN TRANSACTION demo SELECT * FROM discounts INSERT discounts VALUES(demo1,null,null,null,20.0) SAVE TRANSACTION save_demo INSERT discounts VALUES(demo2,null,null,null,20.0) SELECT * FROM discounts ROLLBACK TRANSACTION save_demo回滚部分事务 SELECT * FROM discounts ROLLBACK TR

15、ANSACTION回滚整个事务 SELECT * FROM discounts,例2、事务嵌套 use pubs go BEGIN TRANSACTION demo SELECT * FROM discounts INSERT discounts VALUES(demo1,null,null,null,20.0) begin TRANSACTION demo1 INSERT discounts VALUES(demo2,null,null,null,20.0) SELECT * FROM discounts ROLLBACK TRANSACTION demo1 SELECT * FROM di

16、scounts ROLLBACK TRANSACTION-出错，因为相应事务已经被回滚 SELECT * FROM discounts,例3、 USE pubs GO CREATE TABLE TB_transaction2 (col1 int IDENTITY, col2 char(10) GO SET IMPLICIT_TRANSACTIONS ON GO INSERT TB_ transaction2 VALUES(row1) INSERT TB_ transaction2 VALUES(row2) Go COMMIT TRANSACTION PRINT 第一个隐式事务所插入的数据: S

17、ELECT * FROM TB_ transaction2 GO INSERT TB_ transaction2 VALUES(row3) GO,PRINT 第一、二个隐式事务所插入的数据： SELECT * FROM TB_ transaction2 GO ROLLBACK TRANSACTION GO SET IMPLICIT_TRANSACTION OFF GO PRINT 回滚第二个隐式事务后剩下的数据： SELECT * FROM TB_ transaction2 GO,7.2 存储过程,7.2.1 创建存储过程 7.2.2 执行存储过程 7.2.3 存储过程的返回值和状态信息 7.

18、2.4 查看和修改存储过程 7.2.5 重命名和删除存储过程 7.2.6 系统存储过程,存储过程的概念,SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。 存储过程是 SQL 语句和流程控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。,7.2.1 创建存储过程,在SQL Server中，可以使用三种方法创

19、建存储过程 ： 使用创建存储过程向导创建存储过程。 利用SQL Server 企业管理器创建存储过程。 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。,创建存储过程时，需要确定存储过程的三个组成部分：,所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。,1. 使用创建存储过程向导创建存储过程,在企业管理器中，选择工具菜单中的向导选项，选择“向导”（如图8-1所示），则出现欢迎使用创建存储过程向导对话框，如图8-2所示。根据图8-2-图8-7提示可完成创建存

20、储过程。,图8-1 新建SQL Server组,图8-2 选择创建存储过程向导,图8-3 欢迎使用创建存储过程向导对话框,图8-4 选择数据库对话框,图8-5 选择数据库对象对话框,图8-6 完成创建存储过程向导对话框,图8-7 编辑存储过程属性对话框,图8-8 编辑存储过程SQL对话框,2. 使用SQL Server 企业管理器创建存储过程,（）在SQL Server企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项，如图8-9所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程

21、”选项，如图8-10所示。均会出现创建存储过程对话框，如图8-11所示。 （）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限”按钮，如图8-12所示。,图8-9 选择新建存储过程对话框（1）,图8-10 选择新建存储过程对话框（2）,图8-11 新建存储过程对话框,图8-12 设置权限对话框,3. 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程,创建存储过程前，应该考虑下列几个事项： 不能将 CREATE PROCEDURE 语句与其它 SQL 语句

22、组合到单个批处理中。 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规则。 只能在当前数据库中创建存储过程。 一个存储过程的最大尺寸为128M。,用CREATE PROCEDURE创建存储过程的语法形式如下：,CREATE PROC EDURE 存储过程名 ；版本号 ( 参数名 数据类型 VARYING =default OUTPUT ，) WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL语句,1）：为新建立的存储过程名称，它必须遵守T

23、-SQL标识符命名规则，且在一个数据库中，存储过程名必须唯一。 2） ；版本号 :用来区分一组同名存储过程中的不同版本。同名存储过程只需调用一次DROP PROCEDURE即可全部删除。 2）：存储过程的参数有两种类型：输入参数和输出参数. Cursor类型只能用做输出参数，使用该数据类型时，必须同时使用VARYING和OUTPUT。 3）OUTPUT选项用来声明存储过程的返回参数，其值可以返回给调用它的EXECUTE语句，text、ntext和image类型参数可用做存储过程的返回参数。 4）=default用来为存储过程参数设置默认值。 5）：可以包含任意数量的T-SQL语句，它定义存储过

24、程所执行的操作。定义的文本不能超过128MB。,6）WITH RECOMPILE选项要求SQL Server不要在缓存中保存存储过程的执行计划，而在每次执行时都重新对它进行编译。 7）WITH ENCRYPTION选项要求对存储在syscomments系统表中的存储过程定义文本进行加密。 8）FOR REPLICATION选项说明该存储过程只能在复制过程中执行，但这种类型的存储过程不能在订阅服务器上执行。不能与WITH RECOMPILE同时使用。,比如，我们创建一个最简单的存储过程:,CREATE PROCedure getSTU_IS AS SELECT * FROM Student wh

25、ere sdept=IS,CREATE PROCedure getSTU ( department varchar(20) AS SELECT * FROM Student where sdept=department,若我们使用带参数的存储过程,则可用一个存储过程实现对不同系的学生数据的查询：,创建存储过程中的还可以含有流程控制等语句。存储过程可以嵌套，即在一个存储过程中可以调用另外一个存储过程。存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象等语句。这类语句如下：,.SET SHOWPLAN_TEXT .SET SHOWPLAN_ALL CREATE T

26、ABLE CREATE VIEW CREATE DEFAULT CREATE RULE CREATE TRIGGER CREATE PROCEDURE,执行存储过程的语句： EXECute = =|,7.2.2 执行存储过程,例如、调用上面的过程，查询数学系学生的信息的语句为： EXECUTE getSTU MA 或者：EXECUTE getSTU department=MA 或者：DECLARE dept CHAR(20) SELECT dept=MA EXECUTE getSTU dept,7.2.3 存储过程的返回值和状态信息,无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过

27、程的执行状态。 如果存储过程执行成功，返回的结果码是0； 如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。 我们在创建存储过程时，也可以定义自己的状态码和错误信息。比如：,/*查询工资大于salary的员工信息*/ CREATE PROCedure getemp (salary) AS IF salary=NULL RETURN 13 IF NOT EXISTS(SELECT*FROM职工 WHERE 工资salary) RETURN-103 SELECT*FROM职工WHERE工资salary,其调用语句可以是： DECLARE return_status int Ex

28、ecute return_status=getemp 1200 Go If return_status =13 print “必须提供一个数值作参数！” Else If return_status = -103 print “没有满足条件的记录！”,以下存储过程用来查询学生的平均成绩： CREATE PROCEDURE GET_AVG (Snumber char(8),savg tinyint OUTPUT) AS IF Snumber = NULL RETURN -1 IF NOT EXISTS (SELECT * FROM SC WHERE sno=Snumber) return 2 Se

29、lect savg=avg(grade) From sc Where sno=Snumber,其调用语句为： DECLARE Snum char(8),saverage tinying,re_status int SELECT Snum=95002 Execute re_status=GET_AVG Snum,saverage OUTPUT IF.,7.2.4 存储过程的自动执行,系统存储过程sp_procoption可以将存储过程设置为自动执行方式,使之在SQL Server每次启动时自动执行，其语法格式为： sp_procoption procName=procedure ,optionN

30、ame=option ,optionValue=value 其中： procedure为需要自动执行的存储过程名称。 option和value参数分别为待设置的选项名称及其状态。Option唯一取值为startup,它设置存储过程的自动执行状态。Value的取值为true和false、或on 和off。,注意： 1）设置为自动执行的存储过程不要返回任何结果集合。因为它们由系统自动执行，无法处理其返回结果。 2）SQL Server配置选项scan for stratup控制SQL Server每次启动时是否查找和执行系统中标识为自动执行的存储过程。只有打开该选项时，自动执行存储过程才能真正自动

31、执行。,7.2.5 查看和修改存储过程,存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。,使用企业管理器查看并修改用户创建的存储过程,在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码，同时还可以做修改，如下图所示。,用T-SQL修改存储过程,ALTER PROC EDURE 存储过程名 ；版

32、本号 ( 参数名 数据类型 VARYING =default OUTPUT ，) WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL语句,7.2.6 重命名和删除存储过程,1. 重命名存储过程 修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下： sp_rename 原存储过程名称，新存储过程名称 另外，通过企业管理器也可以修改存储过程的名称。,2. 删除存储过程,删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：

33、 drop procedure procedure ,n 当然，利用企业管理器也可以很方便地删除存储过程。 例如： drop procedure getemp,示例1,A. 使用带有复杂 SELECT 语句的存储过程：查询学生的考试成绩 CREATE PROCEDURE student_grade1 AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o 执行：EXEC student_grade1,示例2,B. 使用带有参数的存储过程：查询

34、某个学生某门课程的考试成绩 CREATE PROCEDURE student_grade2 student_name char(10), course_name char(20) AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE sname = student_name AND cname = course_name,执行示例2存储过程,1. 按参数位置传递值 EXEC student_grade2 刘晨, 数据库 2. 按

35、参数名传递值 EXEC Student_grade2 student_name = 刘晨, course_name=数据库,示例3,C. 使用 OUTPUT 参数 Create Procedure Proc1 var1 int, var2 int, var3 int output As Set var3=var1*var2 执行 Declare m int, m2 int,m3 int Set m1=5 Set m2=7 Execute Proc1 m1,m2,m3 output Select m3,7.2.7 系统存储过程,SQL Server提供了许多预定义的存储过程，这些存储过程存储在m

36、aster数据库中（以_sp为前缀），提供了有效的查询系统表的方法，以及许多系统管理功能 如：sp_help 、sp_helpdatabases,1sp_helpdb,作用：报告有关指定数据库或所有数据库的信息。 语法： sp_helpdb dbname= 数据库名 如果没有指定数据库名，则 sp_helpdb 报告 master.dbo.sysdatabases 中的所有数据库。 例1. 返回pubs数据库的信息 exec sp_helpdb pubs 例2. 返回有关所有数据库的信息 exec sp_helpdb,2. sp_databases,作用：列出驻留在 SQL Server 实例

37、中的数据库。 语法：sp_databases 返回系统中的全部数据库名、数据库大小（以 KB为单位）以及标记值（SQL Server的此项值为NULL）,3sp_tables,作用：返回当前环境下可查询对象的列表（任何可出现在 FROM 子句中的对象）。 语法：sp_tables table_type= “ 类型 ” 其中：类型包括TABLE（用户表）、SYSTEM TABLE（系统表）和 VIEW（视图） 例1：返回在当前环境中可查询的对象的列表 EXEC sp_tables 例2：返回当前数据库中的所有用户表 EXEC sp_tables table_type= TABLE,4sp_hel

38、pconstraint,作用：返回某个表的所有约束 语法：sp_helpconstraint 表名 示例：显示 authors 表的所有约束 EXEC sp_helpconstraint authors,5sp_spaceused,作用：显示当前数据库或数据库中的某个表所使用的磁盘空间以及保留的磁盘空间。 语法：sp_spaceused 表名 例1：察看当前数据库的空间使用情况 EXEC sp_spaceused 例2：查看pubs数据库的titles表的空间信息 EXEC sp_spaceused titles,7.2.7 用好存储过程,存储过程（Stored Procedure）是一组为了

39、完成特定功能的SQL 语句集，经编译后存储在数据库。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 在SQL Server 的系列版本中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。 系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。 用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。,存储过程的优点,当利用MS SQL Server 创建一个应用程序时，Trans

40、action-SQL 是一种主要的编程语言。若运用Transaction-SQL 来进行编程，有两种方法: 其一是，在本地存储Transaction- SQL 程序，并创建应用程序向SQL Server 发送命令来对结果进行处理。 其二是，可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中，并创建应用程序来调用存储过程，对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。,优点: (1） 存储过程允许标

41、准组件式编程,存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。,（2） 存储过程能够实现较快的执行速度,如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction- SQL 语句在每次运行时都要进行编译和优化

42、，因此速度相对要慢一些。,（3） 存储过程能够减少网络流量,对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的 Transaction-SQL 语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句，从而大大增加了网络流量，降低网络负载。,（4） 存储过程可被作为一种安全机制来充分利用,系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。,(5)自动完成需要预先执行的任务.,存储过程可以在系统启动时自动执行,而不必在系统启动后再进行

43、手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务 注意：存储过程虽然既有参数又有返回值，但是它与函数不同。存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字（如何执行存储过程见本章下一字）。,练习： 1.什么是存储过程？为什么要使用存储过程？ 2. 试为如下问题编写、建立存储过程，并定义合适的过程返回状态码和信息 1） 插入一个新的仓库，其信息由参数确定。 2） 给指定城市的所有仓库增加x m2面积。 3） 检索某职工的工资信息。 4） 检索在某城市工作的仓库职工号。,7.3 触发器及其用途,7

44、.3.1 基本概念 7.3.2 触发器的种类 7.3.3 创建触发器 7.3.4 触发器的原理 7.3.5 管理触发器,7.3.1 基本概念,触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。存储过程和触发器同是提高数据库服务器性能的有力工具。,触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。,触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其他许多不同的功能。,触发器的主要作用: （1）强化约束（Enforce Restriction） 触发器可以侦测数据库内的操作，从而

45、不允许数据库中未经许可的更新和变化。,（2）级联运行（Cascaded Operation） 触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器包含对另外一个表的数据操作（如删除、更新、插入），而该操作又导致该表上的触发器被触发。,（3）存储过程的调用(Stored Procedure Invocation) 为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS之外进行操作。,由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根

46、据这种差异执行一定的操作。此外一个表的同一类型（INSERT、UPDATE、DELETE）的多个触发器能够对同一种数据操作采取多种不同的操作。,1触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。,2触发器可以禁止或撤消违反参照完整性的修改。,3触发器可以强制比用CHECK约束定义更加复杂的限制。,触发器也是一个数据库对象。一个触发器和三部分内容有关：激活触发器的表、激活触发器的数据修改语句和触发器要采取的动作。,触发器可以用于数据参照完整性和以下一些场合：,7.3.2 触发器的种类,SQL Server 2000 支持

47、两种类型的触发器：AFTER触发器和INSTEAD OF触发器。 1、AFTER触发器 即为SQL Server 2000版本以前所介绍的触发器。该类型触发器要求只有执行完某一操作（INSERT、UPDATE、DELETE），并处理过所有约束后，触发器才被触发，且只能在表上定义。如果操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。 可以为针对表的同一操作定义多个触发器。AFTER触发器可以指定哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder来完成此任务。,2、INSTEAD OF触发器 该类触发器表示并不执行其所定义的操作（INSERT、

48、UPDATE、DELETE），而仅是执行触发器本身。既可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。,7.3.3 创建触发器,SQL Server中可以用SQL Server的 企业管理器和Transaction-SQL来创建触发器。 在创建触发器以前必须考虑到以下几个方面： （1）CREATE TRIGGER语句必须是批处理的第一个语句。 （2）触发器是数据库对象，所以其命名必须符合命名规则。 （3）虽然触发器可以参照视图或临时表，但不能在视图或临时表上创建触发器(Instead of)，只能在基表或

49、在创建视图的表上创建触发器。 （4）一个触发器只能对应一个表，这是由触发器的机制决定的。,（5）在触发器定义中，所有建立和更改数据库以及数据库对象的语句、所有的drop语句都不允许在触发器中使用。 （6）在触发器定义中，可使用IF UPDATE子句来测试INSERT、UPDATE语句中是否对指定字段有影响。如果将一个值赋给指定字段或更改了指定的字段，则这个子句为真。 （7）通常不要在触发器中返回任何结果，因此不要在触发器定义中使用select语句或变量赋值语句。 创建一个触发器时，必须指定触发器的名字、在哪一个表上定义触发器、激活触发器的修改语句，如INSERT、DELETE、UPDATE，当

50、然两个或三个不同的修改语句也可以触发同一个触发器，如INSERT和UPDATE语句能激活同一个触发器。,1用CREATE TRIGGER命令创建触发器 可用CREATE TRIGGER命令创建触发器，其语法规则如下 CREATE TRIGGER trigger_name ONtable|view FOR|AFTER|INSTEAD OF DELETE ,INSERT , UPDATEwith append AS SQL 语句 ,从以上语句可以看出，一个表最多可以有三类触发器：插入（INSERT）触发器、更新（UPDATE）触发器、删除（DELETE）触发器。一个触发器只能应用到一个表上，但一个

51、触发器可以包含很多动作，可以执行很多功能。,（1）trigger_name是用户要创建的触发器的名字。触发器的名字必须符合SQL Server的命名规则，且其名字在当前数据库中必须是惟的。 （2）tablel|view是与用户创建的触发器相关联的表的名字或视图的名称，并且此表或视图必须已经存在。 （3）DELETE , INSERT , UPDATE关键字用来指明哪种数据操作将激活触发器。至少要指明其中的一个选项，在触发器的定义中，三者的顺序不受限制，各选项要用逗号隔开。 （4）WITH APPEND表明增加另外一个已存在的触发器。只有在兼容性水平（指某一数据库行为与以前版本的SQL Serv

52、er兼容程度）不大于65时才使用该选项。 （5）AS是触发器将要执行的动作。 （6）sql语句是包含在触发器中的条件语句或处理语句。,例 创建一个触发器，当向s表中插入一条记录时，自动显示s表中的记录。 CREATE TRIGGER Change_Display On s FOR INSERT,UPDATE,DELETE AS SELECT * FROM student,该触发器建立完毕后，当执行如下操作时，将会显示s数据表中的全部记录。 EXECUTE InsertRecordDefa sno=S11，sn=张建峰，age=17,sex=男,2用Enterprise Manger创建触发器的

53、步骤如下：,（1）启动Enterprise Manager,登录到要使用的服务器。 （2）在Enterprise Manager的左窗格中，展开要创建触发器的数据库文件夹， 单击“表”文件夹，此时在右窗格中显示该数据库的所有表。,（3）在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，此时会出现 “触发器属性”对话框。 （4）在“名称”下拉框中选择“”，“文本”编辑框中输入触发器的文本命令。 （5）单击“检查语法”按钮，检查语句是否正确。 （6）单击“应用”按钮，在“名称”下拉列框中会显示新创建的触发器名字。,属性

54、对话框,7.3.4 触发器的原理,从以上的介绍中我们可以看出触发器具有强大的功能，那么SQL Server是如何用触发器来完成这些任务呢？下面我们将对其工作原理及实现做详细介绍。 每个触发器有两个特殊的表：插入表和删除表，分别为inserted和deleted。有以下几个特点： 1）这两个表是逻辑表，并且这两个表是由系统管理的，存储在内存中因此不允许用户直接对其修改。 2）这两个表的结构总是与被该触发器作用的表有相同的表结构。,3）这两个表是动态驻留在内存中的，当触发器工作完成，这两个表也被删除。这两个表主要保存因用户操作而被影响到的原数据值或新数据值。 4）另外，这两个表是只读的，且只在触发

55、器内部可读。例如在触发器内可用如下语句查看DELETED表中的信息： select*from deleted,1插入表的功能（inserted）,对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入（INSERT）操作，那么对该表插入的所有行来说，都有一个相应的副本级存放到插入表（inserted）中，即插入表就是用来存储原插入的内容。,2删除表的功能(deleted),对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除（DELETE）操作，则将所有的被删除的行存放至删除表（deleted）中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表中得以还

56、原。,需要强调的是，更新（UPDATE）操作包括两个部分，即先将旧的内容删除，然后将新值插入。因此，对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了旧值，然后在插入表中存放新值。,由于触发器仅当被定义的操作被执行时才被激活，即触发器仅在执行插入、删除和更新操作时才被执行。每条SQL语句仅能激活触发器一次，可能存在一条语句影响多条记录的情况。在这种情况下可以使用系统变量rowcount的值，该变量存储了最新的一条SQL语句执行后所影响的记录数。,3插入视图和删除视图,当在定义了触发器的表上发生修改操作时会自动派生出两个视图，一个是插入视图，一个是删除视图。当在表上发生插入操

57、作时，新插入的行将出现在inserted表中形成插入视图；当在表上发生删除操作时，被删除的旧行将出现deleted表中，形成删除视图。而更新的实现过程是先删除旧行，然后再插入新行。,7.3.5 管理触发器,如果要显示作用于表上的触发器究竟对表有哪些操作，必须查看触发器信息。在SQL Server中，有多种方法查看触发器信息。接下来，我们将介绍两种常用的方法，即通过SQL Server 的管理工具Enterprise Manager 以及系统存储过程sp_help、sp_helptext和sp_depends来查看触发器信息。,1使用Enterprise Manager显示触发器信息 使用Ent

58、erprise Manager显示触发器信息，步骤如下。,（1）启动Enterprise Manager,登录到要使用的服务器。 （2）在Enterprise Manager的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹，此时在右窗格中显示该数据库的所有表。 （3）在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，出现“触发器属性”对话框。 （4）在“名称”下拉列表中选择所要查看的触发器的名称，在“文本”编辑框中显示出该触发器的文本命令。,（1）sp_help 通过该系统过程，可以了解触发器的一般信息，如触发器的名字、属性、类型、创建时间。使用sp_help系统过程的命令格式为： sp_help 触发器名字,例 查看我们已经建立的change_display触发器。 sp_help change_display,2使用系统存储过程查看触发器 系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。下面我们将分别对其进行介绍。,(2) sp_helptext 通过sp_helptext能够查看触发器的正文信息，其语法格式为： sp_helptext 触发