各種-教學(xué)新07mysql觸發(fā)器與派生表

MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程

之視圖觸發(fā)器123

本章首先講解了視圖以及觸發(fā)器的管理及使用，然后結(jié)合“選課系統(tǒng)”分別介紹視圖以及觸發(fā)器在該系統(tǒng)中的應(yīng)用。4觸發(fā)器視圖臨時表派生表（DerivedTable）5視圖、子查詢、臨時表、派生表內(nèi)容一覽123觸發(fā)器主要用于監(jiān)視某個表的insert、update以及delete等更新操作，這些操作可以分別激活該表的insert、update或者delete類型的觸發(fā)程序運(yùn)行，從而實(shí)現(xiàn)數(shù)據(jù)的自動維護(hù)。使用觸發(fā)器實(shí)現(xiàn)檢查約束準(zhǔn)備工作使用觸發(fā)器維護(hù)冗余數(shù)據(jù)45使用觸發(fā)器模擬外鍵級聯(lián)選項(xiàng)查看觸發(fā)器的定義

觸發(fā)器67觸發(fā)器主要用于監(jiān)視某個表的insert、update以及delete等更新操作，這些操作可以分別激活該表的insert、update或者delete類型的觸發(fā)程序運(yùn)行，從而實(shí)現(xiàn)數(shù)據(jù)的自動維護(hù)。刪除觸發(fā)器使用觸發(fā)器的注意事項(xiàng)

觸發(fā)器

觸發(fā)器使用createtrigger語句可以創(chuàng)建一個觸發(fā)器，語法格式如下。createtrigger觸發(fā)器名觸發(fā)時間觸發(fā)事件on表名foreachrowbegin觸發(fā)程序end

準(zhǔn)備工作MySQL的觸發(fā)事件有三種：insert：將新記錄插入表時激活觸發(fā)程序，例如通過insert、loaddata和replace語句，可以激活觸發(fā)程序運(yùn)行。

準(zhǔn)備工作MySQL的觸發(fā)事件有三種：update：更改某一行記錄時激活觸發(fā)程序，例如通過update語句，可以激活觸發(fā)程序運(yùn)行。delete：從表中刪除某一行記錄時激活觸發(fā)程序，例如通過delete和replace語句，可以激活觸發(fā)程序運(yùn)行。

準(zhǔn)備工作觸發(fā)器的觸發(fā)時間有兩種：before與after。before表示在觸發(fā)事件發(fā)生之前執(zhí)行觸發(fā)程序。after表示在觸發(fā)事件發(fā)生之后執(zhí)行觸發(fā)器。因此嚴(yán)格意義上講一個數(shù)據(jù)庫表最多可以設(shè)置六種類型的觸發(fā)器。

準(zhǔn)備工作foreachrow表示行級觸發(fā)器。目前MySQL僅支持行級觸發(fā)器，不支持語句級別的觸發(fā)器（例如createtable等語句）。foreachrow表示更新（insert、update或者delete）操作影響的每一條記錄都會執(zhí)行一次觸發(fā)程序。

準(zhǔn)備工作觸發(fā)程序中可以使用old關(guān)鍵字與new關(guān)鍵字。當(dāng)向表插入新記錄時，在觸發(fā)程序中可以使用new關(guān)鍵字表示新記錄，當(dāng)需要訪問新記錄的某個字段值時，可以使用“new.字段名”的方式訪問。

準(zhǔn)備工作觸發(fā)程序中可以使用old關(guān)鍵字與new關(guān)鍵字。當(dāng)從表中刪除某條舊記錄時，在觸發(fā)程序中可以使用old關(guān)鍵字表示舊記錄，當(dāng)需要訪問舊記錄的某個字段值時，可以使用“old.字段名”的方式訪問。

準(zhǔn)備工作觸發(fā)程序中可以使用old關(guān)鍵字與new關(guān)鍵字。當(dāng)修改表的某條記錄時，在觸發(fā)程序中可以使用old關(guān)鍵字表示修改前的舊記錄、使用new關(guān)鍵字表示修改后的新記錄。當(dāng)需要訪問舊記錄的某個字段值時，可以使用“old.字段名”的方式訪問。當(dāng)需要訪問修改后的新記錄的某個字段值時，可以使用“new.字段名”的方式訪問。

準(zhǔn)備工作觸發(fā)程序中可以使用old關(guān)鍵字與new關(guān)鍵字。old記錄是只讀的，可以引用它，但不能更改它。在before觸發(fā)程序中，可使用“setnew.col_name=value”更改new記錄的值。

準(zhǔn)備工作前面曾經(jīng)提到，MySQL可以使用復(fù)合數(shù)據(jù)類型set或者enum對字段的取值范圍進(jìn)行檢查約束，使用復(fù)合數(shù)據(jù)類型可以實(shí)現(xiàn)離散的字符串?dāng)?shù)據(jù)的檢查約束，對于數(shù)值型的數(shù)不建議使用set或者enum實(shí)現(xiàn)檢查約束，可以使用觸發(fā)器實(shí)現(xiàn)。

使用觸發(fā)器實(shí)現(xiàn)檢查約束冗余的數(shù)據(jù)需要額外的維護(hù)，維護(hù)冗余數(shù)據(jù)時，為了避免數(shù)據(jù)不一致問題的發(fā)生（例如：剩余的學(xué)生名額+已選學(xué)生人數(shù)≠課程的人數(shù)上限），冗余的數(shù)據(jù)應(yīng)該盡量避免交由人工維護(hù)，建議冗余的數(shù)據(jù)交由應(yīng)用系統(tǒng)（例如觸發(fā)器）自動維護(hù)。

使用觸發(fā)器維護(hù)冗余數(shù)據(jù)對于InnoDB存儲引擎的表而言，由于支持外鍵約束，在定義外鍵約束時，通過設(shè)置外鍵的級聯(lián)選項(xiàng)cascade、setnull或者noaction（restrict），外鍵約束關(guān)系可以交由InnoDB存儲引擎自動維護(hù)。

使用觸發(fā)器模擬外鍵級聯(lián)選項(xiàng)可以使用下面四種方法查看觸發(fā)器的定義。1．使用showtriggers命令查看觸發(fā)器的定義

查看觸發(fā)器的定義可以使用下面四種方法查看觸發(fā)器的定義。2．查詢information_schema數(shù)據(jù)庫中的triggers表，可以查看觸發(fā)器的定義MySQL中所有觸發(fā)器的定義都存放在information_schema數(shù)據(jù)庫下的triggers表中，查詢triggers表，可以查看所有數(shù)據(jù)庫中所有觸發(fā)器的詳細(xì)信息，查詢語句如下：select*frominformation_schema.triggers\G

查看觸發(fā)器的定義可以使用下面四種方法查看觸發(fā)器的定義。3．使用“showcreatetrigger”命令可以查看某一個觸發(fā)器的定義。例如使用“showcreatetriggerorganization_delete_before_trigger\G”命令可以查看觸發(fā)器organization_delete_before_trigger的定義。

查看觸發(fā)器的定義可以使用下面四種方法查看觸發(fā)器的定義。4．成功創(chuàng)建觸發(fā)器后，MySQL自動在數(shù)據(jù)庫目錄下創(chuàng)建TRN以及TRG觸發(fā)器文件，以記事本方式打開這些文件，可以查看觸發(fā)器的定義。

查看觸發(fā)器的定義可以使用droptrigger語句將該觸發(fā)器刪除，語法格式如下。droptrigger觸發(fā)器名

刪除觸發(fā)器1．觸發(fā)程序中如果包含select語句，該select語句不能返回結(jié)果集。2．同一個表不能創(chuàng)建兩個相同觸發(fā)時間、觸發(fā)事件的觸發(fā)程序。3．觸發(fā)程序中不能使用以顯式或隱式方式打開、開始或結(jié)束事務(wù)的語句，如starttransaction、commit、rollback或者setmit=0等語句。

使用觸發(fā)器的10條注意事項(xiàng)4．MySQL觸發(fā)器針對記錄進(jìn)行操作，當(dāng)批量更新數(shù)據(jù)時，引入觸發(fā)器會導(dǎo)致更新操作性能降低。5．在MyISAM存儲引擎中，觸發(fā)器不能保證原子性。InnoDB存儲引擎支持事務(wù)，使用觸發(fā)器可以保證更新操作與觸發(fā)程序的原子性，此時觸發(fā)程序和更新操作是在同一個事務(wù)中完成。

使用觸發(fā)器的10條注意事項(xiàng)6．InnoDB存儲引擎實(shí)現(xiàn)外鍵約束關(guān)系時，建議使用級聯(lián)選項(xiàng)維護(hù)外鍵數(shù)據(jù)；MyISAM存儲引擎雖然不支持外鍵約束關(guān)系時，但可以使用觸發(fā)器實(shí)現(xiàn)級聯(lián)修改和級聯(lián)刪除，進(jìn)而維護(hù)“外鍵”數(shù)據(jù)，模擬實(shí)現(xiàn)外鍵約束關(guān)系。

使用觸發(fā)器的10條注意事項(xiàng)7．使用觸發(fā)器維護(hù)InnoDB外鍵約束的級聯(lián)選項(xiàng)時，數(shù)據(jù)庫開發(fā)人員究竟應(yīng)該選擇after觸發(fā)器還是before觸發(fā)器？答案是：應(yīng)該首先維護(hù)子表的數(shù)據(jù)，然后再維護(hù)父表的數(shù)據(jù)，否則可能出現(xiàn)錯誤。

使用觸發(fā)器的10條注意事項(xiàng)8．MySQL的觸發(fā)程序不能對本表進(jìn)行更新語句（例如update語句）。觸發(fā)程序中的更新操作可以直接使用set命令替代，否則可能出現(xiàn)錯誤信息，甚至陷入死循環(huán)。9．在before觸發(fā)程序中，auto_increment字段的new值為0，不是實(shí)際插入新記錄時自動生成的自增型字段值。使用觸發(fā)器的10條注意事項(xiàng)10．添加觸發(fā)器后，建議對其進(jìn)行詳細(xì)的測試，測試通過后再決定是否使用觸發(fā)器。

使用觸發(fā)器的10條注意事項(xiàng)派生表與視圖一樣，一般在from子句中使用，其語法格式如下（粗體字代碼為派生表代碼）。….from(select子句)派生表名….派生表必須是一個有效的表，因此它必須遵守以下規(guī)則：每個派生表必須有自己的別名。派生表中的所有字段必須要有名稱，字段名必須唯一。

派生表（DerivedTable）子查詢一般在主查詢語句中的where子句或者h(yuǎn)aving子句中使用。視圖通常在主查詢語句中的from子句中使用。

子查詢、視圖、臨時表、派生表視圖本質(zhì)是一條select語句，執(zhí)行的是某一個數(shù)據(jù)源某個字段的查詢操作，如果視圖的“主查詢”語句是update語句、delete語句或者insert語句，且“主查詢”語句執(zhí)行了該數(shù)據(jù)源該字段的更新操作，那么主查詢語句將出錯。原因非常簡單：在對某個表的某個字段操作時，查詢操作（select語句）不能與更新操作（update語句、delete語句或者insert語句）同時進(jìn)行。

子查詢、視圖、臨時表、派生表與視圖相似，臨時表一般在from子句中使用。臨時表與視圖的區(qū)別在于：臨時表本質(zhì)也是一條select語句，執(zhí)行的是某一個數(shù)據(jù)源某個字段的查詢操作，但由于臨時表會先執(zhí)行完畢，并且將查詢結(jié)果集提前置放到服務(wù)器內(nèi)存。因此“臨時表”的“主查詢”語句（例如update、delete或者insert語句）執(zhí)行字段的更新操作時，不會產(chǎn)生“ERROR1443(HY000)”錯誤。

子查詢、視圖、臨時表、派生表派生表與臨時表的功能基本相同，它們之間的最大區(qū)別在于生命周期不同。

臨時表如果是手工創(chuàng)建，