整理的关于SQL Server中事务,触发器和存储过程的介绍以及实际例子.docx

事务事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，SQL Server能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。 (2):事务通常是以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。 COMMIT表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。 ROLLBACK表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有以完成的操作全部撤消，滚回到事务开始的状态。 (3):事务运行的三种模式: A:自动提交事务 每条单独的语句都是一个事务。每个语句后都隐含一个COMMIT。 B:显式事务 以BEGIN TRANSACTION显式开始，以COMMIT或ROLLBACK显式结束。 C:隐性事务 在前一个事务完成时，新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK显式结束。 (4):事务的特性(ACID特性) A:原子性(Atomicity) 事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。 B:一致性(Consistency) 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 C:隔离性(Isolation) 一个事务的执行不能被其他事务干扰。 D:持续性/永久性(Durability) 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。 注:事务是恢复和并发控制的基本单位。数据库事务的ACID属性原子性（atomic）（atomicity) 事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。 一致性（consistent）(consistency) 事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。 隔离性（insulation）(isolation) 由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。防止数据丢失 持久性（Duration）(durability） 事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。SQL 事务处理模型事务有三种模型： 1隐式事务是指每一条数据操作语句都自动地成为一个事物，每个事务都有显式的开始和结束标记。 2显式事务是指有显式的开始和结束标记的事物，事务的开始是隐式的，事务的结束有明确的标记。 3自动事务是系统自动默认的，开始和结束不用标记DBMS并发控制机制的责任： 对并发操作进行正确调度，保证事务的隔离性更一般，确保数据库的一致性。 如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。由于并发操作带来的数据不一致性包括：丢失数据修改、读”脏”数据（脏读）、不可重复读、产生幽灵数据。 （1）丢失数据修改 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。如上例。 再例如，两个编辑人员制作了同一文档的电子复本。每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文档。最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。 （2）读“脏”数据（脏读） 读“脏”数据是指事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被除撤消，而此时T1把已修改过的数据又恢复原值，T2读到的数据与数据库的数据不一致，则T2读到的数据就为“脏”数据，即不正确的数据。 例如：一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。 （ 3）不可重复读 指事务T1读取数据后，事务T2执行更新操作，使T1无法读取前一次结果。不可重复读包括三种情况： 事务T1读取某一数据后，T2对其做了修改，当T1再次读该数据后，得到与前一不同的值。 （4）产生幽灵数据 按一定条件从数据库中读取了某些记录后，T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录消失 T1按一定条件从数据库中读取某些数据记录后，T2插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。触发器 触发器 1、触发器的 本质： 触发器是一种特殊的存储过程，它不能被显式地调用,而是在往表中插入记录、更改记录或者删除记录时，当事件发生时，才被自动地激活。 2、这样做带来的 功能： 触发器可以用来对表实施复杂的完整性约束，保持数据的一致性，当触发器所保护的数据发生改变时，触发器会自动被激活，响应同时执行一定的操作（对其它相关表的操作），从而保证对数据的不完整性约束或不正确的修改。 触发器可以查询其它表，同时也可以执行复杂的T-SQL语句。触发器和引发触发器执行的命令被当作一次事务处理，因此就具备了事务的所有特征。 注意： 事务具备什么特征？在触发器中的作用？ 如果发现引起触发器执行的T-SQL语句执行了一个非法操作，比如关于其它表的相关性操作，发现数据丢失或需调用的数据不存在，那么就回滚到该事件执行前的SQL SERVER数据库状态。 3、触发器的作用： 触发器可以对数据库进行级联修改，这一点刚才已经说过了。 需要说明的是： 触发器和约束的关系和区别 (1)一般来说，使用约束比使用触发器效率更高。 (2)同时，触发器可以完成比CHECK约束更复杂的限制。 说明： 2.1 与CHECK约束不同，在触发器中可以引用其它的表。 2.2 触发器可以发现改变前后表中数据的不一致，并根据这些不同来进行相应 的操作。 2.3 对于一个表不同的操作（INSERT、UPDATE、DELETE）可以采用不同的触发器，即使是对相同的语句也可以调用不同的触发器来完成不同的操作。 举例1：在签订一份订单时，货物的库存量应减少。 问？这应用了触发器的什么特征？CHECK约束能解决吗？ 举例2：正在进行整理的货物不能下订单。 问？这应用了触发器的什么特征？CHECK约束能解决吗？ 4、对触发器3种操作的分析： 在SQL SERVER为每个触发器都创建了两个专用表：inserted表和deleted表。 这是两个逻辑表，由系统来维护，在触发执行时存在，在触发结束时消失。 这样有什么用途？ 带着问题看，具体操作步骤和过程： (1）deleted表存放由于执行delete或update语句而要从表中删除的所有行。 在执行delete或update操作时，被删除的行从激活触发器的表中被移动（move）到deleted 表，这两个表不会有共同的行。 (2）inserted表存放由于执行insert或update语句而要向表中插入的所有行。 在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中， inserted表的内容是激活触发器的表中新行的拷贝。 说明：update事务可以看作是先执行一个delete操作，再执行一个insert操作，旧的行首先被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。 11.1.3 instead of 和 after 触发器 主要包括定义和应用范围条件，操作执行时机； 11.2 创建触发器 1、考虑为什么要设计出发器，为解决什么问题而设计？ 2、应制定的内容：为什么，大家思考一下？不知道，看下面的例子，全看完！ T-SQL语句创建触发器 语法结构： 作业： (要求：在northwind表中建立2个表：cust_test和order_test) 作业1： 在cust_test表中建立删除触发器，实现上述2表的级联删除。作业2： 在order_test表建立insert触发器，当向order_test表插入一行，如果cust_test表中对应 记录status值为1，说明处于准备状态不能写入该数据。图形化操作触发器 11.3 查看触发器情况 图形化操作结合T-SQL命令 (1)sp_helptrigger 触发器名 查看触发器的名称，拥有者和五个布尔值 (2)sp_helptext 触发器名 查看文本信息 (3)设置某一触发器的无效和重新有效 (4）删除触发器 作业3： 在order_test表上建立一个插入触发器，在添加一个订单时，减少cust_test表的相应货物的记录的库存量。 答案3： 作业4： 在order_test表上建立一个插入触发器，规定订单日期(Odate)不能手工修改。作业5： 要求订购的物品一定要在仓库中有的，并且数量足够。 例6： 在order_test表上建立一个插入触发器，同时插入多行数据时，要求订购的物品一定要在仓库中有的。 Transact-SQL 参考 SET ROWCOUNT 使 Microsoft? SQL Server? 在返回指定的行数之后停止处理查询。 语法 SET ROWCOUNT number | number_var 参数 number | number_var是在停止给定查询之前要处理的行数（整数）。 注释 建议将当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句重新编写为使用 TOP 语法。有关更多信息，请参见 DELETE、INSERT 或 UPDATE。 对于在远程表和本地及远程分区视图上执行的 INSERT、UPDATE 和 DELETE 语句，忽略 SET ROWCOUNT 选项设置。 若要关闭该选项（以便返回所有的行），请将 SET ROWCOUNT 指定为 0。 说明 设置 SET ROWCOUNT 选项将使大多数 Transact-SQL 语句在已受指定数目的行影响后停止处理。这包括触发器和 INSERT、UPDATE 及 DELETE 等数据修改语句。ROWCOUNT 选项对动态游标无效，但限制键集的行集和不感知游标。使用该选项时应谨慎，它主要与 SELECT 语句一起使用。 如果行数的值较小，则 SET ROWCOUNT 替代 SELECT 语句 TOP 关键字。 SET ROWCOUNT 的设置是在执行或运行时设置，而不是在分析时设置。 权限 SET ROWCOUNT 权限默认授予所有用户。 示例 SET ROWCOUNT 在指定的行数后停止处理。在下例中，注意有 x 行满足预付款少于或等于 $5,000 的条件；但是，从更新所返回的行数中可以看出并非所有的行都得到处理。ROWCOUNT 影响所有的 Transact-SQL 语句。 sql触发器练习2012-03-30 10:36/*-第1题-创建名为student的数据库。*/create database student/*-第2题-向student数据库里创建四张表，表名分别为：stu（学生信息表）、sc（选课表）、teacher（教师信息表）、courseinfo（课程信息表）*/use studentgocreatetable stu( sno char(10) notnull primary key, sname char(8) notnull, sex char(2) null, age int null, dept varchar(20) null)createtable sc( sno char(10) notnull , score float null, courseid char(5) null, tno char(10)createtable teacher( tno char(10) notnull primary key, tn char(8) notnull, prof varchar(20) , sal float , dept varchar(20), courseid char(10)createtable courseinfo( courseid varchar(10) notnull, cname varchar(20), book varchar(30), ctest datetime, dept varchar(10)createtable bookinfo( bookid char(10), bn varchar(30), pubic varchar(30), book_price money, quantity int)/*-第3题-向stu、sc、teacher、courseinfo表里插入数据*/insertinto stu(sno,sname,sex,age,dept)select1001,宋江,男,25,计算机系unionselect3002,张明,男,23,生物系unionselect1003,李小鹏,男,26,计算机系unionselect1004,郑冬,女,25,计算机系unionselect4005,李小红,女,27,工商管理unionselect5006,赵紫月,女,24,外语系insertinto sc(sno,score,courseid,tno)select1001, 87, C1,3102unionselect1001, 77, C2,4105unionselect1001, 63, C3,3108unionselect1001, 56, C3,3108unionselect3002, 78, C3,3108unionselect3002, 78, C4,5102unionselect1003, 89, C1,9103unionselect1004, 56,C2,3106unionselect4005, 87, C4,5102unionselect5006, null, C1,7101insertinto teacher(tno,tn,prof,sal,dept,courseid)select3102,李明,初级,2500,计算机系,C1unionselect3108,黄小明,初级,4000,生物系,C3unionselect4105,张小红,中级,3500,工商管理,C2unionselect5102,宋力月,高级,3500,物理系,C4unionselect3106,赵明阳,初级,1500,地理系,C2unionselect7108,张丽,高级,3500,生物系,C3unionselect9103,王彬,高级,3500,计算机系,C1unionselect7101,王力号,初级,1800,生物系,C1insertinto courseinfo(courseid,cname,book ,ctest,dept)selectC1,计算机基础,computer,2009-4-6,计算机系unionselectC2,工商管理基础,the logic circuit,2009-7-16,工商管理unionselectC3,生物科学,the signal and system,2010-3-6,生物系unionselectC4,大学物理,sigal processing,2009-4-26,物理系unionselectC5,数据库原理,sql techniques,2010-2-6,计算机系insertinto bookinfoselectb1231,Image Processing,人民出版社,34.56,8 unionselectb1232,Signal Processing,清华出版社,51.75,10 unionselectb1233,Digital Signal Processing,邮电出版社,48.5,11 unionselectb1234,The Logic Circuit,北大出版社,49.2,40 unionselectb1235,SQL Techniques,邮电出版社,65.4,20/*-54- insert触发器的使用为stu表创建触发器s_insert,当向stu表中插入数据时,要求学号必须以97开头，否则取消插入操作。*/create trigger s_insert1on stuforinsertasdeclare s_no varchar(4)select s_no=sno from insertedif(left(s_no,2)!=97)beginrollbacktransactionraiserror(输入的学号： %s不是97级的学生，请确认后重新录入！,16,1,s_no)endinsertinto stu values(9804,王永,男,18,计算机系)/*-54- delete触发器的使用为stu表创建Delete触发器s_delete，当一次删除的记录大于一行时，取消删除操作。*/create trigger s_delete1on stufordeleteasdeclare rowcount intselect rowcount =(selectcount(*)from deleted) /*rowcount*/if rowcount1 begin rollbacktransaction raiserror(当前要删除的记录数为%d，一次只允许删除一行记录!,16,1,rowcount) endgodelete stu where dept=计算机系/*-54- update触发器的使用为courseinfo表创建update触发器c_update,当courseinfo表的couseid和cname列（第1、2列）被更新时，触发器给出提示信息，该两列不能被更新，并回滚事务，当其余的列（3、4、5列）被更新时，触发器将更新前后的数据写入c_upinfo表中。（提示：数据表的列可以通过COLUMNS_UPDATED（）这个函数获得，它返回包含在update语句中字段的二进制掩码1111111 。例如columns_updated()&30 可以获得数据表的第1，2列）,columns_updated()&280可以获得第3，4，5列 */createtable c_upinfo( oper_time smalldatetime, date_type char(10), courseid varchar(10), cname varchar(20), book varchar(30), ctest datetime, dept varchar(10)create trigger c_updateon courseinfoforupdateasif(columns_updated()&3)0 begin rollbacktransaction raiserror(courseinfo表的courseid列和cname列中的数据不允许被更新!,16,1) endif(columns_updated()&28)0 begin insertinto c_upinfo(oper_time,date_type,courseid,cname,book,ctest,dept) select getdate(),older,del.courseid,ame,del.book,del.ctest,del.dept from deleted as del insertinto c_upinfo(oper_time,date_type,courseid,cname,book,ctest,dept) select getdate(),New,ins.courseid,ame,ins.book,ins.ctest,ins.dept from inserted as ins endupdate courseinfoset cname=线性代数where courseid=C3select * from c_upinfo where courseid=C3update courseinfoset book=代数基础,ctest=2009-9-20where courseid=C3存储过程：存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 在SQL Server 的系列版本中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。通过系统存储过程，MS SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。 存储过程的优点 当利用MS SQL Server 创建一个应用程序时，Transaction-SQL 是一种主要的编程语言。若运用Transaction-SQL 来进行编程，有两种方法。其一是，在本地存储Transaction- SQL 程序，并创建应用程序向SQL Server 发送命令来对结果进行处理。其二是，可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中，并创建应用程序来调用存储过程，对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。 我们通常更偏爱于使用第二种方法，即在SQL Server 中使用存储过程而不是在客户计算机上调用Transaction-SQL 编写的一段程序，原因在于存储过程具有以下优点： （1） 存储过程允许标准组件式编程 存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。 （2） 存储过程能够实现较快的执行速度 如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction- SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。 （3） 存储过程能够减少网络流量 对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的 Transaction-SQL 语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句，从而大大增加了网络流量，降低网络负载。 （4） 存储过程可被作为一种安全机制来充分利用 系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。（我们将在14 章“SQLServer 的用户和安全性管理”中对存储过程的这一应用作更为清晰的介绍） 注意：存储过程虽然既有参数又有返回值，但是它与函数不同。存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字。 2.创建存储过程P_1，功能是查询性别为男的学生的学号，姓名，性别。create proc p_1( xb char(2)as select 学号,姓名,性别from 学生where 性别=xbexecute p_1 男2创建存储过程P_2，功能是查询指定学生的学号，姓名，性别和班级，姓名由参数传递。create proc p_2(xm char(8)asselect 学号,姓名,性别from 学生where 姓名=xmexecute p_2 张三3创建存储过程P_3，功能是查询某门课程的学生成绩，显示学号，姓名，课程名，成绩，并将成绩转换为等级分制。课程名由参数传递。create proc p_3(kcm char(20),cj int output)asselect 学生.学号,姓名,课程名,casewhen 成绩=60 and 成绩=70 and 成绩=80 and 成绩=90