第13讲 事务与触发器

1、回顾回顾存储过程存储过程(Procedure)的定义的定义输入参数输入参数 参数名参数名输出参数输出参数 参数名参数名 output执行状态值，执行状态值，0表示成功，表示成功，-99-1表示系统发现的执行错表示系统发现的执行错误，其余的值表示自定义的错误。误，其余的值表示自定义的错误。存储过程的执行存储过程的执行EXECUTE PROC 过程名过程名 参数参数output 2022-3-72提纲提纲重点重点事务的四个特点：事务的四个特点：原子性，一致性，隔离性，持久性原子性，一致性，隔离性，持久性触发器的实现方法触发器的实现方法难点：难点：原子性，一致性，隔离性，持久性原子性，一致性，隔离性

2、，持久性的理解的理解AFTER触发与触发与INSTEAD OF触发的区别触发的区别 思考：思考：余额表余额表(帐号，余额帐号，余额)“001”账户向账户向“002”账户转账户转X元，手续费元，手续费1%，需要完成如，需要完成如下下2项数据操作：项数据操作：操作操作1：001账户减账户减X*1.01,操作操作2：002账户加账户加X.完成以下完成以下存储过程存储过程proc_转帐转帐，用于实现转帐业务的数据，用于实现转帐业务的数据操作功能。操作功能。 CREATE PROC proc_转帐转帐 outId char(20), inId char(20), amout int AS -SQL 体体

3、数据库原理与应用数据库原理与应用第第13讲讲 事务与触发器事务与触发器1. 事务事务关键词关键词: BEGIN TRAN, COMMIT TRAN，ROLLBACK TRAN10.5 10.5 事务处理事务处理事务事务(Transaction)是一个逻辑工作单元。单元中的所有操作要么全部成功，是一个逻辑工作单元。单元中的所有操作要么全部成功，要么全部取消。要么全部取消。事务的必要性事务的必要性(1) 如果一个业务需要多个数据操作，而只有其中的部分如果一个业务需要多个数据操作，而只有其中的部分操作成功，则数据的操作成功，则数据的一致性将一致性将会被破坏。会被破坏。例如例如：银行：银行转帐，至少需

4、要两项操作：甲账户扣除，乙账户增加，转帐，至少需要两项操作：甲账户扣除，乙账户增加，不允许只有一项操作成功，而另一项失败。不允许只有一项操作成功，而另一项失败。(2)导致错误的原因可分为两类：一是可以预见的逻辑错导致错误的原因可分为两类：一是可以预见的逻辑错误，比如账户不存在，余额不足等。二是不可预见的误，比如账户不存在，余额不足等。二是不可预见的错误，比如错误，比如DBMS停止服务、操作延时导致中断等等。停止服务、操作延时导致中断等等。(3) 把一个业务所需的把一个业务所需的多个操作放入一个事务多个操作放入一个事务，可以保证，可以保证所有操作作为一个单元来执行。所有操作作为一个单元来执行。事

5、务执行原理事务执行原理p 事务作为一个整体提交，在提交之前，所有的事务作为一个整体提交，在提交之前，所有的操作被临时存放起操作被临时存放起来来，并没有保存到数据库中。，并没有保存到数据库中。p 任何一个操作错误，数据将回退到事务开始时的状态，临时更改任何一个操作错误，数据将回退到事务开始时的状态，临时更改被全部取消。被全部取消。p 事务提交后，数据更新被保存到数据库。事务提交后，数据更新被保存到数据库。T-SQL中管理事务中管理事务 BEGIN TRAN 事务名事务名-开始事务开始事务操作操作1if 错误错误 ROLLBACK-回退回退操作操作2 if 错误错误 ROLLBACK-回退回退 操

6、作操作3 if 错误错误 ROLLBACK-回退回退 COMMIT TRAN 事务名事务名-提交事务提交事务事物的属性事物的属性(ACID)原子性原子性(Atomicity)事务中的操作要么全成功，要么全回事务中的操作要么全成功，要么全回退，退，一致性一致性(Consistency)事务执行前后数据在逻辑上是一致事务执行前后数据在逻辑上是一致的，的，隔离性隔离性(Isolation)甲事务执行时，乙事务不能得到甲事务甲事务执行时，乙事务不能得到甲事务中间的状态。即事务串行化，乙事务得到的数据要么是甲中间的状态。即事务串行化，乙事务得到的数据要么是甲事务执行前的状态，要么是执行后的状态。事务执行

7、前的状态，要么是执行后的状态。持久性持久性(Durability)事务执行完后，对数据进行的更改将事务执行完后，对数据进行的更改将被保存。被保存。事务的事务的3种模式种模式1.显式事务：用显式事务：用BEGIN TRAN开始事务，用开始事务，用COMMIT 提交提交事务，用事务，用ROLLBACK回退事务。回退事务。 (重点重点)2.自动提交事务：是数据引擎的缺省模式，每个单独的语句自动提交事务：是数据引擎的缺省模式，每个单独的语句在完成后被提交，失败后被回滚。在完成后被提交，失败后被回滚。 3.隐式事务：在当前事务完成提交或回退后，新事务自动启隐式事务：在当前事务完成提交或回退后，新事务自动

8、启动。隐性事务不需要使用动。隐性事务不需要使用BEGIN TRAN 语句标识事务开始，语句标识事务开始，但需要但需要COMMIT或或ROLLBACK语句来提交或回退事务。语句来提交或回退事务。 10.5 10.5 事务处理事务处理使用事务的经验：使用事务的经验： 在存储过程中管理事务，控制事务的启动、提交和回在存储过程中管理事务，控制事务的启动、提交和回滚。事务中的所有操作包含在：滚。事务中的所有操作包含在：BEGIN TRAN和和COMMIT TRAN之间。自定义存储过程状态值，以反之间。自定义存储过程状态值，以反应事务是否有效执行。应事务是否有效执行。(2) 对事务中的每个操作进行跟踪，一

9、般用到两个系统变对事务中的每个操作进行跟踪，一般用到两个系统变量：量： error和和 rowcount。用系统变量用系统变量error跟踪执行状态，当跟踪执行状态，当error0时，时，说明操作发生了执行错误；说明操作发生了执行错误；用系统变量用系统变量rowcount跟踪受影响的行数跟踪受影响的行数，如果，如果rowcount与预期不符，说明操作发生了业务错误。与预期不符，说明操作发生了业务错误。发生错误时，用发生错误时，用ROLLBACK TRAN回退事务。回退事务。示例：示例：余额表余额表(帐号，余额帐号，余额)“001”账户向账户向“002”账户转账户转X元，手续费元，手续费1%，需

10、要完成如，需要完成如下下2项数据操作：项数据操作：操作操作1：001账户减账户减X*1.01,操作操作2：002账户加账户加X.可能发生的错误如下：可能发生的错误如下：可控的错误：可控的错误：没有没有001账号，没有账号，没有002账号，账号，001账号账号余额不足。余额不足。不可控的错误：操作不可控的错误：操作1失败，操作失败，操作2失败。失败。（事务定义事务定义见见 代码代码）使用事务的不好的做法：使用事务的不好的做法： 有些程序员定义事务时，只用一对有些程序员定义事务时，只用一对BEGIN TRAN和和COMMIT TRAN，在这种情况下，如果部分操作发生错，在这种情况下，如果部分操作发

11、生错误，其余操作可能成功，将导致数据的不一致性。误，其余操作可能成功，将导致数据的不一致性。 在开始事务之前使用在开始事务之前使用SET XACT_ABORT ON（默认情况（默认情况下为下为OFF)，可以保证事务中任一操作发生错误时，所有，可以保证事务中任一操作发生错误时，所有操作全部回退，但这种方法操作全部回退，但这种方法只能检查执行错误只能检查执行错误，不能检不能检查业务错误查业务错误。例如：例如：SET XACT_Abort ONBEGIN TRANUPDATE 余额表余额表 SET 余额余额=余额余额-amount*1.01 WHERE 账号账号=outIdUPDATE 余额表余额表

12、 SET 余额余额=余额余额+amount WHERE 账号账号=inId print 转账成功转账成功(1) COMMIT TRAN案例案例10.5.2 10.5.2 事务管理事务管理事务嵌套事务嵌套事务可以嵌套执行。即事务可以嵌套执行。即BEGIN TRAN和和COMMIT TRAN之间可以含之间可以含有有BEGIN TRAN和和COMMIT TRAN。最外层的事务提交时，内存事务的操作才被接受，如果外层事务被最外层的事务提交时，内存事务的操作才被接受，如果外层事务被回退，内层事务也将被回退。回退，内层事务也将被回退。案例案例10.5.2 10.5.2 事务管理事务管理事务部分回退事务部分

13、回退 在事务中设定在事务中设定保存点保存点(SavePoint)，可指定，可指定回退到保存点回退到保存点，保存点之前的操作被视为保存点之前的操作被视为有效有效。例：例：如果在完成转账之后，显示账户余额。可在转账业务完如果在完成转账之后，显示账户余额。可在转账业务完成时，进行一次事务保存。成时，进行一次事务保存。BEGIN TRAN tran_转账并显示余额转账并显示余额 -转账操作转账操作 SAVE TRAN AFTER_转账转账 SELECT * FROM 余额表余额表 WHERE 账号账号=outId if error0 ROLLBACK TRAN AFTER_转账转账 COMMIT TR

14、AN TRAN tran_转账并显示余额转账并显示余额 课堂小练：课堂小练：在在BookStore数据库中，存在三个表：数据库中，存在三个表：图书表图书表(图书编号图书编号，书名，状态，书名，状态)借阅表借阅表(图书编号图书编号，借书证号，借出日期，借书证号，借出日期)业务记录表业务记录表(业务编号，借书证号，图书编号，业务业务编号，借书证号，图书编号，业务日期，业务类型日期，业务类型)当借书业务发生时，需要对三个数据操作，当借书业务发生时，需要对三个数据操作，(1)借阅表增加一行借阅表增加一行（T001,1110033,2012-2-3）(2)图书表中某行的状态改为图书表中某行的状态改为借出

15、借出，(3)业务记录表增加一行。业务记录表增加一行。(Y01,1110033,T001,2012-2-3,QQ)请编写事务，实现借书逻辑。请编写事务，实现借书逻辑。2. 触发器触发器关键词：关键词：INSTEAD OF触发器触发器, AFTER触触发器，发器，INSERTED表，表，DELETED表表思考：思考：考虑考虑思考思考中编写的存储过程，当用户调用这个存中编写的存储过程，当用户调用这个存储过程时，可以保证数据库的逻辑一致性，但储过程时，可以保证数据库的逻辑一致性，但是如果用户直接在是如果用户直接在DBMS中中对对“借阅表借阅表”表进表进行增、删、改操作呢？行增、删、改操作呢？解决方法解

16、决方法：在：在“借阅表借阅表”上定义触发器。上定义触发器。触发器触发器(TRIGGER)是一种特殊类型的存储过程。是一种特殊类型的存储过程。p触发器是一种存储过程，与一般的存储过程不同的是：触发器是一种存储过程，与一般的存储过程不同的是：触发器是通过事件触发的，而普通的存储过程需要调触发器是通过事件触发的，而普通的存储过程需要调用才执行。用才执行。p常见的触发器由常见的触发器由UPDATE、INSERT、DELETE操作触操作触发，这种触发器的称为发，这种触发器的称为DML触发器。也有触发器。也有DDL触发器，触发器，不常使用。不常使用。p触发器用于保证数据完整性，触发器用于保证数据完整性，凡

17、是约束能提供的保证，凡是约束能提供的保证，触发器都可以提供触发器都可以提供，约束无法保证的完整性，触发器，约束无法保证的完整性，触发器也能提供。也能提供。触发器的两种类型触发器的两种类型INSTEAD OF 触发器触发器对数据增删改时，只执行触发对数据增删改时，只执行触发器中的操作，而不执行（器中的操作，而不执行（ UPDATE、INSERT、DELETE ）操作。）操作。AFTER触发器触发器数据增删改操作被临时存放，然后与数据增删改操作被临时存放，然后与触发器中的操作一起提交，也可以检查错误从而回退。触发器中的操作一起提交，也可以检查错误从而回退。相当于增删改操作与触发器中的操作构成一个事

18、务。相当于增删改操作与触发器中的操作构成一个事务。触发器的执行时间触发器的执行时间当一个表同时存在当一个表同时存在INSTEAD OF触发器、触发器、AFTER触发器触发器和约束时，对该表中的数据进行增删改，则三种数据和约束时，对该表中的数据进行增删改，则三种数据库对象依次工作，执行次序是：库对象依次工作，执行次序是：执行执行INSTEAD OF触触发器发器-验证约束验证约束-执行执行AFTER触发器触发器。10.4.1 10.4.1 创建触发器创建触发器DML触发器基于触发器基于Deleted表和表和Inserted表来工作。表来工作。Deleted表和表和Inserted表是两个临时表表是

19、两个临时表。执行执行INSERT操作，被添加的行存储在操作，被添加的行存储在Inserted表中。表中。执行执行DELETE操作，被删除的行会存储在操作，被删除的行会存储在Deleted表。表。执行执行UPDATE操作，被修改的行存储在操作，被修改的行存储在Deleted表中，修表中，修改后的数据存储在改后的数据存储在Inserted表中。表中。使用触发器的建议使用触发器的建议 尽量使用约束，仅仅在约束不能满足要求的时候使用尽量使用约束，仅仅在约束不能满足要求的时候使用触发器。触发器。 尽量使用存储过程为外部用户提供调用接口，触发器尽量使用存储过程为外部用户提供调用接口，触发器仅用来控制数据库

20、管理员在仅用来控制数据库管理员在DBMS上的更新操作。上的更新操作。 要注意，由于存储过程中的数据更新也会触发要注意，由于存储过程中的数据更新也会触发TRIGGER，因此，要留意避免重复的操作。例如：，因此，要留意避免重复的操作。例如：用户调用存储过程，存储过程进行了三个关联操作，用户调用存储过程，存储过程进行了三个关联操作，其中的某个操作上触发器也要求进行关联操作，这样其中的某个操作上触发器也要求进行关联操作，这样就产生了重复。就产生了重复。课堂练习：请回答触发情况课堂练习：请回答触发情况-select * from 学生表学生表-select * from 成绩表成绩表CREATE TRI

21、GGER TR_学生表学生表 ON 学生表学生表INSTEAD OF INSERTASIF (SELECT Count(*) FROM 学生表学生表WHERE 性别性别=男男) 5 select * from insertedCREATE TRIGGER TR_学生表学生表 ON 学生表学生表for INSERT,DELETE,UPDATEASBEGINUPDATE 学生表学生表 SET 年龄年龄=年龄年龄+10 WHERE 学号学号= 001-UPDATE 学生表学生表 SET 年龄年龄=年龄年龄+10 WHERE 学号学号= 100if error0 rollbackEND注意：触发器中的注意：触发器中的rollback将使将使INSERT,DELETE,UPDATE等操作回退等操作回退insert 学生表学生表 values(022,刘二刘二,男男,19,05002,60.0,从化从化,广州大学广州大学)UPDATE 学生表学生表 set 学分学分=74总结总结 事务是一个操作序列，它包含了一组数据库操作命令，事务是一个操作序列，它包含了一组数据库操作命令，所有的命令作为一个整体一起向系统提交或撤销操作所有的命令作为一个整体一起向系统提交或撤销操作请求，即要