《MySQL数据库应用案例教程》651-5（马洁）教案 第19课 触发器

PAGE12PAGE12PAGE11PAGE11

课题触发器课时2课时（90min）教学目标知识技能目标：（1）了解什么是触发器（2）掌握创建触发器的方法（3）掌握查看触发器的方法（4）掌握使用触发器的方法（5）掌握删除触发器的方法素质目标：（1）了解数据库前沿技术，紧跟时代发展（2）了解数据库的发展及主流国产数据库的应用，增强民族自豪感教学重难点教学重点：触发器的概念，创建、查看、使用、删除触发器的方法教学难点：创建和使用触发器的方法教学方法案例分析法、问答法、讨论法、讲授法、实践法教学用具电脑、投影仪、多媒体课件、教材教学设计第1节课：→→→传授新知（38min）第2节课：→传授新知（20min）→课堂实训（15min）→课堂小结（3min）→作业布置（2min）教学过程主要教学内容及步骤设计意图第一节课课前任务【教师】布置课前任务，和学生负责人取得联系，让其提醒同学通过文旌课堂APP或其他学习软件，预习本节课要学习的知识【学生】完成课前任务通过课前任务，使学生预习本节课要学习的知识，增加学生的学习兴趣考勤（2min）【教师】使用文旌课堂APP进行签到【学生】班干部报请假人员及原因培养学生的组织纪律性，掌握学生的出勤情况问题导入（5min）【教师】提出以下问题：触发器的作用是什么？·通过问题导入，引导学生主动思考，激发学生的学习兴趣传授新知（38min）【教师】通过学生的回答引入要讲的知识，讲解应用触发器、创建触发器的相关知识14.1为什么要应用触发器✈【教师】随机邀请学生回答以下问题：触发器和存储过程有什么区别？✈【学生】聆听、思考、回答✈【教师】总结学生的回答同存储过程和函数类似，MySQL中的触发器也是存储在系统内部的一段程序代码，可以把它看作是一个特殊的存储过程。所不同的是，触发器无需人工调用，当程序满足定义条件时就会被MySQL自动调用。这些条件可以称为触发事件，包括INSERT、UPDATE和DELETE操作。触发器常被用在数据库端确保数据的完整性。例如，在具体开发项目时，常会遇到如下情况，当在用户表中插入一个新用户后，用户总数必须要自动加1。就是在对表执行某项操作后，需要自动进行一些处理。此时就可以使用触发器处理数据库对象，可以创建一个触发器对象，每添加一条学生记录，就执行一次计算学生总数的操作，这样可以保证每次添加学生记录后，学生总数与学生记录数一致。14.2创建触发器创建触发器的语法形式如下：CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtable_nameFOREACHROWtrigger_body上述语句中，trigger_name表示触发器的名称。trigger_time表示触发器的触发时机，可以取值为BEFORE或者AFTER，BEFORE是在检查约束前触发，而AFTER是在检查约束后触发。trigger_event表示触发事件，可以是INSERT、UPDATE或DELETE。table_name表示建立触发器的表名。FOREACHROW表示数据表中任意一条记录满足触发条件都会激活触发器。trigger_body表示触发器激活后执行的SQL语句。【提示】触发器只能创建在永久表（PermanentTable）上，不能创建在临时表（TemporaryTable）上。从MySQL5.7.2开始，可以为一张表定义具有相同触发事件和触发时机的多个触发器。默认情况下，具有相同触发事件和触发时机的触发器按其创建顺序激活。本节按照触发器触发时机的不同，分为AFTER和BEFORE两种情况，详细介绍触发器的创建。14.2.1创建AFTER触发器✈【教师】组织学生扫码观看“认识并创建触发器”视频（详见教材），让学生对相关知识有一个大致了解AFTER触发器是指触发器监视的触发事件执行之后，再激活触发器，激活后所执行的操作无法影响触发器所监视的事件。以买家购买商品为例，首先向订单表中添加订单记录，再更新商品表中商品的数量，这种情况下无论商品是什么状态，订单都已经插入数据库。AFTER触发器可以根据所监视的事件分为三种，分别是INSERT型、UPDATE型和DELETE型，下面分别介绍。1．INSERT型为便于操作，创建触发器之前需要先创建两张表。【实例14-1】参照表14-1和表14-2创建两张表goods和orders。✈【教师】通过多媒体展示“商品表（goods）结构”和“订单表（orders）结构”表格，介绍相关知识表14-1商品表（goods）结构字段名数据类型注释idINT(11)商品编号nameVARCHAR商品名称numINT(11)商品库存表14-2订单表（orders）结构字段名数据类型注释oidINT(11)订单编号gidINT(11)购买商品编号amountINT(11)购买商品数量步骤1登录MySQL，并选择数据库“test_db”。步骤2分别执行以下SQL语句，创建两张表goods和orders。创建goods表：CREATETABLEgoods(idINT(11)PRIMARYKEYAUTO_INCREMENT,nameVARCHAR(30)UNIQUE,numINT(11)DEFAULT0);创建orders表：CREATETABLEorders(oidINT(11)PRIMARYKEY,gidINT(11),amountINT(11));步骤3执行以下SQL语句，向goods表中添加3条记录，完成表的创建。INSERTINTOgoods(id,name,num)VALUES(1,'果粒橙',10),(2,'三元酸奶',10),(3,'加多宝',10);如果没有为订单表创建触发器，每次买家下订单，系统都需要执行两步操作：第一步是向订单表中插入一条记录，第二步是更新商品表中商品的库存。如果为订单表创建触发器，就可以在向订单表插入记录的同时自动更新商品表中商品的库存。下面通过实例介绍触发器的创建和应用。【实例14-2】为订单表创建INSERT型触发器，并验证其应用。步骤1执行SQL语句，为订单表创建触发器。SQL语句及其执行结果如下：mysql>DELIMITER$$mysql>CREATETRIGGERtg1->AFTERINSERTONorders->FOREACHROW->BEGIN->UPDATEgoodsSETnum=num-1WHEREid=1;->END$$QueryOK,0rowsaffected(0.11sec)mysql>DELIMITER;步骤

2执行SQL语句，在订单表orders中插入一条记录。SQL语句及其执行结果如下：mysql>INSERTINTOorders(oid,gid,amount)VALUES(1,1,1);QueryOK,1rowaffected(0.03sec)步骤3执行SQL语句，查看goods表中数据。SQL语句及其执行结果如下：mysql>SELECT*FROMgoods;++++|id|name|num|++++|1|果粒橙|9||2|三元酸奶|10||3|加多宝|10|++++3rowsinset(0.02sec)由结果可以看出，goods表中果粒橙的库存变为9，说明在插入一条订单后，触发器自动执行更新操作，将果粒橙的数量减去1。以上实例中，由于触发器tg1里的num值和id值都是固定的，所以无论买家购买哪个商品，购买多少件商品，goods表中的变化都是第1件商品的数量减1。为使触发器更加符合实际需求，接下来重新创建触发器tg2，将订单中的id值和num值传到触发器中，使goods表根据实际情况进行更新。【实例14-3】为订单表重新创建触发器，使其更符合实际需要。步骤

1执行SQL语句，为订单表创建触发器tg2。SQL语句及其执行结果如下：mysql>DELIMITER$$mysql>CREATETRIGGERtg2->AFTERINSERTONorders->FOREACHROW->BEGIN->UPDATEgoodsSETnum=num-NEW.amountWHEREid=NEW.gid;->END$$QueryOK,0rowsaffected(0.06sec)mysql>DELIMITER;对于INSERT型触发器而言，新插入的行使用NEW表示，引用行中的字段值可以使用“NEW.字段名”。步骤

2激活触发器tg2之前，需要先把触发器tg1删除。执行以下SQL语句删除触发器tg1。DROPTRIGGERtg1;步骤3执行以下SQL语句，向orders表中插入一条新记录。INSERTINTOorders(oid,gid,amount)VALUES(2,2,3);步骤4执行SQL语句，查看goods表。SQL语句及其执行结果如下：mysql>SELECT*FROMgoods;++++|id|name|num|++++|1|果粒橙|9||2|三元酸奶|7||3|加多宝|10|++++3rowsinset(0.00sec)由结果可以看出，第2件商品的数量变为7，说明触发器执行更新操作所需的参数值会随着触发事件中插入值的改变而改变。2．UPDATE型依然以前面创建的商品表和订单表为例进行介绍。假设买家修改了订单中的商品购买数量，系统首先需要修改orders表中的订单记录，然后goods表中对应的商品库存先要恢复为原来的数量，接着再减去新订单中商品的数量。【实例14-4】创建UPDATE型触发器tg3，并验证其效果。步骤

1执行SQL语句，为订单表创建触发器tg3。SQL语句及其执行结果如下：mysql>DELIMITER$$mysql>CREATETRIGGERtg3->AFTERUPDATEONorders->FOREACHROW->BEGIN->UPDATEgoodsSETnum=num+OLD.amount-NEW.amountWHEREid=NEW.gid;->END$$QueryOK,0rowsaffected(0.07sec)mysql>DELIMITER;【提示】对于UPDATE型触发器而言，修改操作之前的记录使用OLD表示，引用此条记录中的字段值可以使用“OLD.字段名”；修改操作后的记录使用NEW表示，引用此条记录中的字段值可以使用“NEW.字段名”。步骤2执行以下SQL语句，修改orders表中的第2条记录。UPDATEordersSETamount=5WHEREoid=2;步骤3执行SQL语句，查看goods表中的记录。SQL语句及其执行结果如下：mysql>SELECT*FROMgoods;++++|id|name|num|++++|1|果粒橙|9||2|三元酸奶|5||3|加多宝|10|++++3rowsinset(0.00sec)由查询结果可以看出，当订单中第2条记录中第2种商品的购买数量修改为5后，goods表中第2种商品的库存修改为5，这说明触发器tg3已经生效。3．DELETE型依然以前面创建的商品表和订单表为例进行介绍。假设买家直接取消订单，系统要执行的操作是先删除orders表中的订单，然后goods表中对应的商品数量要恢复为原来的值。【实例14-5】为orders表创建DELETE型触发器tg4，并验证其效果。步骤1执行SQL语句，创建触发器tg4。SQL语句及其执行结果如下：mysql>DELIMITER$$mysql>CREATETRIGGERtg4->AFTERDELETEONorders->FOREACHROW->BEGIN->UPDATEgoodsSETnum=num+OLD.amountWHEREid=OLD.gid;->END$$QueryOK,0rowsaffected(0.06sec)mysql>DELIMITER;对于DELETE型触发器而言，被删除的一行记录使用OLD表示，引用此条记录中的字段值可以使用“OLD.字段名”。步骤2执行以下SQL语句，删除orders表中的第2条记录。DELETEFROMordersWHEREoid=2;步骤3执行SQL语句，查看goods表中的记录。SQL语句及其执行结果如下：mysql>SELECT*FROMgoods;++++|id|name|num|++++|1|果粒橙|9||2|三元酸奶|10||3|加多宝|10|++++3rowsinset(0.00sec)由结果可以看出，goods表中第2种商品的库存恢复为10，这说明触发器tg4已经生效。14.2.2创建BEFORE触发器✈【教师】组织学生扫码观看“创建BEFORE触发器”视频（详见教材），让学生对相关知识有一个大致了解BEFORE触发器是指触发器在所监视的触发事件执行之前激活，激活后执行的操作先于监视的事件，这样就有机会进行一些判断，或修改即将发生的操作。✈【教师】随机邀请学生回答以下问题：触发器有哪些类型？✈【学生】聆听、思考、回答✈【教师】总结学生的回答BEFORE触发器也可以根据监视事件分为三种，分别是INSERT型、UPDATE型和DELETE型，本节主要讲解INSERT型。依然以前面创建的商品表和订单表为例进行介绍。假设订单中某商品的数量大于goods表中所对应商品的总量，goods表中的商品库存会出现负数。为避免这类问题，可以创建BEFORE触发器，这种情况下，系统会先判断订单中商品的购买数量，如果大于库存，则抛出异常，终止操作。【实例14-6】为orders表创建BEFORE触发器tg5。步骤1执行SQL语句，创建触发器tg5。SQL语句及其执行结果如下：mysql>DELIMITER$$mysql>CREATETRIGGERtg5->BEFOREINSERTONorders->FOREACHROW->BEGIN->DECLAREmsgVARCHAR(200);->UPDATEgoodsSETnum=num-NEW.amountWHEREid=NEW.gidANDnum>=NEW.amount;->IFROW_COUNT()<>1THEN->SELECTCONCAT(name,'库存不足')INTOmsgFROMgoodsWHEREid=NEW.gid;->SIGNALSQLSTATE'TX000'SETMESSAGE_TEXT=msg;->ENDIF;->END$$QueryOK,0rowsaffected(0.16sec)mysql>DELIMITER;【提示】ROW_COUNT()函数用于记录更新操作影响的行数，如果其值不等于1，就说明订单中商品的数量大于库存数量，goods表没有更新，此时将执行下面的语句，将“商品名+库存不足”赋给变量msg。SIGNAL语句用于在存储程序（例如存储过程、存储函数、触发器或事件）中向调用者返回错误或警告条件。此外，它还提供对错误特征（错误编号，SQLSTATE值，消息）的控制。步骤2执行SQL语句，在orders表中新增一条订单记录。SQL语句及其执行结果如下：mysql>INSERTINTOorders(gid,amount)VALUES(3,20);ERROR1644(TX000):加多宝库存不足步骤3执行SQL语句，查看goods表，结果如下：mysql>SELECT*FROMgoods;++++|id|name|num|++++|1|果粒橙|9||2|三元酸奶|10||3|加多宝|10|++++3rowsinset(0.00sec)由查询结果可以看出，订单中商品的购买数量大于库存，订单不会被插入orders表，goods表中的商品库存也不会改变。14.2.3使用图形化工具创建触发器使用NavicatforMySQL可以非常方便地创建触发器，具体操作如下：步骤1使用NavicatforMySQL连接MySQL后，双击需要操作的数据库“test_db”。步骤2选中需要创建触发器的表orders，单击“设计表”按钮，然后选择“触发器”选项卡。步骤

3单击“添加触发器”按钮，填写触发器的名称及其特性，然后在“定义”编辑区填写触发器激活后需执行的SQL语句，最后单击“保存”按钮即可。……（详见教材）【学生】聆听、思考、记录通过教师的讲解和演示，使学生了解应用触发器、创建触发器的相关知识第二节课问题导入（5min）【教师】提出以下问题：如何查看创建好的触发器？【学生】思考、举手回答通过问题导入，引导学生主动思考，激发学生的学习兴趣传授新知（20min）【教师】通过学生的回答引入新知，介绍查看和删除触发器的相关知识14.3查看触发器✈【教师】组织学生扫码观看“查看和删除触发器”视频（详见教材），让学生对相关知识有一个大致了解在MySQL中，查看触发器有两种方法，一种是使用SHOWTRIGGERS语句，一种是在information_schema数据库的triggers表中查看触发器的详细信息。14.3.1查看触发器在MySQL中，对同一个表相同触发时机的相同触发事件，只能定义一个触发器。例如，对于某个表的不同字段的AFTER更新触发器，只能定义成一个触发器，在触发器中通过判断更新的字段进行相应的处理。所以在创建触发器之前，最好能够查看MySQL中是否已经存在该触发器。使用SHOWTRIGGERS语句可以查看MySQL中已经存在的触发器，基本语法形式如下：SHOWTRIGGERS\G【实例14-7】执行SQL语句，查看MySQL中已经存在的触发器，结果如下：mysql>SHOWTRIGGERS\G***************************1.row***************************Trigger:tg5Event:INSERTTable:ordersStatement:BEGINDECLAREmsgVARCHAR(200);UPDATEgoodsSETnum=num-NEW.amountWHEREid=NEW.gidANDnum>=NEW.amount;IFROW_COUNT()<>1THENSELECTCONCAT(name,'库存不足')INTOmsgFROMgoodsWHEREid=NEW.gid;SIGNALSQLSTATE'TX000'SETMESSAGE_TEXT=msg;ENDIF;ENDTiming:BEFORECreated:2018-06-1711:19:48.81sql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDefiner:root@localhostcharacter_set_client:gbkcollation_connection:gbk_chinese_ciDatabaseCollation:utf8_general_ci***************************2.row***************************Trigger:tg2Event:INSERTTable:ordersStatement:BEGINUPDATEgoodsSETnum=num-new.amountWHEREid=new.gid;ENDTiming:AFTERCreated:2018-06-1511:11:42.81sql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDefiner:root@localhostcharacter_set_client:gbkcollation_connection:gbk_chinese_ciDatabaseCollation:utf8_general_ci由执行结果可以看出，执行SHOWTRIGGERS语句后，前面定义的所有触发器的信息都显示出来，由于篇幅原因，此处只给出了前两条信息。查询结果中主要参数及其意义如下：Trigger：表示触发器名称。Event：表示触发器的激活事件，如INSERT，UPDATE或DELETE。Table：表示定义触发器的表。Statement：表示触发器体，即触发器激活时执行的语句。Timing：表示触发器的触发时机。值为BEFORE或AFTER。使用SHOWTRIGGERS语句不仅可以查看所有触发器，也可以查看某个表上创建的触发器，其基本语法格式如下：SHOWTRIGGERSFROMdb_nameLIKE'table_name'\G其中db_name表示数据库名，table_name表示表名。另外，如果用户需要精确查看某一个触发器，也可以使用SHOWTRIGGERS语句，其基本语法格式如下：SHOWTRIGGERSWHERE`TRIGGER`LIKE'trigger_name%'\G【实例14-8】执行SQL语句，查看触发器tg3，SQL语句及其执行结果如下：mysql>SHOWTRIGGERSWHERE`TRIGGER`LIKE'tg3%'\G***************************1.row***************************Trigger:tg3Event:UPDATETable:ordersStatement:BEGINUPDATEgoodsSETnum=num+OLD.amount-NEW.amountWHEREid=NEW.gid;ENDTiming:AFTERCreated:2018-06-1513:50:49.91sql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDefiner:root@localhostcharacter_set_client:gbkcollation_connection:gbk_chinese_ciDatabaseCollation:utf8_general_ci1rowinset(0.00sec)【提示】精确查看某一个触发器时，WHERE子句中的列名TRIGGER需要使用反引号“`”，该符号位于键盘左上角。14.3.2查看触发器的详细信息MySQL中所有触发器的定义都存储在系统数据库information_schema中的triggers表中，可以通过查询语句SELECT查看，具体语法形式如下：SELECT*FROMinformation_schema.triggersWHEREtrigger_name='tri_name';【实例14-9】通过SELECT语句查看触发器tg5，SQL语句及其执行结果如下：mysql>SELECT*FROMinformation_schema.triggersWHEREtrigger_name='tg5'\G***************************1.row***************************TRIGGER_CATALOG:defTRIGGER_SCHEMA:test_dbTRIGGER_NAME:tg5EVENT_MANIPULATION:INSERTEVENT_OBJECT_CATALOG:defEVENT_OBJECT_SCHEMA:test_dbEVENT_OBJECT_TABLE:ordersACTION_ORDER:1ACTION_CONDITION:NULLACTION_STATEMENT:BEGINDECLAREmsgVARCHAR(200);UPDATEgoodsSETnum=num-NEW.amountWHEREid=NEW.gidANDnum>=NEW.amount;IFROW_COUNT()<>1THENSELECTCONCAT(name,'库存不足')INTOmsgFROMgoodsWHEREid=NEW.gid;SIGNALSQLSTATE'TX000'SETMESSAGE_TEXT=msg;ENDIF;ENDACTION_ORIENTATION:ROWACTION_TIMING:BEFOREACTION_REFERENCE_OLD_TABLE:NULLACTION_REFERENCE_NEW_TABLE:NULLACTION_REFERENCE_OLD_ROW:OLDACTION_REFERENCE_NEW_ROW:NEWCREATED:2018-06-1711:19:48.81SQL_MODE:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDEFINER:root@localhostCHARACTER_SET_CLIENT:gbkCOLLATION_CONNECTION:gbk_chinese_ciDATABASE_COLLATION:utf8_general_ci1rowinset(0.03sec)其中的主要参数及其意义如下：TRIGGER_SCHEMA：表示触发器所属数据库。TRIGGER_NAME：表示触发器名。EVENT_MANIPULATION：表示触发器的激活事件。EVENT_OBJECT_TABLE：表示触发器所属数据表。ACTION_ORIENTATION：表示每条记录发生改变都会激活触发器。ACTION_TIMING：表示触发器执行的时机。CREATED：表示触发器创建时间。14.4删除触发器14.4.1删除触发器使用DROPTRIGGER语句可以删除MySQL中定义的触发器，基本语法形式如下：DROPTRIGGERdata_name.trigger_name;上述语句中，data_name表示数据库名，trigger_name表示触发器名。【实例14-10】执行SQL语句，删除触发器tg3，SQL语句及其执行结果如下：mysql>DROPTRIGGERtest_db.tg3;QueryOK,0rowsaffected(0.04sec)使用SHOWTRIGGERS语句查询触发器tg3，验证删除结果，SQL语句及其执行结果如下：mysql>SHOWTRIGGERSWHERE`TRIGGER`LIKE'tg3%'\GEmptyset(0.00sec)由结果可以看出，触发器tg3没有查询到，说明已经被删除。14.4.2使用图形化工具查看和删除触发器使用NavicatforMySQL可以非常方便地查看和删除触发器，具体操作如下：步骤

1使用NavicatforMySQL连接MySQ