第13讲 事务与触发器.ppt

回顾,存储过程(Procedure)的定义输入参数参数名输出参数参数名output执行状态值，0表示成功，-99-1表示系统发现的执行错误，其余的值表示自定义的错误。存储过程的执行EXECUTEPROC过程名参数output,2020/6/9,2,提纲,重点事务的四个特点：原子性，一致性，隔离性，持久性触发器的实现方法难点：原子性，一致性，隔离性，持久性的理解AFTER触发与INSTEADOF触发的区别,思考：余额表(帐号，余额)“001”账户向“002”账户转X元，手续费1%，需要完成如下2项数据操作：操作1：001账户减X*1.01,操作2：002账户加X.完成以下存储过程proc_转帐，用于实现转帐业务的数据操作功能。CREATEPROCproc_转帐outIdchar(20),inIdchar(20),amoutintAS-SQL体,数据库原理与应用,第13讲事务与触发器,1.事务,关键词:BEGINTRAN,COMMITTRAN，ROLLBACKTRAN,10.5事务处理,事务(Transaction)是一个逻辑工作单元。单元中的所有操作要么全部成功，要么全部取消。事务的必要性(1)如果一个业务需要多个数据操作，而只有其中的部分操作成功，则数据的一致性将会被破坏。例如：银行转帐，至少需要两项操作：甲账户扣除，乙账户增加，不允许只有一项操作成功，而另一项失败。(2)导致错误的原因可分为两类：一是可以预见的逻辑错误，比如账户不存在，余额不足等。二是不可预见的错误，比如DBMS停止服务、操作延时导致中断等等。(3)把一个业务所需的多个操作放入一个事务，可以保证所有操作作为一个单元来执行。,事务执行原理事务作为一个整体提交，在提交之前，所有的操作被临时存放起来，并没有保存到数据库中。任何一个操作错误，数据将回退到事务开始时的状态，临时更改被全部取消。事务提交后，数据更新被保存到数据库。T-SQL中管理事务BEGINTRAN事务名-开始事务操作1if错误ROLLBACK-回退操作2if错误ROLLBACK-回退操作3if错误ROLLBACK-回退COMMITTRAN事务名-提交事务,事物的属性(ACID)原子性(Atomicity)事务中的操作要么全成功，要么全回退，一致性(Consistency)事务执行前后数据在逻辑上是一致的，隔离性(Isolation)甲事务执行时，乙事务不能得到甲事务中间的状态。即事务串行化，乙事务得到的数据要么是甲事务执行前的状态，要么是执行后的状态。持久性(Durability)事务执行完后，对数据进行的更改将被保存。事务的3种模式1.显式事务：用BEGINTRAN开始事务，用COMMIT提交事务，用ROLLBACK回退事务。(重点)2.自动提交事务：是数据引擎的缺省模式，每个单独的语句在完成后被提交，失败后被回滚。3.隐式事务：在当前事务完成提交或回退后，新事务自动启动。隐性事务不需要使用BEGINTRAN语句标识事务开始，但需要COMMIT或ROLLBACK语句来提交或回退事务。,10.5事务处理,使用事务的经验：在存储过程中管理事务，控制事务的启动、提交和回滚。事务中的所有操作包含在：BEGINTRAN和COMMITTRAN之间。自定义存储过程状态值，以反应事务是否有效执行。(2)对事务中的每个操作进行跟踪，一般用到两个系统变量：error和rowcount。用系统变量error跟踪执行状态，当error0时，说明操作发生了执行错误；用系统变量rowcount跟踪受影响的行数，如果rowcount与预期不符，说明操作发生了业务错误。发生错误时，用ROLLBACKTRAN回退事务。,示例：余额表(帐号，余额)“001”账户向“002”账户转X元，手续费1%，需要完成如下2项数据操作：操作1：001账户减X*1.01,操作2：002账户加X.可能发生的错误如下：可控的错误：没有001账号，没有002账号，001账号余额不足。不可控的错误：操作1失败，操作2失败。（事务定义见代码）,使用事务的不好的做法：有些程序员定义事务时，只用一对BEGINTRAN和COMMITTRAN，在这种情况下，如果部分操作发生错误，其余操作可能成功，将导致数据的不一致性。在开始事务之前使用SETXACT_ABORTON（默认情况下为OFF)，可以保证事务中任一操作发生错误时，所有操作全部回退，但这种方法只能检查执行错误，不能检查业务错误。例如：SETXACT_AbortONBEGINTRANUPDATE余额表SET余额=余额-amount*1.01WHERE账号=outIdUPDATE余额表SET余额=余额+amountWHERE账号=inIdprint转账成功COMMITTRAN,案例,10.5.2事务管理,事务嵌套事务可以嵌套执行。即BEGINTRAN和COMMITTRAN之间可以含有BEGINTRAN和COMMITTRAN。最外层的事务提交时，内存事务的操作才被接受，如果外层事务被回退，内层事务也将被回退。注：trancount计算目前代码所在的嵌套是第几层嵌套。注：Tsql语句里每遇到一个“begintran”语句时，trancount就会自加一；每遇到一个“committran”时，trancount就会自减一；遇到“rollbacktran”时trancount自动归零。,案例,10.5.2事务管理,事务部分回退在事务中设定保存点(SavePoint)，可指定回退到保存点，保存点之前的操作被视为有效。例：如果在完成转账之后，显示账户余额。可在转账业务完成时，进行一次事务保存。BEGINTRANtran_转账并显示余额-转账操作SAVETRANAFTER_转账SELECT*FROM余额表WHERE账号=outIdiferror0ROLLBACKTRANAFTER_转账COMMITTRANTRANtran_转账并显示余额,课堂小练：在BookStore数据库中，存在三个表：图书表(图书编号，书名，状态)借阅表(图书编号，借书证号，借出日期)业务记录表(业务编号，借书证号，图书编号，业务日期，业务类型)当借书业务发生时，需要对三个数据操作，(1)借阅表增加一行（T001,1110033,2012-2-3）(2)图书表中某行的状态改为借出，(3)业务记录表增加一行。(Y01,1110033,T001,2012-2-3,QQ)请编写事务，实现借书逻辑。,2.触发器,关键词：INSTEADOF触发器,AFTER触发器，INSERTED表，DELETED表,思考：考虑思考中编写的存储过程，当用户调用这个存储过程时，可以保证数据库的逻辑一致性，但是如果用户直接在DBMS中对“借阅表”表进行增、删、改操作呢？解决方法：在“借阅表”上定义触发器。,触发器(TRIGGER)是一种特殊类型的存储过程。触发器是一种存储过程，与一般的存储过程不同的是：触发器是通过事件触发的，而普通的存储过程需要调用才执行。常见的触发器由UPDATE、INSERT、DELETE操作触发，这种触发器的称为DML触发器。也有DDL触发器，不常使用。触发器用于保证数据完整性，凡是约束能提供的保证，触发器都可以提供，约束无法保证的完整性，触发器也能提供。,触发器的两种类型INSTEADOF触发器对数据增删改时，只执行触发器中的操作，而不执行（UPDATE、INSERT、DELETE）操作。AFTER触发器数据增删改操作被临时存放，然后与触发器中的操作一起提交，也可以检查错误从而回退。相当于增删改操作与触发器中的操作构成一个事务。触发器的执行时间当一个表同时存在INSTEADOF触发器、AFTER触发器和约束时，对该表中的数据进行增删改，则三种数据库对象依次工作，执行次序是：执行INSTEADOF触发器-验证约束-执行AFTER触发器。,10.4.1创建触发器,DML触发器基于Deleted表和Inserted表来工作。Deleted表和Inserted表是两个临时表。执行INSERT操作，被添加的行存储在Inserted表中。执行DELETE操作，被删除的行会存储在Deleted表。执行UPDATE操作，被修改的行存储在Deleted表中，修改后的数据存储在Inserted表中。,使用触发器的建议尽量使用约束，仅仅在约束不能满足要求的时候使用触发器。尽量使用存储过程为外部用户提供调用接口，触发器仅用来控制数据库管理员在DBMS上的更新操作。要注意，由于存储过程中的数据更新也会触发TRIGGER，因此，要留意避免重复的操作。例如：用户调用存储过程，存储过程进行了三个关联操作，其中的某个操作上触发器也要求进行关联操作，这样就产生了重复。,课堂练习：请回答触发情况-select*from学生表-select*from成绩表CREATETRIGGERTR_学生表ON学生表INSTEADOFINSERTASIF(SELECTCount(*)FROM学生表WHERE性别=男)5select*frominsertedCREATETRIGGERTR_学生表ON学生表forINSERT,DELETE,UPDATEASBEGINUPDATE学生表SET年龄=年龄+10WHERE学号=001-UPDATE学生表SET年龄=年龄+10WHERE学号=100iferror0rollbackEND注意：触发器中的rollback将使INSERT,DELETE,UPDATE等操作回退insert学生表values(022,刘二,男,19,05002,60.0,从化,广州大学)UPDATE学生表set学分=74,总结,事务是一个操作序列，它包含了一组数据库操作命令，所有的命令作为一个整体一起向系统提交或撤销操作请求，即要么都执行，要么都不执行。通常在程序中用BEGINTRANSACTION语句来标识一个事务的开始，用COMMITTRANSACTION语句标识事务结束。事务回滚是指当事务中的某一语句执行失败时，将对数据库的操作恢复到事务执行前或某个指定位置，事务回滚用ROLLBACKTRANSACTION语句。触发器就其本质而言是一种特殊的存储过程，有3种类型：插入触发器、更新触发器和删除触发器。当