第12章触发器和事件电子课件 MySQL数据库管理与应用.pptx

《第12章触发器和事件电子课件 MySQL数据库管理与应用.pptx》由会员分享，可在线阅读，更多相关《第12章触发器和事件电子课件 MySQL数据库管理与应用.pptx（34页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、第12 章触发器和事件电子课件 MySQL 数据库管理与应用第12章 触发器和事件主要内容12.1 触发器12.2 事件12.3 本章小结12.1 触发器 12.1.1 触发器概述 MySQL的触发器是与表有关的命名数据库对象，本质上是一种特殊的存储过程，它在插入、修改和删除特定表中的数据时触发执行。触发器不能使用CALL语句调用，当表上出现特定事件时，MySQL将自动调用该触发器。触发器可以查询其他表，也可以包含复杂的SQL语句，但不能在触发器中以显式或隐式方式开始或结束事务。12.1 触发器 12.1.1 触发器概述触发器的优点：触发器不需要明确调用，当触发表中的数据做出相应的修改后由系统

2、自动调用。触发器支持回滚机制，保证了数据的一致性和完整性。触发器可以用来实施比FOREIGN KEY、CHECK约束等更为复杂的检查和操作。触发器可以通过数据库中的相关表修改其他表。12.1 触发器 12.1.1 触发器概述 触发器比数据库本身标准的功能有更精细和更复杂的数据控制能力，用户利用触发器可以方便地实现数据库中数据的完整性和一致性。MySQL中的触发器是行级触发器，在增加、删除和修改操作相对频繁的表上尽量不要创建触发器，因为它会对表中受影响的每一行都执行一次触发器，所以触发器消耗资源较大，要慎重使用。12.1 触发器 12.1.2 NEW和OLD变量 在触发器中不能直接使用列名去标识

3、，我们用“NEW.列名”和“OLD.列名”来区分。对于insert、update和delete三种触发器事件，“NEW.列名”和“OLD.列名”并不是都适用，需要注意它们的合法性。12.1 触发器 12.1.2 NEW和OLD变量 当向表中插入新记录时，在触发器中可以使用NEW.列名来获得新纪录的某个列的值，此时OLD是不合法的。当从表中删除记录时，在触发器中可以使用OLD.列名来获得被删除纪录的某个列的值，此时NEW是不合法的。当修改表中的某条记录时，在触发器中可以使用OLD来获得修改前的记录的值，使用NEW来获得修改后的记录的值。OLD记录是只读的，不能修改；NEW记录可以在BEFORE触

4、发器中修改（SET NEW.列名=值），但在AFTER触发器中不能更改。12.1 触发器 12.1.3 创建触发器 使用命令创建触发器 语法格式：CREATE TRIGGER IF NOT EXISTS trigger_nameBEFORE|AFTER INSERT|UPDATE|DELETE ON table_name FOR EACH ROW FOLLOWS|PRECEDES other_trigger_nameBEGIN trigger_body END12.1 触发器 12.1.3 创建触发器 使用命令创建触发器根据触发动作时间和触发器事件的组合，在同一张表上建立的同一触发器事件、不同

5、触发动作时间的触发器的执行顺序如下所示：如果有 BEFORE触发器，先执行BEFORE触发器。执行SQL语句。如果有AFTER触发器，执行AFTER触发器。若SQL语句或触发器执行失败，MySQL会回滚事务，顺序如下所示：如果 BEFORE 触发器执行失败，SQL语句无法正确执行。如果SQL语句执行失败，AFTER型触发器不会被触发。如果AFTER类型的触发器执行失败，SQL语句会回滚。12.1 触发器 12.1.3 创建触发器 使用命令创建触发器增加一个备选课程表，表中四个字段含义分别为课程号、课程名称、还能选课的人数、选课人数上限。【例12.1】创建备选课程表course_availabl

6、e。在MySQL命令行客户端输入命令：CREATE TABLE course_available(cno CHAR(3)PRIMARY KEY,cname VARCHAR(10),cavailable TINYINT,climit TINYINT);12.1 触发器 12.1.3 创建触发器 使用命令创建触发器【例12.2】创建触发器，当在备选课程表上插入数据时，检查选课人数上限climit字段的取值是否在0到100之间，如果该值大于100则按100插入，如果该值小于0则按0插入。在MySQL命令行客户端输入命令：DELIMITER/CREATE TRIGGER insert_triBEFOR

7、E INSERT ON course_available FOR EACH ROWBEGIN IF NEW.climit100 then SET NEW.climit=100;END IF;END/DELIMITER;12.1 触发器 12.1.3 创建触发器 使用命令创建触发器【例12.3】创建触发器，当在备选课程表上插入数据时，检查选课人数上限climit字段的取值是否在0到100之间，若不在这个区间则不允许插入该行数据。这个要求使用CHECK约束设置更为简单一些，目前只考虑使用触发器来完成。在MySQL命令行客户端输入命令：DELIMITER/CREATE TRIGGER insert_

8、tri2BEFORE INSERT ON course_availableFOR EACH ROW BEGIN IF(NEW.climit100)THENSIGNAL SQLSTATE HY000 SET message_text=climit的值必须在0到100之间;END IF;END/DELIMITER;12.1 触发器 12.1.3 创建触发器 使用命令创建触发器【例12.4】创建触发器，实现当向score表中添加记录时，自动将备选课程表中相应课程的cavailable列的值减1。在MySQL命令行客户端输入命令：DELIMITER/CREATE TRIGGER sc_insert_t

9、riBEFORE INSERT ON scoreFOR EACH ROWBEGIN UPDATE course_available SET cavailable=cavailable-1 WHERE cno=NEW.cno;END/DELIMITER;12.1 触发器 12.1.3 创建触发器 使用图形化工具创建触发器 打开Workbench工具，连接到MySQL服务器。以例题12.4中的sc_insert_tri触发器的创建过程为例进行讲解。选中“Schemas”标签，在“jwgl”数据库下找到要创建触发器的表score，单击score右边的 图标，或者右键单击score，找到“Alter

10、table”选项并单击，出现score表的界面，如图所示。12.1 触发器 12.1.3 创建触发器 使用图形化工具创建触发器 单击下方“Triggers”标签，进入触发器界面，如图所示。触发动作时间是BEFORE，触发器事件是INSERT，所以我们单击BEFORE INSERT后 边 的 加 号“+”，或 者 右 键 单 击 BEFORE INSERT，找到“Add new trigger”选项并单击，出现如图所示界面。12.1 触发器 12.1.3 创建触发器 使用图形化工具创建触发器 将代码部分补充完整，修改触发器的默认名称为“sc_insert_tri”，注意此界面不需要输入DELIM

11、ITER语句，然后单击“Apply”，此时出现查看触发器的SQL脚本的界面。依次单击“Apply”和“Finish”后，触发器就创建完成，此时在score表 下 的“Triggers”中 就 出 现 了 sc_insert_tri触 发 器，在BEFORE INSERT下面也显示了触发器sc_insert_tri，如图所示。12.1 触发器 12.1.4 查看触发器 使用命令查看触发器查看触发器是指查看数据库中已存在的触发器的定义、状态和语法等信息。可以通过三种语句查看触发器的信息。SHOW TRIGGERS FROM|IN db_name LIKE pattern SHOW CREATE T

12、RIGGER trigger_name 查询information_schema数据库下的triggers表【例12.5】查看触发器信息。在MySQL命令行客户端输入命令：SHOW TRIGGERS GSHOW CREATE TRIGGER insert_tri GSELECT*FROM information_schema.triggers WHERE trigger_name=sc_insert_tri G12.1 触发器 12.1.4 查看触发器 使用图形化工具查看触发器 打开Workbench工具，连接到MySQL服务器。单击数据库“jwgl”右边的 图标，或者右键单击数据库“jwgl

13、”，找到“Schema Inspector”选项并单击，会出现jwgl数据库的相关信息。单击“Triggers”标签，即可看到当前数据库下所有的触发器信息，如图所示。如果想看触发器的定义信息，可以单击BEFORE INSERT下面出现的触发器，例如我们在course_available表上创建的两个触发器，选中想要查看的触发器名称，此时界面中会出现该触发器的相关信息及定义语句，如图所示。12.1 触发器 12.1.5 使用触发器触发器创建完成后，需要我们在触发表上执行触发器事件，由系统自动调用相应的触发器。【例12.6】给出激发触发器insert_tri2执行的SQL语句。在MySQL命令行客

14、户端输入命令：INSERT INTO course_available VALUES(204,大型数据库,40,123);SELECT*FROM course_available;12.1 触发器 12.1.5 使用触发器 根据触发器insert_tri2的主体语句的定义，由于123大于100，触发器应该返回错误信息，但我们发现SQL语句运行结果为“Query OK”，此时再运行查询语句，结果发现表中竟然添加了课程号为“204”的这条记录，只是把插入的数据从123换成了100。这是什么原因呢？原 来 我 们 之 前 在 course_available表 上 创 建 了 两 个 触 发 器 i

15、nsert_tri和insert_tri2，它们都是触发动作时间为BEFORE的INSERT类型触发器，对于触发动作时间、触发器事件和触发对象完全相同的多个触发器而言，虽然它们都可以创建成功，但在使用时默认只有第一个创建的触发器可以被触发执行，所以在上图中显示的结果是因为激活执行了insert_tri触发器。12.1 触发器 12.1.5 使用触发器如果想使用insert_tri2触发器，有以下几种操作方法。将insert_tri触发器删除，此时在course_available 上的BEFORE INSERT类型触发器中，insert_tri2是最先被创建的，在激活时默认情况下会被激活执行。

16、在insert_tri触发器中增加关于会话变量值的判断语句，我们通过在触发器之外的命令行中改变会话变量的值，从而达到暂时屏蔽该触发器的目的。在insert_tri2触发器中增加一条语句“PRECEDES insert_tri”，它的含义是该触发器先于insert_tri触发器执行。当然这两个触发器必须是触发对象、触发器事件和触发动作时间都完全相同时才有意义。12.1 触发器 12.1.5 使用触发器【例12.6】重新创建触发器insert_tri2，使其能够被激发执行。修改例题12.3，在FOR EACH ROW语句后增加 PRECEDES insert_tri即可。激发触发器执行的语句：IN

17、SERT INTO course_available VALUES(302,宪法学,50,123);INSERT INTO course_available VALUES(302,宪法学,50,76);12.1 触发器 12.1.5 使用触发器【例12.7】给出激发触发器sc_insert_tri执行的SQL语句。在MySQL命令行客户端输入命令：INSERT INTO score VALUES(20210201001,302,NULL);从结果中可以看出，当在score表中添加课程号为302的数据时，激发了触发器sc_insert_tri的执行，course_available表中课程号为3

18、02的记录可选人数cavailable字段值自动减1，从50变成了49。12.1 触发器 12.1.6 删除触发器 使用命令行删除触发器可以使用DROP TRIGGER语句删除触发器，语法格式如下所示。DROP TRIGGER IF EXISTS schema_name.trigger_name如果删除表，则该表上创建的触发器也会被删除。【例12.8】删除insert_tir触发器。在MySQL命令行客户端输入命令：DROP TRIGGER insert_tri;12.1 触发器12.1.6 删除触发器 使用图形化工具删除触发器 打开Workbench工具，连接到MySQL服务器。找到要删除的

19、触发器，如：insert_tri2，此时单击该触发器右边的减号“-”，然后单击“Apply”，出现删除触发器的脚本页面，再次单击“Apply”和“Finish”即可完成删除操作。12.2 事件12.2.1 事件概述 事件是MySQL在相应的时刻调用的过程式数据库对象，它又被称为临时触发器。事件和触发器类似，都是在某些事情发生的时候启动，区别是触发器是被基于某个表所产生的事件触发，而事件是基于特定时间周期触发来执行某些任务，它可以在某个时刻调用一次，也可以在某个时间段内周期性地启动，通常用于对实时性要求较高的场合。MySQL8.0中的事件在默认情况下是开启的，我们可以通过参数event_sche

20、duler的值来查看事件是否开启。SHOW VARIABLES LIKE event_scheduler;12.2 事件 12.2.2 创建事件语法格式：CREATE EVENT IF NOT EXISTS event_name ON SCHEDULE schedule ON COMPLETION NOT PRESERVE ENABLE|DISABLE COMMENT comment DO event_body12.2 事件 12.2.2 创建事件【例12.9】创建事件，在1分钟内每隔10秒钟将course_available表中课程名为“大型数据库”的climit字段的值减1。在MySQL命

21、令行客户端输入命令：CREATE EVENT event_updateON SCHEDULE EVERY 10 SECOND ENDS CURRENT_TIMESTAMP()+INTERVAL 1 MINUTEON COMPLETION PRESERVE ENABLEDO UPDATE course_available SET climit=climit-1 WHERE cname=大型数据库;12.2 事件 12.2.3 修改事件 语法格式：ALTER EVENT event_name ON SCHEDULE schedule ON COMPLETION NOT PRESERVERENAME

22、 TO new_event_nameENABLE|DISABLE COMMENT commentDO event_body12.2 事件 12.2.3 修改事件【例12.10】关闭事件event_update,并将其重命名为new_event。在MySQL命令行客户端输入命令：ALTER EVENT event_update RENAME TO new_event DISABLE;12.2 事件 12.2.4 查看事件 通过SHOW EVENTS语句来查看事件相关信息，语法格式：SHOW EVENTS FROM|IN schema_name LIKE pattern 通过SHOW CREATE

23、 EVENT语句查看事件的当前定义信息及状态 通过查询information_schema.events表查看事件信息【例12.11】查看事件event_update。在MySQL命令行客户端输入命令：SHOW EVENTS FROM jwgl GSHOW CREATE EVENT new_event GSELECT*FROM information_schema.events WHERE event_name=new_event G12.2 事件 12.2.5 删除事件 语法格式：DROP EVENT IF EXISTS event_name【例12.12】删除事件new_event。在MySQL命令行客户端输入命令：DROP EVENT new_event;12.3 本章小结本章介绍了MySQL触发器和事件的操作，主要包括NEW和OLD变量、创建触发器、查看触发器、使用和删除触发器。熟练掌握触发器的操作，可以帮助数据库更好地实现数据的完整性和一致性，但由于行级触发器的特点，在插入、删除和修改操作频繁的表上应尽量避免创建触发器。