《MySQL数据库应用与管理项目化教程》课件-项目八-1 触发器

项目八触发器任务目标任务一创建触发器任务二应用触发器任务三 查看触发器任务四 删除触发器教学目标能力目标◎能熟练查看触发器信息，按需建立和修改触发器；◎能应用触发器解决复杂的业务逻辑关联。知识目标◎了解触发器的用途和类型，熟悉触发器创建语法；◎掌握触发器中的NEW和OLD的应用。学习重点◎掌握触发器的查看、建立和测试；◎掌握NEW和OLD的应用。学习难点◎触发器的级联触发与测试。任务一创建触发器创建触发器

主要内容:触发器作用创建触发器测试触发器重点难点:触发器应用；触发器作用引入：商品销售会使商品销售量增加，而库存量必须相应地减少相同数量，通常为了实现这样的强制业务规则和数据完整性，我们把这种业务规则编写成触发器，通过触发器来实现复杂的业务规则的触发动作。触发器触发器是一种与数据表紧密结合的特殊的存储过程，用于保护表中的数据。当该数据表有插入、更改或删除事件发生时，所设置的相应触发器的触发操作也会自动执行。检查数据变动的一致性:在销售图书时，为保证图书资料表和销售销售表的图书信息变动一致，可通过建立一个销售销售表上建立UPDATE和INSERT触发器，可以使用关键字old和new来引用触发器中发生变化的记录内容。创建触发器查阅语法：官网文档：/doc/refman/8.0/en/create-trigger.html语法：CREATETRIGGERtrigger_name

{BEFORE|AFTER}

{INSERT|UPDATE|DELETE}ONtbl_nameFOREACHROW[{FOLLOWS|PRECEDES}other_trigger_name]trigger_body创建触发器关键字：NEW和OLD触发器中使用的NEW和OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。使用方法：NEW.col_nameOLD.col_nameOLD是只读的，而NEW则可以在触发器中使用SET赋值，这样不会再次触发触发器，造成循环调用。INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；AFTER触发器应用例1：建立一个item表的插入触发器mysql>DROPTRIGGERIFEXISTStg_goods_ins;mysql>delimiter//mysql>CREATETRIGGERtg_goods_ins

AFTERINSERTONitem

FOREACHROW

BEGIN

UPDATEgoods

SETamount=amount-NEW.quantity

WHEREid=NEW.goods_id;END//mysql>delimiter;mysql>SELECT*FROMitem;mysql>INSERTINTOitem(order_id,goods_id,supplier_id,quantity)VALUES(2,2,1,10);#测试插入触发器mysql>SELECT*FROMitem;mysql>SELECT*FROMgoods;AFTER触发器应用例2：建立一个item表的修改触发器mysql>DROPTRIGGERIFEXISTStg_goods_mod;mysql>delimiter//mysql>CREATETRIGGERtg_goods_mod AFTERUPDATEONitemFOREACHROWBEGINUPDATEgoodsSETamount=amount+(old.quantity-NEW.quantity)WHEREgoods_id=NEW.goods_id;END//mysql>delimiter;mysql>UPDATEitemSETquantity=quantity-5#测试修改触发器WHEREitem_id=6;mysql>SELECT*FROMitem;mysql>SELECT*FROMgoods;AFTER触发器应用例3：建立一个item表的删除触发器mysql>DROPTRIGGERIFEXISTStg_goods_del;mysql>delimiter//mysql>CREATETRIGGERtg_goods_delAFTERDELETEONitemFOREACHROWBEGINUPDATEgoodsSETamount=amount+old.quantityWHEREgoods_id=old.goods_id;END//mysql>delimiter;mysql>SELECT*FROMitem;mysql>SELECT*FROMgoods;mysql>DELETEFROMitemWHEREitem_id=6;BEFORE触发器应用例4：建立一个item表的BEFORE触发器mysql>SELECT*FROMorders;mysql>DROPTRIGGERIFEXISTStg_goods_ins2;mysql>delimiter//mysql>CREATETRIGGERtg_goods_ins2BEFOREINSERTONitemFOREACHROWBEGINIFNOTEXISTS(SELECT*FROMgoodsWHEREgoods_id=NEW.goods_idANDamount>NEW.quantity)THENBEGIN SIGNALSQLSTATE'H1001'SETMESSAGE_TEXT='库存不足';END;ELSEUPDATEgoodsSETamount=amount-NEW.quantityWHEREgoods_id=NEW.goods_id;ENDIF;END//mysql>delimiter;mysql>SELECT*FROMgoods;mysql>INSERTINTOitem(order_id,goods_id,supplier_id,quantity)VALUES(2,2,1,150);#测试插入触发器mysql>SELECT*FROMitem;任务实施按下列操作步骤完成db_shopping数据库触发器的建立和测试。1.选择db_shop数据库，执行语句如下：mysql>USEdb_shop;2.修改前面建立的tg_customer，当在订购表修改一条订购单时，若订购额大于0，则自动返还订购增加金额整除100的金额数到会员的余额中，执行语句如下：mysql>delimiter//mysql>CREATETRIGGertg_customerAFTERUPDATEONordersFOREACHROWBEGINDECLARENmdecimal(11,2);DECLAREOmdecimal(11,2);SETNm=NEW.amount;SETOm=OLD.amount;IFNm-Om>0THENUPDATEcustomerSETmenber_balance=menber_balance+(Nm-Om)/100WHEREid=NEW.customer_id;ENDIF;END//mysql>delimiter;3、测试触发器，执行语句如下：mysql>UPDATEordersSETamount=(SELECTSUM(total_price)FROMitemWHEREorder_id=4)WHEREid=4;查看触发器查阅语法：官网文档：/doc/refman/8.0/en/show.html使用定义和状态:SHOWTRIGGERS语句语法：SHOWTRIGGERS[{FROM|IN}db_name][LIKE'pattern'|WHEREexpr]mysql>SHOWTRIGGERSmysql>SHOWTRIGGERSFROMdb_shopLIKE'item%';查看触发器查看定义：SHOWCREATE语句mysql>SHOWCREATETRIGGERtrigger_name；查看tringgers表中的触发器信息语法：SELECT*FROMinformation_schema.triggers[WHEREtrigger_name='tri_name'];mysql>SELECT*FROMinformation_schema.triggers任务实施按下列操作完成db_shop数据库中触发器查看，从而了解触发器作用和定义。1.选择db_shop数据库，执行语句如下：mysql>USEdb_shop;2.查看系统中所有触发器，执行语句如下：mysql>SELECT*FROMinformation_schema.triggers\G3.查看当前数据库指定触发器的定义脚本，执行语句如下：mysql>SHOWCREATETRIGGERtg_goods_del;删除触发器语法：DROPTRIGGER[IFEXISTS][schema_name.]trigger_namemysql>DROPTRIGGERtest_db.tg3;考考你对item表，分别建立相应的插入、删除、修改触发器，联动修改goods中的商品数量（注：goods中没有商品数量，请添加此字段）对上面的触发器进行测试；任务实施按下列操作完成db_shopping数据库触发器的删除修改。1.选择db_shopping数据库，执行语句如下：mysql>USEdb_shopping;2.修改前面建立的tg_customer，当在订购表修改一条订购单时，按增减金额整除100的金额数更新到会员的余额，执行语句如下：mysql>delimiter//mysql>DROPTRIGGERIFEXISTStg_customer;mysql>CREATETRIGGertg_customerAFTERUPDATEONordersFOREACHROWBEGINDECLARENmdecimal(11,2);DECLAREOmdecimal(11,2);SETNm=NEW.amount;SE