MySQL數(shù)據(jù)庫項目化教程課件 11.項目三-任務4-觸發(fā)器

項目三數(shù)據(jù)庫開發(fā)與維護任務四觸發(fā)器MySQL數(shù)據(jù)庫項目化教程任務四

觸發(fā)器任務4觸發(fā)器學習目標

專業(yè)能力1.認識觸發(fā)器；2.掌握創(chuàng)建觸發(fā)器方法。

方法能力1.

通過使用觸發(fā)器，提升數(shù)據(jù)庫完整性維護能力2.通過完成學習任務，提高解決實際問題的能力。

社會能力1..培養(yǎng)學生邏輯思維能力和分析問題、解決問題的能力；2.培養(yǎng)嚴謹?shù)墓ぷ髯黠L，增強信息安全意識和危機意識；3.培養(yǎng)學生運用數(shù)據(jù)庫管理系統(tǒng)解決實際問題的能力任務3：觸發(fā)器在圖書管理數(shù)據(jù)庫系統(tǒng)維護過程中，數(shù)據(jù)表中為了保證數(shù)據(jù)的完整性或執(zhí)行其他特殊規(guī)則，只有約束還不夠。項目組要求小明運用觸發(fā)器的功能，確保圖書借閱過程中，書籍狀態(tài)保持和真實情況一致。數(shù)據(jù)表中為了保證數(shù)據(jù)的完整性或執(zhí)行其他特殊規(guī)則，MySQL除了提供約束之外，還提供了另外一種機制：觸發(fā)器（trigger)。本次任務使用觸發(fā)器來完成書籍狀態(tài)更新的工作。知識學習觸發(fā)器（Trigger）同存儲過程和函數(shù)類似，MySQL中的觸發(fā)器也是存儲在系統(tǒng)內部的一段程序代碼，可以把它看作是一個特殊的存儲過程。所不同的是，觸發(fā)器無需人工調用，當程序滿足定義條件時就會被MySQL自動調用。這些條件可以稱為觸發(fā)事件，包括INSERT、UPDATE和DELETE操作觸發(fā)器常被用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性。例如，在開發(fā)圖書管理系統(tǒng)項目時，會遇到如下情況，當借出一本書后要更改book表中書架上狀態(tài)。就是在對表執(zhí)行某項操作后，需要自動進行一些處理。此時就可以使用觸發(fā)器處理數(shù)據(jù)庫對象，可以創(chuàng)建一個觸發(fā)器對象，每借出一本書，就執(zhí)行一次設置book_num=0，這樣可以保證每次書被借出后，書不在書架上，避免錯誤操作。觸發(fā)器有如下優(yōu)點：（1）自動執(zhí)行。觸發(fā)器在對表的數(shù)據(jù)作了任何修改之后立即被激活。（2）級聯(lián)更新。觸發(fā)器可以通過數(shù)據(jù)庫中的相關表進行層疊更改，這比直接把代碼寫在前臺的做法更安全合理。（3）強化約束。觸發(fā)器可以引用其它表中的列，能夠實現(xiàn)比CHECK約束更為復雜的約束。（4）跟蹤變化。觸發(fā)器可以阻止數(shù)據(jù)庫中未經許可的指定更新和變化。（5）強制業(yè)務邏輯。觸發(fā)器可用于執(zhí)行管理任務，并強制影響數(shù)據(jù)庫的復雜業(yè)務規(guī)則。知識學習1. 創(chuàng)建觸發(fā)器創(chuàng)建存儲過程可以使用CREATETRIGGER語句。CREATE

[DEFINER={'user'|CURRENT_USER}]

TRIGGERtrigger_nametrigger_timetrigger_eventONtable_nameFOREACHROW[trigger_order]trigger_body字段含義可能的值DEFINER=可選參數(shù)，指定創(chuàng)建者，默認為當前登錄用戶該觸發(fā)器將以此參數(shù)指定的用戶執(zhí)行，所以需要考慮權限問題；'root@%'CURRENT_USERtrigger_name觸發(fā)器名稱

trigger_time觸發(fā)時間，在某個事件之前還是之后；BEFORE、AFTERtrigger_event觸發(fā)事件，如插入時觸發(fā)、刪除時觸發(fā)；INSERT：插入操作觸發(fā)器，INSERT、LOADDATA、REPLACE時觸發(fā)；UPDATE：更新操作觸發(fā)器，UPDATE操作時觸發(fā)；DELETE：刪除操作觸發(fā)器，DELETE、REPLACE操作時觸發(fā)；INSERTUPDATEDELETEtable_name

觸發(fā)操作時間的表名；

trigger_order可選參數(shù)，如果定義了多個具有相同觸發(fā)事件和觸法時間的觸發(fā)器時，默認觸發(fā)順序與觸發(fā)器的創(chuàng)建順序一致，可以使用此參數(shù)來改變它們觸發(fā)順序。FOLLOWS：當前創(chuàng)建觸發(fā)器在現(xiàn)有觸發(fā)器之后激活；

PRECEDES：當前創(chuàng)建觸發(fā)器在現(xiàn)有觸發(fā)器之前激活；FOLLOWSPRECEDEStrigger_body觸發(fā)執(zhí)行的SQL語句內容，一般以begin開頭，end結尾begin…end知識學習在trigger_body中，我們可以使用NEW表示將要插入的新行，OLD表示將要刪除的舊行。通過OLD，NEW中獲取它們的字段內容，方便在觸發(fā)操作中使用，下表是對應事件是否支持OLD、NEW的對應關系：觸發(fā)事件OLDNEWINSERT×√DELETE√×UPDATE√√由于UPDATE相當于刪除舊行（OLD），然后插入新行（NEW），所以UPDATE同時支持OLD、NEW。知識學習【例1】創(chuàng)建一個觸發(fā)器，當刪除stuinfo數(shù)據(jù)庫中student表中某學生的信息時，同時將score表中與該學生有關的數(shù)據(jù)全部刪除。步驟一：創(chuàng)建一個名為stu_del的觸發(fā)器DELIMITER//CREATETRIGGERstu_delAFTERDELETEONstudentFOREACHROWBEGINDELETEFROMscoreWHEREsno=OLD.sno;END//右鍵點擊student表，打開設計表菜單，選中觸發(fā)器可見新創(chuàng)建的觸發(fā)器stu_del。知識學習步驟二：驗證觸發(fā)器的功能DELETEFROMstudentWHEREsno='2020010008';第一次執(zhí)行刪除student表中數(shù)據(jù)失敗，是由于score表有外鍵和student表相關聯(lián)，所以刪除相關外鍵后才能執(zhí)行刪除操作。成功刪除sno=‘2020010008’的學生信息后，觸發(fā)了觸發(fā)器stu_del，成功刪除了score=‘2020010008’的數(shù)據(jù)。知識學習2. 查看觸發(fā)器查看MySQL創(chuàng)建的觸發(fā)器有三種方式：方法一：通過information_schema.triggers表查看觸發(fā)器語法格式：select*frominformation_schema.triggers;方法二：查看當前數(shù)據(jù)庫的觸發(fā)器語法格式：showtriggers;方法三：查看指定數(shù)據(jù)庫的觸發(fā)器語法格式：showtriggersfrom數(shù)據(jù)庫名;3.刪除觸發(fā)器和其他數(shù)據(jù)庫對象一樣，使用DROP語句即可將觸發(fā)器從數(shù)據(jù)庫中刪除。語法格式：DROPTRIGGER觸發(fā)器名任務實施觸發(fā)器觸發(fā)器設計觸發(fā)器trigger_borrow,當某學生借書成功后，圖書表相應的圖書不在架上，狀態(tài)book_num變?yōu)?。案例1：分析：因為借書成功即borrow表插入數(shù)據(jù)，該觸發(fā)器的觸發(fā)事件是insert事件；改變書的書架狀態(tài)是在發(fā)生在insert事件之后，所以是after觸發(fā)器。步驟一：創(chuàng)建觸發(fā)器trigger_borrowDELIMITER//createtriggertrigger_borrowafterinsertonborrowforeachrowBEGINupdatebooksetbook_num=book_num-1wherebook_id=new.book_id;END//觸發(fā)器創(chuàng)建成功觸發(fā)器設計觸發(fā)器trigger_borrow,當某學生借書成功后，圖書表相應的圖書不在架上，狀態(tài)book_num變?yōu)?。案例1：步驟二：通過CALL調用儲存過程proc_borrow實現(xiàn)借書。CALLproc_borrow('2020010001',11);在borrow表中添加新的借書記錄驗證觸發(fā)器trigger_borrow同時，在book表中對應書籍的book_num變?yōu)?，即不在書架上。觸發(fā)器設計觸發(fā)器trigger_return,,當某學生成功還書后，圖書表相應的圖書顯示在架上，狀態(tài)book_num變?yōu)?。案例4：分析：因為還書成功即returns表插入數(shù)據(jù)，該觸發(fā)器的觸發(fā)事件是insert事件；改變書的書架狀態(tài)是在發(fā)生在insert事件之后，所以是after觸發(fā)器。步驟一：創(chuàng)建觸發(fā)器trigger_returnDELIMITER//createtriggertrigger_returnafterinsertonreturnsforeachrowBEGINupdatebooksetbook_num=book_num+1wherebook_id=(selectbook_idfromborrowwhereborrow_id=new.borrow_id);END//觸發(fā)器創(chuàng)建成功觸發(fā)器步驟二：通過CALL調用儲存過程proc_return實現(xiàn)還書CALLproc_r