6 触发器与事务处理ppt课件数据库原理与应用 .pptx
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_05.gif)
《6 触发器与事务处理ppt课件数据库原理与应用 .pptx》由会员分享,可在线阅读,更多相关《6 触发器与事务处理ppt课件数据库原理与应用 .pptx(83页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、数据库原理与应用数据库原理与应用6.1触发器数据库原理与应用数据库原理与应用2数据库原理与应用数据库原理与应用6.1.1 6.1.1 概念概念触发器(触发器(TRIGGER)是一种)是一种特殊的存储过程特殊的存储过程。为为什么要使用触什么要使用触发发器?器?加入了新的水果供加入了新的水果供应应商,在供商,在供应应商表中添加一条商表中添加一条该该供供应应商相商相关的关的记录记录,供,供应应商的商的总总数就必数就必须须同同时时改改变变。供供应应商退出商退出时时,在供,在供应应商表中商表中删删除除该该供供应应商的商的记录记录,同,同时时也也希望能希望能删删除除该该供供应应商提供的水果商提供的水果记录
2、记录。当对表进行当对表进行INSERT、UPDATE、DELETE操作时就会激活相应操作时就会激活相应的触发器并执行。的触发器并执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器经常用于加强数据的完整性约束和业务规则等。数据库原理与应用数据库原理与应用触发器的作用触发器的作用(1)安全性)安全性。对用户操作数据库的权限进行控制。比如,基于时。对用户操作数据库的权限进行控制。比如,基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据间限制用户的操作,例如不允许下班后和节假日修改数据库数据(2)审计审计。可以跟踪用可以跟踪用户对户对数据数据库库的操作,把用的操作,把用户对户对数
3、据数据库库的更的更改写入到改写入到审计审计表。表。(3)实现实现非非标标准的数据准的数据库库完整性完整性规则规则 触触发发器可以器可以对对数据数据库库相关的表相关的表进进行更新操作。行更新操作。触触发发器可以器可以产产生比生比检查约检查约束更束更为为复复杂杂的限制的限制。触发器能够回退那些破坏相关完整性的操作,取消试图进行数触发器能够回退那些破坏相关完整性的操作,取消试图进行数据更改的事务。据更改的事务。触发器可以自动计算数据值。触发器可以自动计算数据值。数据库原理与应用数据库原理与应用6.1.2 6.1.2 创建触发器创建触发器CREATE TRIGGER 触发器名触发器名 BEFORE|A
4、FTER INSERT|DELETE|UPDATE ON 表名表名 FOR EACH ROW 触发的触发的SQL语句语句数据库原理与应用数据库原理与应用【例6-1】创建触发器del_tri触发器,触发器将记录哪些用户删除了fruits表中的数据,以及删除的时间和进行的操作类型。首先创建merch_log的日志信息表,用于存储用户对表的操作。CREATE TABLE merch_log(who VARCHAR(30),oper_date DATE,oper VARCHAR(20);数据库原理与应用数据库原理与应用其次在fruits表上创建DELETE类型触发器,实现向merch_log表添加操作
5、的用户名、日期及操作类型。CREATE TRIGGER del_tri AFTER DELETE ON fruits FOR EACH ROW INSERT INTO merch_log(who,oper_date,oper)VALUES(USER(),SYSDATE(),DELETE);数据库原理与应用数据库原理与应用最后测试触发器是否正常运行,在fruits表中删除f_id为a1的记录;并查询日志信息表merch_log。DELETE FROM fruits WHERE f_id=a1;SELECT *FROM merch_log;数据库原理与应用数据库原理与应用触发器如何取得激活触发器操
6、作的旧值和新值触发器如何取得激活触发器操作的旧值和新值(1)旧值。在字段名前加上旧值。在字段名前加上“OLD.”限定词限定词(2)新值。新值。在字段名前加上在字段名前加上“NEW.”限定限定词词 INSERT触触发发器,只能使用器,只能使用NEW.列名,因列名,因为为不涉及旧不涉及旧值值行。行。DELETE触触发发器,只能使用器,只能使用OLD.列名,因列名,因为为不涉及新不涉及新值值行。行。UPDATE触触发发器,可以使用器,可以使用OLD.列名引用更新前某一行的旧列名引用更新前某一行的旧值值,使用使用NEW.列名引用更新后行的新列名引用更新后行的新值值。数据库原理与应用数据库原理与应用【例
7、6-2】本例题实现级联更新。在修改suppliers表中的s_id之后(AFTER)级联地、自动地修改fruits表中该供应商的s_id。CREATE TRIGGER tr_up AFTER UPDATE ON suppliers FOR EACH ROW UPDATE fruits SET s_id=NEW.s_id WHERE s_id=OLD.s_id;UPDATE suppliers SET s_id=110 WHERE s_id=101;SELECT*FROM fruits WHERE s_id=110;数据库原理与应用数据库原理与应用6.1.3 6.1.3 查看触发器查看触发器【例
8、6-3】查询触发器tr_up的信息。USE information_schema;SELECT*FROM triggers WHERE trigger_name=tr_up;通过数据库information_schema中的系统表triggers,查询指定触发器的定义、状态和语法信息等。数据库原理与应用数据库原理与应用6.1.4 6.1.4 删除触发器删除触发器【例6-4】删除触发器tr_up。DROP TRIGGER tr_up;DROP TRIGGER 触发器名;6.2事务数据库原理与应用数据库原理与应用13数据库原理与应用数据库原理与应用update bank set sal=sal-1
9、000 where name=张张三三;update bank set sale=sale+1000 where name=李四李四;如何在如何在银行银行表(表(bank)中中实现实现客户客户(name)张三张三给给李四李四转账转账1000元存款元存款(sal)的操作?)的操作?update bank set sal=sal-1000 where name=张张三三;update bank set sal=sal+1000 where name=李四李四;下面的下面的SQL语句执行后的结果?语句执行后的结果?张三张三账户减账户减少了少了10001000元元,但李四但李四账户账户却没有加钱却没有
10、加钱。是否有一种方法是否有一种方法使得使得一个业务对应的一个业务对应的SQL语句要么都执语句要么都执行,要么里面有一句没有执行,就全部不执行?行,要么里面有一句没有执行,就全部不执行?数据库原理与应用数据库原理与应用 事务通常包含一系列事务通常包含一系列INSERT、DELETE、UPDATE等更新操作等更新操作语句,这些更新操作是一个语句,这些更新操作是一个不可分割不可分割的逻辑工作单元。的逻辑工作单元。每个事务的处理必须要满足每个事务的处理必须要满足ACID的的4个特性,即原子性(个特性,即原子性(A)、)、一致性(一致性(C)、隔离性()、隔离性(I)和持久性()和持久性(D)。)。6.
11、2.1 6.2.1 事务的概述事务的概述数据库原理与应用数据库原理与应用1.1.原子性(原子性(AtomicityAtomicity)原子性意味着每个事务都必须作为一个不可分割的单元,事务原子性意味着每个事务都必须作为一个不可分割的单元,事务中包含的所有操作要么全做,要么全不做。中包含的所有操作要么全做,要么全不做。6.2.2 6.2.2 事务的事务的ACIDACID特性特性如何实现事务的原子性呢?如何实现事务的原子性呢?使用使用DBMS的事务日志文件,把那些未成功执行的事务中已执的事务日志文件,把那些未成功执行的事务中已执行的操作对数据产生的影响行的操作对数据产生的影响“抹掉抹掉”。事事务日
12、志文件日志文件记录了每个事了每个事务对数据数据库所作所作变更的更的“旧旧值”和和“新新值”,当一个事,当一个事务不能完成不能完成时,将,将这些些变更了的更了的“新新值”恢复到它的恢复到它的“旧旧值”(即抹掉了(即抹掉了该变更)。更)。数据库原理与应用数据库原理与应用 一致性是指事务在完成时,必须使所有的数据从一种一致性状一致性是指事务在完成时,必须使所有的数据从一种一致性状态变更为另外一种一致性状态,确保数据的完整性。态变更为另外一种一致性状态,确保数据的完整性。如银行转账事务:如银行转账事务:update bank set sal=sal-1000 where name=张张三三;updat
13、e bank set sal=sal+1000 where name=李四李四;转账事事务必必须保保证两个两个账户的的总钱数不数不变(这就是一种一致性的限制),就是一种一致性的限制),转账前前总数是多少,数是多少,转账后的后的总还是多少。是多少。2.2.一致性(一致性(ConsistencyConsistency)数据库原理与应用数据库原理与应用 事务的事务的隔离性可以防止多个事务隔离性可以防止多个事务并发执行并发执行时,由于它们的操时,由于它们的操作命令作命令交叉交叉执行而导致的数据不一致状态。执行而导致的数据不一致状态。发生发生过的过的事件:事件:要求:要求:一个事务的执行不能被其他事务干
14、扰。一个事务的执行不能被其他事务干扰。3.3.隔离性(隔离性(IsolationIsolation)数据库原理与应用数据库原理与应用 事务完成之后,所做的修改对数据的影响是永久的,即使出现事务完成之后,所做的修改对数据的影响是永久的,即使出现系统故障,数据仍可以恢复。系统故障,数据仍可以恢复。4.4.持久性(持久性(DurabilityDurability)InnoDB存储引擎引入了与事务处理相关的存储引擎引入了与事务处理相关的REDO(重做)日志(重做)日志和和UNDO(撤消)日志。(撤消)日志。当每条当每条SQL语句进行数据更新操作语句进行数据更新操作时,将写入时,将写入REDO日志文件,
15、日志文件,在在MySQL崩溃恢复时会重新执行崩溃恢复时会重新执行REDO日志中的记录。日志中的记录。REDO日志对应磁盘上的日志对应磁盘上的ib_logfileN文件文件 UNDO日志主要用于事务异常时的数据回滚日志主要用于事务异常时的数据回滚。磁盘上不存在单独的磁盘上不存在单独的UNDO日志文件,所有的日志文件,所有的UNDO日志均存放日志均存放在表空间对应的在表空间对应的.ibd数据文件中。数据文件中。数据库原理与应用数据库原理与应用1一个事务执行过程中,其正在访问的数据被其他事务所修一个事务执行过程中,其正在访问的数据被其他事务所修改,导致处理结果不正确,这是由于违背了事务的改,导致处理
16、结果不正确,这是由于违背了事务的 A)原子性)原子性 B)一致性)一致性 C)隔离性)隔离性 D)持久性)持久性2“一旦事务成功提交,其对数据库的更新操作将永久有效,一旦事务成功提交,其对数据库的更新操作将永久有效,即使数据库发生故障即使数据库发生故障”,这一性质是指事务的,这一性质是指事务的 A)原子性)原子性 B)一致性)一致性 C)隔离性)隔离性 D)持久性)持久性练习:练习:CD数据库原理与应用数据库原理与应用(1)自动提交事务模式自动提交事务模式。每条单独的语句都是一个事务,是每条单独的语句都是一个事务,是MySQL默认的事务管理模默认的事务管理模式。式。(2)显式事务模式显式事务模
17、式。由由用户定义事务的启动和结束。用户定义事务的启动和结束。6.2.3 MySQL6.2.3 MySQL事务控制语句事务控制语句1.1.事务模式事务模式(3)隐性事务模式隐性事务模式。在当前事务完成提交或回滚后,新事务自动启动。在当前事务完成提交或回滚后,新事务自动启动。数据库原理与应用数据库原理与应用 修改提交方式修改提交方式:SET AUTOCOMMIT=0|1;SET AUTOCOMMIT=1是默认的,为自动提交事务模式。是默认的,为自动提交事务模式。SET AUTOCOMMIT=0,设置之后的所有事务都需要通过明,设置之后的所有事务都需要通过明确的命令进行提交和回滚。确的命令进行提交和
18、回滚。数据库原理与应用数据库原理与应用START TRANSACTION;或或 BEGIN WORK;【说明】【说明】在存储过程中只能使用在存储过程中只能使用START TRANSACTION语句来开启一语句来开启一个事务,因为个事务,因为MySQL数据库分析器会自动将数据库分析器会自动将BEGIN识别为识别为BEGINEND语句。语句。2.2.开始事务开始事务数据库原理与应用数据库原理与应用COMMIT WORK AND NO CHAIN NO RELEASE;【说明】【说明】提交事务的最简单形式,只需要给出提交事务的最简单形式,只需要给出COMMIT命令命令。AND CHAIN子句会在当前
19、事务结束时,立刻启动一个新事务,子句会在当前事务结束时,立刻启动一个新事务,并且新事务与刚结束的事务有相同的隔离等级。并且新事务与刚结束的事务有相同的隔离等级。RELEASE子句在终止了当前事务后,会让服务器断开与当前子句在终止了当前事务后,会让服务器断开与当前客户端的连接。客户端的连接。NO关键字可以抑制关键字可以抑制CHAIN或或RELEASE完成。完成。3.3.提交事务提交事务数据库原理与应用数据库原理与应用ROLLBACK WORK AND NO CHAIN NO RELEASE;回滚会结束用户的事务,并撤消正在进行的所有未提交的修改回滚会结束用户的事务,并撤消正在进行的所有未提交的修
20、改(即(即BEGIN WORK或或START TRANSACTIO后的所有修改)。后的所有修改)。4.4.回滚事务回滚事务数据库原理与应用数据库原理与应用【例6-5】假设银行存在两个借记卡账户(account)李三与王五,要求这两个借记卡账户不能用于透支,即两个账户的余额(balance)不能小于0。创建存储过程tran_proc(),实现两个账户的转账业务。数据库原理与应用数据库原理与应用数据库原理与应用数据库原理与应用 创建保存点创建保存点:SAVEPOINT 保存点名称保存点名称;5.5.设置保存点设置保存点 回滚事务到保存点回滚事务到保存点:ROLLBACK WORK TO SAVEP
21、OINT 保存点名称保存点名称;数据库原理与应用数据库原理与应用【例6-6】创建save_p1_proc存储过程,仅仅撤消第二条insert语句,但提交了第一条insert语句。数据库原理与应用数据库原理与应用数据库原理与应用数据库原理与应用【例6-6】创建save_p2_proc存储过程,先撤消第二条insert语句,然后撤消了所有的insert语句。数据库原理与应用数据库原理与应用数据库原理与应用数据库原理与应用6.3并 发 控 制数据库原理与应用数据库原理与应用34数据库原理与应用数据库原理与应用事务并发执行:事务并发执行:DBMS同时执行多个事务对同一数据的操作同时执行多个事务对同一数
22、据的操作(并发操作),为此,(并发操作),为此,DBMS需要对各事务中的操作顺序进行需要对各事务中的操作顺序进行安排,以达到同时运行多个事务的目的。安排,以达到同时运行多个事务的目的。6.3.1 6.3.1 理解什么是并发控制理解什么是并发控制在单处理机系统中,事务的并发执行实际上是这些并发事务轮在单处理机系统中,事务的并发执行实际上是这些并发事务轮流交叉进行的,这种并发方式称为交叉并发方式。流交叉进行的,这种并发方式称为交叉并发方式。在多处理机系统中,每个处理机可以运行一个事务,多个处理在多处理机系统中,每个处理机可以运行一个事务,多个处理机可以同时运行多个事务,实现事务真正的并发运行,这种
23、并机可以同时运行多个事务,实现事务真正的并发运行,这种并发执行方式称为同时并发方式。发执行方式称为同时并发方式。数据库原理与应用数据库原理与应用为什么出现一票两卖为什么出现一票两卖?分析:分析:售票售票处A读车票数据票数据库余票数余票数为x;x;售票售票处B读车票数据票数据库余票数余票数为x;x;售票售票处A售出一售出一张火火车票,更新数票,更新数据据库中余票数中余票数为x-1x-1;售票售票处B售出一售出一张火火车票,更新数票,更新数据据库中余票数中余票数为x-1x-1;本本卖出出2张票,但数据票,但数据库只减了只减了1张票票。原因:原因:两个售票两个售票过程(事程(事务)交叉交叉进行,行,
24、发生了相互生了相互干干扰。并发执行的事务,可能会同时读写数据库中同一数据的情况,并发执行的事务,可能会同时读写数据库中同一数据的情况,如果不加以控制,可能会引起读写数据的冲突,对数据库的一如果不加以控制,可能会引起读写数据的冲突,对数据库的一致性会造成破坏。致性会造成破坏。数据库原理与应用数据库原理与应用事务对数据库中数据可以进行哪些操作?事务对数据库中数据可以进行哪些操作?读操作和读操作和写操作写操作读和写,哪个可能会导致数据不正确?读和写,哪个可能会导致数据不正确?读不会破坏数据,但写可能导致数据不正确。读不会破坏数据,但写可能导致数据不正确。事务并发执行可能引发的问题?事务并发执行可能引
25、发的问题?读读-读读读读-写写写写-读读 写写-写写 保持数据保持数据一致性一致性不可重复不可重复读读脏数据数据丢失更新失更新6.3.2 6.3.2 并发执行可能引起的问题并发执行可能引起的问题数据库原理与应用数据库原理与应用1.1.丢失更新丢失更新又称为又称为覆盖未提交的数据覆盖未提交的数据。原因原因:由于两个由于两个(或多个或多个)事务对同一数据并发地写入引起,事务对同一数据并发地写入引起,称为称为写写写写冲突。冲突。结果结果:与串行地执行两个:与串行地执行两个(或多个或多个)事务的结果不一致。事务的结果不一致。数据库原理与应用数据库原理与应用2 2、不可重复读不可重复读 又称为又称为读值
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 触发器与事务处理ppt课件数据库原理与应用 触发器 事务处理 ppt 课件 数据库 原理 应用
![提示](https://www.deliwenku.com/images/bang_tan.gif)
限制150内