《MySQL数据原理与应用》 课件 第11章 触发器

触发器第11章通过本课程的学习，您将应该：了解数据库的相关概念熟悉数据库工具的使用熟悉SQL语言的规则与规范熟练掌握MySQL数据库管理方法熟练掌握MySQL表结构管理方法熟练掌握表记录的检索方法熟练掌握视图与索引的使用方法熟练掌握数据处理之增删改熟练掌握存储过程与游标的使用方法熟练掌握存储函数的应用熟练掌握触发器的应用熟练掌握事务的基本特性和应用场景熟练掌握数据库安全及管理熟练掌握MySQL数据库备份和恢复操作了解利用MySQL+Java开发一个数据库学习系统并部署课程目标项目部署成功本讲教学目标了解触发器的定义、作用和优缺点。熟悉掌握触发器的创建。掌握查看和删除触发器。掌握通过图形化工具管理触发器。通过完成课业任务学会分析问题、解决问题，提高动手操作能力。通过本讲的学习，您将可以：1.OPTION2.OPTIONOPTION4.OPTION3.5.OPTION创建after_insert_class触发器目录ContentMySQL触发器的概述01触发器的创建02查看触发器03删除触发器04课业任务05常见错误及解决方案06MySQL触发器的概述0111.1.1什么是触发器MySQL从5.0.2版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。所谓事件是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时，就相当于该事件发生了，就会自动激发触发器执行相应的操作。当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。11.1.2触发器作用触发器是一种特殊的存储过程，它在插入、删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力，其作用总结如下。作用3.实现复杂的数据完整性规则实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制，与规则不同的地方是触发器能够引用列或数据库对象。

4.实现复杂的级联操作尽管利用外键可以实现相关的级联操作，但是利用触发器可以实现更加复杂的级联操作。1.安全性能够基于数据库的值使用户具有操作数据库的某种权利。能够基于时间限制用户的操作；能够基于数据库中的数据限制用户的操作。2.审计能够跟踪用户对数据库的操作。审计用户操作数据库的语句；把用户对数据库的更新写入审计表。11.1.3触发器的优缺点（1）触发器可以确保数据的完整性。触发器可以保证存储在数据库中的所有数据值保持正确的状态，从而保护了数据的完整性。（2）触发器可以记录操作日志。利用触发器可以具体记录什么时间发生了什么。例如，记录修改会员储值金额的触发器，就是一个很好的例子。这对操作人员还原操作执行时的具体场景，更好地定位问题原因很有帮助。（3）触发器还可以用在操作数据前，对数据进行合法性检查。超市进货的时候，需要录入进货价格。但是人为操作很容易犯错误，例如在录入金额的时候，看错了行，录入的价格远超售价，导致账面上的亏损，这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统。优点11.1.3触发器的优缺点（1）触发器可读性差。触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能不受应用层的控制，这对系统维护是非常有挑战的。（2）数据的变更，可能会导致触发器出错。数据表结构的变更，会导致触发器出错，进而影响数据操作的正常运行。（3）操作频繁的表上不建议创建触发器。在添加、删除和修改操作相对频繁的表上不建议创建触发器，因为它会对表中受影响的每一行执行一次触发器，使触发器消耗资源较大。缺点11.1.4NEW和OLD变量行级触发器中有两个过渡变量，用于识别值的状态，分别是：OLD变量和NEW变量，这两个变量只在DML触发表中字段时才有效，只能在触发器内部使用。对于INSERT语句，只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句，可以同时使用OLD和NEW。NEW变量：是指操作之后，用来记录变量，使用形式：new.字段名。OLD变量：是指操作之前，用来记录变量，使用形式：old.字段名。触发器的创建0211.2触发器的创建触发器（trigger）是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MySQL自动调用。创建触发器的语法结构如下。11.2触发器的创建

说明：（1）触发器名称必须符合标识符的命名规则，同一个数据库不允许存在同名的触发器；（2）表名：表示触发器监控的对象；（3）BEFORE|AFTER：表示触发的时间。BEFORE表示在事件之前触发；AFTER表示在事件之后触发；（4）INSERT|UPDATE|DELETE：表示触发的事件。INSERT表示插入记录时触发；UPDATE表示更新记录时触发；DELETE表示删除记录时触发。（5）FOREACHROW通知触发器每隔一行执行一次动作，而不是对整个表执行一次,叫行级触发器；（6）触发器执行的语句块：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。11.2触发器的创建当向数据库学习系统的“tb_department”系部表中添加数据时需要记录日志信息，利用SQL语句创建一个触发器来实现这个功能。【案例11.1】记录系部表添加数据的日志信息。首先，根据需求创建一个名为“tb_department_logs”（系部日志表）的数据表，用于存放“tb_department”日志信息的SQL语句如下所示。11.2触发器的创建

说明：（1）id字段：INT类型，主键约束并且设置为自增长；（2）date字段：DATE类型，用于记录日期值；（3）log_text字段：VARCHAR类型，用于记录相关信息；（4）创建数据表的前提一定要通过“USE数据库名称;”语句指定创建的数据表在哪个数据库中。执行上述SQL语句，运行结果如下图所示，创建数据表成功。11.2触发器的创建其次，创建名为“after_insert_department”的触发器，向“tb_department”数据表插入数据之后向“tb_department_logs”数据表中插入日志信息的SQL语句如下所示。11.2触发器的创建

说明：（1）存储过程体中表示当“tb_department”数据表插入数据后，“tb_department_logs”数据表中会记录当时的时间以及记录“添加了新的系部信息”；（2）“DELIMITER$$”语句的作用是将MySQL的结束符设置为$$，并以“$$”结束存储过程。存储过程定义完毕之后再使用“DELIMITER;”恢复默认结束符，DELIMITER也可以指定其他符号作为结束符；（3）CURDATE()函数是MySQL数据库服务器中以YYYY-MM-DD格式返回系统当前日期和时间；（4）CONCAT()函数用于连接给定的参数。它可是有一个或多个参数，如果所有参数都是非二进制字符串，则结果是非二进制字符串。如果参数包含任何二进制字符串，则结果为二进制字符串。如果给定了一个数字参数，那么它将被转换为等效的非二进制字符串形式。11.2触发器的创建执行上述SQL语句，运行结果如下图所示，触发器创建成功。向“tb_department”数据表中插入数据，SQL语句示例如下。11.2触发器的创建

说明：往“tb_department”数据表中添加一条记录，其中department_id（学院编号）字段的值是X09；department_name（学院名称）的值是人工智能学院；department_phone（学院联系方式）的值是为87471266；department_address（学院地址）的值是13栋教学楼。执行上述SQL语句，运行结果如下图所示，数据添加成功。11.2触发器的创建执行“SELECT*FROMtb_department;”SQL语句查看“tb_department”数据表的数据，如下图所示，从运行结果可知tb_department数据表中已经插入了一条相应的记录，说明添加数据成功。11.2触发器的创建执行“SELECT*FROMtb_department_logs;”SQL语句查看“tb_department_logs”数据表的数据。如下图所示，从运行结果可知INSERT操作触发了触发器，并在“tb_department_logs”数据表中记录了相应信息。查看触发器03查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。可以查看当前数据库中所有的触发器、查看当前数据库的某个触发器和查看triggers表中的触发器信息。11.3.1查看当前数据库的所有触发器查看当前数据库的所有触发器的SQL语句如下所示。

说明：（1）在命令提示符中输入“SHOWTRIGGERS;”语句即可查看选择数据库中的所有触发器，但是，应用该查看语句存在一定弊端，即只能查询所有触发器的内容，并不能指定查看某个触发器的信息。这样一来，就会在用户查找指定触发器信息的时候带来极大不便。故推荐只在触发器数量较少的情况下应用“SHOWTRIGGERS”语句查询触发器基本信息；（2）在命令的后面添加“\G”，让显示的信息更有条理。在Windows系统的命令提示符工具下登录MySQL，选择“db_study”数据库作为当前使用的数据库，执行“SHOWTRIGGERS\G”语句查看当前数据库中存在的触发器，运行结果如下图所示，通过运行结果可以得出，当前数据库只存在一个名为“after_insert_deaprtment”的触发器，并且可以查看到该触发器的作用数据表是tb_department，触发条件是执行了INSERT操作，创建时间等详细信息。11.3.1查看当前数据库的所有触发器（2）在命令的后面添加“\G”，让显示的信息更有条理。在Windows系统的命令提示符工具下登录MySQL，选择“db_study”数据库作为当前使用的数据库，执行“SHOWTRIGGERS\G”语句查看当前数据库中存在的触发器，运行结果如下图所示，通过运行结果可以得出，当前数据库只存在一个名为“after_insert_deaprtment”的触发器，并且可以查看到该触发器的作用数据表是tb_department，触发条件是执行了INSERT操作，创建时间等详细信息。11.3.2查看当前数据库的某个触发器需要查看当前数据库的某个触发器的SQL语句如下所示。在Windows系统的命令提示符工具下登录MySQL，选择“db_study”数据库作为当前使用的数据库，执行“SHOWCREATETRIGGERafter_insert_deaprtment\G”语句查看当前数据库中存在的触发器，运行结果如下图所示，通过运行结果可以得出“after_insert_deaprtment”触发器的作用的数据表，触发条件，创建时间等详细信息。11.3.3查看triggers表中的触发器信息在Windows系统的命令提示符工具下登录MySQL，所有触发器的定义都存在该数据库的“TRIGGERS”表中。可以通过查询“TRIGGERS”数据表来查看数据库中所有触发器的详细信息，SQL语句如下所示。

说明：其中，information_schema是MySQL中默认存在的库，而TRIGGERS是数据库中用于记录触发器信息的数据表，通过SELECT语句查看触发器信息。11.3.3查看triggers表中的触发器信息运行上述SQL语句，得到如下图所示的结果，查看到当前数据库中“TRIGGERS”的触发器，并且可以查看到该触发器的作用的数据表，触发条件，创建时间等详细信息。删除触发器0411.4删除触发器触发器也是数据库对象，删除触发器可以通过DROP语句，语法格式如下：

说明：IFEXISTS的含义为：如果当前数据库中存在相应的触发器名称，则删除触发器；如果当前数据库中不存在相应的触发器，则忽略删除语句，不再执行删除触发器的操作。【案例11.2】删除after_insert_department触发器。删除after_insert_department触发器的SQL语句如下所示。11.4删除触发器运行上述SQL语句，运行结果如下图所示，触发器已经删除成功。执行“SHOWTRIGGERS\G”SQL语句，如下图所示，从运行结果可知触发器已经删除成功，当前不存在任何触发器了。课业任务0511.5

课业任务课业任务11-1保护系部表中人文与教育学院的地址信课业任务11-2保护等级表中学号信息不能被删除课业任务11-3使用Navicat工具创建触发器记录班级课业任务11-4使用Workbench工具管理触发器王小明想利用MySQL+Java开发一个数据库学习系统，在熟悉了数据的增删查改和存储过程的应用后，需要创建触发器来对一些操作进行限制，以此来增加数据的高可用性，现通过4个课业任务来完成。具体请见实验10常见错误及解决方案0611.6常见错误及解决方案11.6.1错误11-1语句结束符冲突11.6.2错误11-2切换结束标记无效具体请见实验1011.6.3错误11-3创建触发器重复选择题填空题判断题操作题（1）MySQL从哪个版本开始支持触发器（）。A.MySQL5.0.2B.MySQL5.7.2C.MySQL7.0.2D.MySQL8.0.30（2）MySQL触发器不能由哪个事件来触发某个操作（）。A.DELETEB.INSERTC.UPDATED.SELECTAD选择题填空题判断题操作题（3）下列哪些属于触发器的作用（）。A.安全性B.审计C.实现复杂的数据完整性规则D.实现复杂的级联操作（4）在MySQL查看当前数据库的所有触发器的SQL语句是（）。A.SHOWDATABASES;B.SHOWTRIGGER;C.DESCDATABASES;D.SHOWTRIGGERS;ABCDD选择题填空题判断题操作题（5）在MySQL删除触发器的SQL语句是（）。A.SELECTTRIGGERIFEXISTS触发器名称;B.DROPTRIGGERIFEXISTS触发器名称;C.DELETETRIGGERIFEXISTS触发器名称;D.UPDATETRIGGERIFEXISTS触发器名称;B选择题填空题判断题操作题（1）触发器的优点（）。（2）触发器的缺点（）。（3）触发器作用事件的触发时间（）。（4）MySQL中查看当前数据库的所有触发器的SQL语句（）。（5）MySQL中创建触发器时使用“DELIMITER$$”语句的作用（）。答案：（1）可以在特定的事件发生时自动执行一系列的操作，无需手动触发，提高了数据的一致性和完整性。（2）增加数据库的负载，每次触发事件都会执行触发器中的操作，可能影响数据库的性能。（3）BEFORE、AFTER和INSTEADOF。（4）SHOWTRIGGERS;（5）作用是改变语句的分隔符，使得可以在触发器中使用多条语句，而不受默认分隔符“;”的限制。判断题填空题选择题操作题（1）MySQL的触发器和存储过程不一样。（）（2）触发器可以在插入、删除或修改特定表中的数据时触发执。（）（3）触发器不具备精细和更复杂的数据控制能力，只能处理简单的操作。（）（4）工作表结构的变更，都可能会导致触发器出错。（）（5）BEFORE和AFTER是触发器的两种触发时间。（）√√√√×操作题选择题填空题判断题（1）创建触发器保护系部表中经济管理学院的地址信息不能被更改。CREATETRIGGERprotect_addressBEFOREUPDATEON系部表FOREACHROWBEGINIFNEW.学院名称='经济管理学院'THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='经济管理学院的地址信息不能被更改';ENDIF;END;操作题选择题填空题判断题（2）创建触发器保护等级表中课程id信息不能被删除。CREATETRIGGERprotect_course_idBEFOREDELETEON等级表FOREACHROWBEGINSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='课程id信息不