數(shù)據(jù)庫技術(shù)與應(yīng)用 課件 項(xiàng)目9 學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫中的觸發(fā)器

項(xiàng)目9學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫中的觸發(fā)器數(shù)據(jù)庫技術(shù)與應(yīng)用掌握使用SQL語句創(chuàng)建觸發(fā)器、修改觸發(fā)器、刪除觸發(fā)器的方法掌握使用SQL語句創(chuàng)建INSERT型觸發(fā)器的方法掌握使用SQL語句創(chuàng)建UPDATE型觸發(fā)器的方法掌握使用SQL語句創(chuàng)建DELETE型觸發(fā)器的方法本章目標(biāo)一、使用SQL語句創(chuàng)建INSERT型觸發(fā)器1.觸發(fā)器概述觸發(fā)器可以看作是一種特殊類型的存儲過程，它與存儲過程的區(qū)別在于存儲過程使用時需要調(diào)用，而觸發(fā)器是在預(yù)先定義好的事件(如INSERT，DELETE等操作)發(fā)生時，才會被MySQL自動調(diào)用。創(chuàng)建觸發(fā)器時需要與數(shù)據(jù)表相關(guān)聯(lián)，當(dāng)表發(fā)生特定事件(如INSERT、DELETE等操作)時，就會自動執(zhí)行觸發(fā)器中提前預(yù)訂好的SQL代碼，實(shí)現(xiàn)插入數(shù)據(jù)前，強(qiáng)制檢驗(yàn)或轉(zhuǎn)換數(shù)據(jù)等操作，或是在觸發(fā)器中代碼執(zhí)行錯誤后，撤銷已執(zhí)行成功的操作，保證數(shù)據(jù)的安全。因此，不難看出觸發(fā)器在使用時的優(yōu)點(diǎn)和缺點(diǎn)，具體內(nèi)容如下(1)優(yōu)點(diǎn)

觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級聯(lián)無痕更改操作

保證數(shù)據(jù)安全，進(jìn)行安全校驗(yàn)。(2)缺點(diǎn)

觸發(fā)器的使用會影響數(shù)據(jù)庫的結(jié)構(gòu)，同時增加了維護(hù)的復(fù)雜程度

觸發(fā)器的無痕操作會造成數(shù)據(jù)在程序(如PHP、Java等)層面不可控。一、創(chuàng)建觸發(fā)器1.語法結(jié)構(gòu)觸發(fā)器必須創(chuàng)建在指定的數(shù)據(jù)表上，在MySQL中，創(chuàng)建觸發(fā)器語法如下：createtrigger觸發(fā)器名觸發(fā)時間觸發(fā)事件on表名foreachrowbegintrigger_stmtend;圖9-1Navicat工具中使用SQL語句創(chuàng)建觸發(fā)器子任務(wù)1：創(chuàng)建insert型觸發(fā)器TR1_T1創(chuàng)建insert類型觸發(fā)器TR1_T1，當(dāng)向成績表中插入一條成績信息時，成績采用百分制在0～100之間，當(dāng)輸入成績小于0時，則按0輸入，當(dāng)輸入成績大于100，則按100輸入。并執(zhí)行實(shí)現(xiàn)查詢的操作。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用圖9-2Navicat工具中使用SQL語句觸發(fā)觸發(fā)器子任務(wù)1：創(chuàng)建insert型觸發(fā)器TR1_T1根據(jù)要求，觸發(fā)器必須在數(shù)據(jù)插入表之前完成成績的控制，所以使用beforeinsert類型觸發(fā)器。當(dāng)向成績表中插入數(shù)據(jù)時，觸發(fā)器自動觸發(fā)，完成成績的控制。執(zhí)行以下語句將觸發(fā)該觸發(fā)器。運(yùn)行效果如圖9-2所示。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用圖9-3Navicat工具中使用SQL語句查詢成績表子任務(wù)1：創(chuàng)建insert型觸發(fā)器TR1_T1打開如圖9-3所示界面，通過查詢語句發(fā)現(xiàn)存入數(shù)據(jù)庫的成績是100，而不是120。運(yùn)行效果如圖9-3所示。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用圖9-4Navicat工具中使用SQL語句創(chuàng)建觸發(fā)器子任務(wù)2：創(chuàng)建insert型觸發(fā)器TR1_T2創(chuàng)建insert類型觸發(fā)器TR1_T2，增加一條學(xué)生記錄時，需要檢查性別是否符合范圍要求。并執(zhí)行插入錯誤驗(yàn)證觸發(fā)器。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用圖9-5Navicat工具中使用SQL語句觸發(fā)器生效子任務(wù)2：創(chuàng)建insert型觸發(fā)器TR1_T2根據(jù)要求，觸發(fā)器必須在數(shù)據(jù)插入表之前完成數(shù)據(jù)的控制，所以使用beforeinsert類型觸發(fā)器。當(dāng)向?qū)W生表中插入數(shù)據(jù)時，觸發(fā)器自動觸發(fā)，驗(yàn)證性別的輸入。執(zhí)行以下語句將觸發(fā)該觸發(fā)器。運(yùn)行效果如圖9-5所示。一、使用SQL語句創(chuàng)建無參的存儲過程并調(diào)用1.MySQL創(chuàng)建多個觸發(fā)器如何在MySQL中為相同的事件和操作時間創(chuàng)建多個觸發(fā)器。他與MySQL5.7.2+版本相關(guān)。如果你有一個較舊版本的MySQL，部分的語句將不起作用。在MySQL5.7.2版之前，您只能為表中的事件創(chuàng)建一個觸發(fā)器，例如，您只能為BEFOREUPDATE或AFTERUPDATE事件創(chuàng)建一個觸發(fā)器。MySQL5.7.2+解除了這一限制，允許您為表中的相同事件和操作時間創(chuàng)建多個觸發(fā)器。事件發(fā)生時，觸發(fā)器將按順序激活。創(chuàng)建第一個觸發(fā)器的語法保持不變。如果您在表中有相同事件的多個觸發(fā)器，MySQL將按創(chuàng)建順序調(diào)用觸發(fā)器。要更改觸發(fā)器的順序，您需要指定FOLLOWS或PRECEDES在FOREACHROW子句之后。FOLLOWS：選項(xiàng)允許在現(xiàn)有觸發(fā)器之后激活新觸發(fā)器。PRECEDES：選項(xiàng)允許在現(xiàn)有觸發(fā)器之前激活新觸發(fā)器。二、

使用SQL語句創(chuàng)建UPDATE型觸發(fā)器2.創(chuàng)建多個觸發(fā)器語法DELIMITER$$CREATETRIGGERtrigger_name[BEFORE|AFTER][INSERT|UPDATE|DELETE]ONtable_nameFOREACHROW[FOLLOWS|PRECEDES]existing_trigger_nameBEGIN…END$$DELIMITER;二、

使用SQL語句創(chuàng)建UPDATE型觸發(fā)器圖9-5Navicat工具中使用SQL語句創(chuàng)建觸發(fā)器子任務(wù)1：創(chuàng)建update型觸發(fā)器TR2_T1創(chuàng)建update型觸發(fā)器TR2_T1，當(dāng)修改一個記錄時，確保此記錄的成績(score)在0～100分之間。并執(zhí)行修改操作錯誤驗(yàn)證觸發(fā)器。二、

使用SQL語句創(chuàng)建UPDATE型觸發(fā)器圖9-6Navicat工具中使用SQL語句觸發(fā)器生效子任務(wù)1：創(chuàng)建update型觸發(fā)器TR2_T1根據(jù)要求，觸發(fā)器必須在數(shù)據(jù)更新表之后完成成績的控制，所以使用AFTERUPDATE類型觸發(fā)器。當(dāng)向成績表中修改數(shù)據(jù)時，觸發(fā)器自動觸發(fā)，完成成績的控制。執(zhí)行以下語句將觸發(fā)該觸發(fā)器。運(yùn)行效果如圖9-6所示。二、

使用SQL語句創(chuàng)建UPDATE型觸發(fā)器圖9-7Navicat工具中使用SQL語句創(chuàng)建觸發(fā)器子任務(wù)2：創(chuàng)建update型觸發(fā)器TR2_T2創(chuàng)建update型觸發(fā)器TR2_T2，在學(xué)生表Student中定義一個觸發(fā)器，保證修改學(xué)生的出生日期要大于學(xué)生的注冊日期。并執(zhí)行修改操作驗(yàn)證觸發(fā)器。二、

使用SQL語句創(chuàng)建UPDATE型觸發(fā)器圖9-8Navicat工具中使用SQL語句觸發(fā)器生效子任務(wù)2：創(chuàng)建update型觸發(fā)器TR2_T2根據(jù)要求，觸發(fā)器必須在數(shù)據(jù)更新表之后完成成績的控制，所以使用AFTERUPDATE類型觸發(fā)器。當(dāng)向?qū)W生表中修改數(shù)據(jù)時，觸發(fā)器自動觸發(fā)，完成出生日期的控制。執(zhí)行以下語句將觸發(fā)該觸發(fā)器。運(yùn)行效果如圖9-8所示。二、

使用SQL語句創(chuàng)建UPDATE型觸發(fā)器查看觸發(fā)器如果想通過語句查看數(shù)據(jù)庫中已經(jīng)存在的觸發(fā)器的信息，可以采用兩種方法：一種是利用SHOWTRIGGERS語句查看觸發(fā)器，另一種是利用SELECT語句查看數(shù)據(jù)庫information_schema下數(shù)據(jù)表triggers中的觸發(fā)器數(shù)據(jù)。利用SHOWTRIGGERS語句查看觸發(fā)器信息的語法格式如下。SHOWTRIGGERS;在MSQL中，觸發(fā)器信息都保存在數(shù)據(jù)庫infomationschena下的數(shù)據(jù)表triggers中，可以通過SELECT語句查看該數(shù)據(jù)表獲取觸發(fā)器信息。通過triggers數(shù)據(jù)表查動發(fā)器的語法格式如下。SELECT*FROMinformation_schema.triggers[MHEREtrigger_name=’觸發(fā)器名稱’]在上述語法格式中，可以通過WHERE子句指定觸發(fā)器的名稱，如果不指定觸發(fā)器名稱，則會查詢出information_schema數(shù)據(jù)庫中所有已經(jīng)存在的觸發(fā)器信息。三、使用SQL語句創(chuàng)建DELETE型觸發(fā)器圖9-9Navicat工具中使用SQL語句創(chuàng)建觸發(fā)器子任務(wù)1：創(chuàng)建delete型觸發(fā)器TR3_T1在學(xué)生表Student中創(chuàng)建delete型觸發(fā)器TR3_T1，當(dāng)刪除學(xué)生表Student中學(xué)生時，自動刪除成績表studing中相應(yīng)學(xué)生的選課記錄。三、使用SQL語句創(chuàng)建DELETE型觸發(fā)器圖9-10Navicat工具中使用SQL語句觸發(fā)器生效子任務(wù)1：創(chuàng)建delete型觸發(fā)器TR3_T1根據(jù)要求，當(dāng)刪除學(xué)生表student中學(xué)生時，觸發(fā)器自動觸發(fā)，自動刪除成績表studing中相應(yīng)學(xué)生的選課記錄，完成成績的控制。執(zhí)行以下語句將觸發(fā)該觸發(fā)器。運(yùn)行效果如圖9-10所示。三、使用SQL語句創(chuàng)建DELETE型觸發(fā)器圖9-11Navicat工具中顯示student表子任務(wù)1：創(chuàng)建delete型觸發(fā)器TR3_T1刪除之后student表和studing表都沒有20211004。查詢結(jié)果如圖9-11和9-12所示。三、使用SQL語句創(chuàng)建DELETE型觸發(fā)器圖9-12Navicat工具中顯示studing表圖9-13Navicat工具中使用SQL語句創(chuàng)建新表子任務(wù)2：創(chuàng)建delete型觸發(fā)器TR3_T2創(chuàng)建DELETE型觸發(fā)器TR3_T2，技術(shù)人員想要在刪除學(xué)生信息后，自動將刪除的學(xué)生信息添加在其他數(shù)據(jù)表以防后續(xù)需要查詢被刪除的學(xué)生信息，具體操作如下。三、使用SQL語句創(chuàng)建DELETE型觸發(fā)器圖9-14Navicat工具中使用SQL語句創(chuàng)建觸發(fā)器子任務(wù)2：創(chuàng)建delete型觸發(fā)器TR3_T2接著在學(xué)生表student中創(chuàng)建觸發(fā)器。當(dāng)刪除學(xué)生表的數(shù)據(jù)后，觸發(fā)該觸發(fā)器，并且在觸發(fā)器的觸發(fā)程序中將被刪除的學(xué)生添加到數(shù)據(jù)表new_student中，具體SQL語句及運(yùn)行效果如圖9-14所示。三、使用SQL語句創(chuàng)建DELETE型觸發(fā)器圖9-15Navicat工具中使用SQL語句刪除記錄子任務(wù)2：創(chuàng)建delete型觸發(fā)器TR3_T2觸發(fā)器創(chuàng)建成功后，會根據(jù)觸發(fā)時機(jī)觸發(fā)事件，技術(shù)人員需要刪除員工表學(xué)號’20211004’中的一條學(xué)生記錄，具體SQL語句及運(yùn)行效果如圖9-15所示。并且想要在刪除操作后查看新表new_studeng中的記錄，以驗(yàn)證觸發(fā)器是否生效，具體SQL語句及運(yùn)行效果如圖9-16所示。三、使用SQL語句創(chuàng)建DELETE型觸發(fā)器圖9-16Navicat工具中使用SQL語句查詢記錄刪除觸發(fā)器當(dāng)創(chuàng)建的觸發(fā)器不再符合當(dāng)前需求時，可以將它刪除。刪除觸發(fā)器的操作很簡單，只需要使用MySQL提供的DROPTRIGGER語句即可。DROPTRIGGER語句的基本語法格式如下。DROPTRIGGER[IFEXISTS][數(shù)據(jù)庫名.]觸發(fā)器名;在上述語法格式中，利用“數(shù)據(jù)庫名，觸發(fā)器名”方式可以刪除指定數(shù)據(jù)庫下的觸發(fā)器，當(dāng)省略“數(shù)據(jù)庫名.”時，則刪除當(dāng)前選擇的數(shù)據(jù)庫下的觸發(fā)器。四、使用SQL語句刪除觸發(fā)器子任務(wù)1：使用SQL語句查看觸發(fā)器通過語句查看數(shù)據(jù)庫中已經(jīng)存在的觸發(fā)器的信息，可以采用兩種方法。方法1：打開如圖9-17所示界面，打開“hn”節(jié)點(diǎn)，點(diǎn)擊“新建查詢”按鈕，新建查詢窗口，在該窗口中輸入以下語句查看觸發(fā)器。四、使用SQL語句刪除觸發(fā)器圖9-17Navicat工具中使用SQL語句查看觸發(fā)器方法2：打開如圖9-18所示界面，打開“hn”節(jié)點(diǎn)，點(diǎn)擊“新建查詢”按鈕，新建查詢窗口，在該窗口中輸入以下語句通過WHERE子句指定觸發(fā)器的名稱查看觸發(fā)器。四、使用SQL語句刪除觸發(fā)器圖9-18Navicat工具中使用SQL語句查看觸發(fā)器子任務(wù)2：使用SQL語句刪除觸發(fā)器在一次學(xué)生成績管理系統(tǒng)升級之后，技術(shù)人員覺得觸發(fā)器TR1_T1的使用意義不大，想要刪除學(xué)生成績管理系統(tǒng)中的觸發(fā)器TR1_T1。具體SQL語句及執(zhí)行結(jié)果如下：步驟：打開如圖9-19所示界面，打開“hn”節(jié)點(diǎn)，點(diǎn)擊“新建查詢”按鈕，新建查詢窗口，在該窗口中輸入以下語句刪除觸發(fā)器。四、使用SQL語句刪除觸發(fā)器圖9-19Navicat工具中使用SQL語句刪除觸發(fā)器上述語句執(zhí)行結(jié)果的信息可以得出，刪除語句成功執(zhí)行，