實(shí)訓(xùn)案例名稱：觸發(fā)器一、任務(wù)介紹觸發(fā)器是一種與表有關(guān)的操作對(duì)象，當(dāng)表上出現(xiàn)指定事件（INSERT、UPDATE、DELETE）時(shí)，會(huì)調(diào)用觸發(fā)器對(duì)象，執(zhí)行觸發(fā)器的操作。二、實(shí)現(xiàn)步驟1.創(chuàng)建觸發(fā)器#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>useshopDatabasechanged#創(chuàng)建用戶信息表user_infomysql>CREATETABLEuser_info(idintnotnullAUTO_INCREMENT,namevarchar(255),sexvarchar(10),primarykey(id));QueryOK,0rowsaffected(1.23sec)#創(chuàng)建統(tǒng)計(jì)表statmysql>CREATETABLEstat(idintnotnullAUTO_INCREMENT,numint,primarykey(id));QueryOK,0rowsaffected(0.34sec)#向統(tǒng)計(jì)表stat中插入一條記錄，默認(rèn)值為0mysql>INSERTINTOstatVALUES(1,0);QueryOK,1rowaffected(0.00sec)#修改結(jié)束符$$mysql>DELIMITER$$#創(chuàng)建一個(gè)觸發(fā)器來計(jì)算用戶的數(shù)量，統(tǒng)計(jì)數(shù)量的值放置在統(tǒng)計(jì)表stat的數(shù)量字段中mysql>CREATETRIGGERcpuNumAFTERINSERTONuser_infoFOREACHROWBEGINUPDATEstatsetnum=num+1WHEREid=1;END$$QueryOK,0rowsaffected(0.18sec)#修改結(jié)束符mysql>DELIMITER;#查看統(tǒng)計(jì)表stat中的數(shù)量字段mysql>SELECT*FROMstat;+----+------+|id|num|+----+------+|1|0|+----+------+1rowinset(0.00sec)#向用戶信息表user_info中插入一條記錄mysql>INSERTINTOuser_infoVALUES(1,'小明','男');QueryOK,1rowaffected(0.01sec)#統(tǒng)計(jì)表stat中的數(shù)量加1mysql>SELECT*FROMstat;+----+------+|id|num|+----+------+|1|1|+----+------+1rowinset(0.00sec)#再向用戶信息表user_info中插入一條記錄mysql>INSERTINTOuser_infoVALUES(2,'小紅','女');QueryOK,1rowaffected(0.00sec)#統(tǒng)計(jì)表stat中的數(shù)量再加1mysql>SELECT*FROMstat;+----+------+|id|num|+----+------+|1|2|+----+------+1rowinset(0.00sec)mysql>2.使用NEW和OLD關(guān)鍵字#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshopDatabasechanged#查詢員工表employeemysql>SELECT*FROMemployee;+----+------+------+------+--------+|id|name|sex|age|deptId|+----+------+------+------+--------+|1|張明|男|30|1||2|孫浩|男|25|1||4|趙穎|女|32|2||5|劉帥|男|28|2||6|劉濤|NULL|NULL|NULL|+----+------+------+------+--------+5rowsinset(0.00sec)#查詢部門表departmentmysql>SELECT*FROMdepartment;+----+------------+---------+|id|deptName|manager|+----+------------+---------+|1|軟件開發(fā)部|李姝||2|人力資源部|吳剛|+----+------------+---------+2rowsinset(0.00sec)#修改結(jié)束符為$$mysql>DELIMITER$$#創(chuàng)建按部門刪除員工的觸發(fā)器，刪除部門后，觸發(fā)器會(huì)自動(dòng)刪除這個(gè)部門中的員工mysql>CREATETRIGGERdeleteEmpAFTERDELETEONdepartmentFOREACHROWBEGINDELETEFROMemployeeWHEREdeptId=OLD.id;END$$QueryOK,0rowsaffected(0.21sec)#修改結(jié)束符為$$mysql>DELIMITER;#刪除部門id等于1的部門時(shí)，觸發(fā)器會(huì)刪除這個(gè)部門中的員工mysql>DELETEFROMdeptWHEREid=1;QueryOK,1rowaffected(0.08sec)#查詢部門表中的數(shù)據(jù)mysql>SELECT*FROMdepartment;+----+---------------+----------+|id|deptName|manager|+----+---------------+----------+|2|人力資源部|吳剛|+----+---------------+----------+1rowinset(0.00sec)#查詢員工表中的數(shù)據(jù)，可以看到deptId等于1的員工被刪除了mysql>SELECT*FROMemployee;+----+-------+------+------+--------+|id|name|sex|age|deptId|+----+-------+------+------+--------+|3|張靜|女|28|2||4|趙穎|女|32|2||5|劉帥|男|28|2||6|劉濤|NULL|NULL|NULL|+----+-------+------+------+--------+3rowsinset(0.00sec)mysql>3.查看和刪除觸發(fā)器（1）查看觸發(fā)器。查看觸發(fā)器和查看數(shù)據(jù)庫(kù)表一樣，通過SHOWTRIGGERS就可以查看觸發(fā)器；mysql>SHOWTRIGGERS\G***************************1.row***************************Trigger:deleteEmp#觸發(fā)器名稱Event:DELETE#觸發(fā)器事件Table:department#觸發(fā)的表Statement:BEGIN#執(zhí)行的SQL語(yǔ)句DELETEFROMemployeeWHEREdeptId=OLD.id;ENDTiming:AFTER#執(zhí)行的時(shí)機(jī)Created:2023-11-0621:45:04.23#創(chuàng)建時(shí)間sql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDefiner:root@localhost#定義的用戶character_set_client:utf8#編碼collation_connection:utf8_general_ciDatabaseCollation:utf8_general_ci（2）刪除觸發(fā)器。刪除觸發(fā)器就像刪除數(shù)據(jù)庫(kù)表一樣簡(jiǎn)單，使用關(guān)鍵字TRIGGER來標(biāo)識(shí)觸發(fā)器，然后使用DROP操作刪除指定名稱的觸發(fā)器。DROPTRIGGERtrigger_name;示例如下。mysql>DROPTRIGGERcpuNum;4.INSERT型觸發(fā)器#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshopDatabasechanged#查詢員工表employeemysql>SELECT*FROMemployee;+----+------+------+|id|name|salary|+----+------+------+|1|張明|3000||2|孫浩|5500||4|趙穎|8200||5|劉帥|9800||6|劉濤|6600|+----+------+------+5rowsinset(0.00sec)#刪除員工表employee的數(shù)據(jù)mysql>DELETEFROMemployee;#查詢員工表employee的數(shù)據(jù)，目前沒有數(shù)據(jù)mysql>SELECT*FROMemployee;Emptyset(0.00sec);QueryOK,0rowsaffected(0.36sec)#創(chuàng)建記錄表record，記錄工資低于3500元的員工mysql>CREATETABLErecord(idintnotnullAUTO_INCREMENT,namevarchar(255),salaryint,primarykey(id));QueryOK,0rowsaffected(0.32sec)#查詢記錄表record的數(shù)據(jù)，目前沒有數(shù)據(jù)mysql>SELECT*FROMrecord;Emptyset(0.00sec)#修改結(jié)束符$$mysql>DELIMITER$$#mysql>CREATETRIGGERaddRecordAFTERINSERTONemployeeFOREACHROWBEGINIF(NEW.salary<3500)THENINSERTINTOrecordVALUES(NEW.id,NEW.name,NEW.salary);ENDIF;END$$QueryOK,0rowsaffected(0.17sec)#修改結(jié)束符mysql>DELIMITER;#向員工表employee中插入一條工資低于3500元的員工記錄mysql>INSERTINTOemployeeVALUES(11,'小明',2800);QueryOK,1rowaffected(0.00sec)#查詢員工表employee的數(shù)據(jù)，可以看到插入成功mysql>SELECT*FROMemployee;+----+--------+--------+|id|name

|salary|+----+--------+--------+|11|小明

|2800|+----+--------+--------+1rowinset(0.00sec)#查詢記錄表record的數(shù)據(jù)，可以看到插入了一條工資低于3500元的員工記錄mysql>SELECT*FROMrecord;+----+--------+--------+|id|name|salary|+----+--------+--------+|11|小明|2800|+----+--------+--------+1rowinset(0.00sec)#再向員工表employee中插入一條工資高于3500元的員工記錄mysql>INSERTINTOemployeeVALUES(12,'小紅',5600);QueryOK,1rowaffected(0.00sec)#查詢員工表employee的數(shù)據(jù)，可以看到插入成功mysql>SELECT*FROMemployee;+----+--------+--------+|id|name|salary|+----+--------+--------+|11|小明|2800||12|小紅|5600|+----+--------+--------+2rowsinset(0.00sec)#查詢記錄表record的數(shù)據(jù)，工資高于3500元的員工記錄沒有插入mysql>SELECT*FROMrecord;+----+--------+--------+|id|name|salary|+----+--------+--------+|11|小明|2800|+----+--------+--------+1rowinset(0.00sec)mysql>5.UPDATE型觸發(fā)器#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456#使用shop數(shù)據(jù)庫(kù)mysql>USEshopDatabasechanged#查看員工表employee的數(shù)據(jù)mysql>SELECT*FROMemployee;+----+--------+--------+|id|name|salary|+----+--------+--------+|1|小明|2800||2|小紅|5600|+----+--------+--------+2rowsinset(0.00sec)#查看記錄表record的數(shù)據(jù)mysql>SELECT*FROMrecord;+----+--------+--------+|id|name|salary|+----+--------+--------+|1|小明|2800|+----+--------+--------+1rowinset(0.00sec)#修改結(jié)束符mysql>DELIMITER$$/*創(chuàng)建UPDATE型觸發(fā)器，在員工表employee中進(jìn)行修改，工資大于3500元時(shí)要?jiǎng)h除記錄表record中相應(yīng)的數(shù)據(jù)，否則插入或者修改記錄表record中的數(shù)據(jù)，REPLACE語(yǔ)句可以用于插入或者更新記錄*/mysql>CREATETRIGGERupdateRecordAFTERUPDATEONemployeeFOREACHROWBEGINIF(NEW.salary>3500)THENDELETEFROMrecordWHEREid=NEW.id;ELSEREPLACEINTOrecordVALUES(NEW.id,NEW.name,NEW.salary);ENDIF;END$$QueryOK,0rowsaffected(0.16sec)#修改結(jié)束符mysql>DELIMITER;#mysql>UPDATEemployeeSETsalary=6000WHEREid=1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0#修改成功mysql>SELECT*FROMemployee;+----+--------+--------+|id|name|salary|+----+--------+--------+|1|小明|6000||2|小紅|5600|+----+--------+--------+2rowsinset(0.00sec)#工資大于3500元，在記錄表record中會(huì)刪除員工id為1的數(shù)據(jù)mysql>SELECT*FROMrecord;Emptyset(0.00sec)#將員工id為2的員工的工資從5600元改為2000元mysql>UPDATEemployeeSETsalary=2000WHEREid=2;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0#修改成功mysql>SELECT*FROMemployee;+----+-------+---------+|id|name|salary|+----+-------+---------+|1|小明|6000||2|小紅|2000|+----+-------+---------+2rowsinset(0.00sec)#記錄表record中插入員工id為2的員工記錄mysql>SELECT*FROMrecord;+----+-------+---------+|id|name|salary|+----+-------+---------+|2|小紅|2000|+----+-------+---------+1rowinset(0.00sec)#中的數(shù)據(jù)mysql>UPDATEemployeeSETsalary=2500WHEREid=2;QueryOK,1rowaffected(0.06sec)Rowsmatched:1Changed:1Warnings:0#修改成功mysql>SELECT*FROMemployee;+----+-------+---------+|id|name|salary|+----+-------+---------+|1|小明|6000||2|小紅|2500|+----+-------+---------+2rowsinset(0.00sec)#修改成功mysql>SELECT*FROMrecord;+----+--------+--------+|id|name|salary|+----+--------+--------+|2|小紅|2500|+----+--------+--------+1rowinset(0.00sec)mysql>6.DELETE型觸發(fā)器#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshopDatabasechanged#查詢員工表employee的數(shù)據(jù)mysql>SELECT*FROMemployee;+----+--------+--------+|id|name|salary|+----+--------+--------+|1|小明|6000||2|小紅|2500|+----+--------+--------+2rowsinset(0.00sec)#查詢記錄表re