電工與電子技術(shù)電子商務(wù)電子課件數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）第12章ppt

1、電子課件數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）第12章第12章 MySQL觸發(fā)器與事件調(diào)度器數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）CONTENTS觸發(fā)器1事件2小結(jié)3觸發(fā)器112.1.1 概念觸發(fā)器是一種特殊的存儲過程，它在插入，刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)庫本身標(biāo)準(zhǔn)的功能有更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。 有以下的作用：審計安全性實現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則實現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫相關(guān)完整性規(guī)則12.1.2 創(chuàng)建使用觸發(fā)器觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫對象，當(dāng)表上出現(xiàn)特定事件時，將激活該對象。在MySQL中，創(chuàng)建觸發(fā)器的基本形

2、式如下：create trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt bl_name必須引用永久性表。不能將觸發(fā)程序與temporary表或視圖關(guān)聯(lián)起來。trigger_time是觸發(fā)程序的動作時間。它可以是before或after，以指明觸發(fā)程序是在激活它的語句之前或之后觸發(fā)。 12.1.2 創(chuàng)建使用觸發(fā)器trigger_event指明了激活觸發(fā)程序的語句的類型。trigger_event可以是下述值之一：(1)insert：將新行插入表時激活觸發(fā)程序，例如，通過ins

3、ert、load data和replace語句。(2)update：更改某一行時激活觸發(fā)程序，例如，通過update語句。(3)delete：從表中刪除某一行時激活觸發(fā)程序，例如，通過delete和replace語句。特別提醒，trigger_event與以表操作方式激活觸發(fā)程序的sql語句并不很類似，這點很重要。對于具有相同觸發(fā)程序動作時間和事件的給定表，不能有兩個觸發(fā)程序。12.1.2 創(chuàng)建使用觸發(fā)器【例12-1】創(chuàng)建一個表tb，其中只有一列a。在表上創(chuàng)建一個觸發(fā)器，每次插入操作時，將用戶變量count的值加一12.1.2 創(chuàng)建使用觸發(fā)器向tb中插入一行數(shù)據(jù)： INSERT INTO tb

4、 VALUES(11); SELECT count; 12.1.2 創(chuàng)建使用觸發(fā)器再向tb中插入一行數(shù)據(jù)： INSERT INTO tb VALUES(21); SELECT count;12.1.2 創(chuàng)建使用觸發(fā)器觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲程序，也不能使用采用CALL語句的動態(tài)SQL觸發(fā)程序不能使用以顯式或隱式方式開始或結(jié)束事務(wù)的語句，如START TRANSACTION、COMMIT或ROLLBACK。使用OLD和NEW關(guān)鍵字，能夠訪問受觸發(fā)程序影響的行中的列（OLD和NEW不區(qū)分大小寫）。在INSERT觸發(fā)程序中，僅能使用NEW.col_name，沒有舊行。在DELETE觸發(fā)

5、程序中，僅能使用OLD.col_name，沒有新行。在UPDATE觸發(fā)程序中，可以使用OLD.col_name來引用更新前的某一行的列，也能使用NEW.col_name來引用更新后的行中的列。用OLD命名的列是只讀的。你可以引用它，但不能更改它。對于用NEW命名的列，如果具有SELECT權(quán)限，可引用它。在BEFORE觸發(fā)程序中，如果你具有UPDATE權(quán)限，可使用“SET NEW.col_name = value”更改它的值。這意味著，你可以使用觸發(fā)程序來更改將要插入到新行中的值，或用于更新行的值。在BEFORE觸發(fā)程序中，AUTO_INCREMENT列的NEW值為0，不是實際插入新記錄時將自動

6、生成的序列號。通過使用BEGIN .END結(jié)構(gòu)，能夠定義執(zhí)行多條語句的觸發(fā)程序。12.1.2 創(chuàng)建使用觸發(fā)器【例12-2】創(chuàng)建一個由delete觸發(fā)多個執(zhí)行語句的觸發(fā)器tb_delete,每次刪除記錄時，都把刪除的記錄的a字段的值賦值給用戶變量old_value。count記錄刪除的個數(shù)。12.1.2 創(chuàng)建使用觸發(fā)器用delete刪除所有數(shù)據(jù)a=21后，查看old_value 和count如下：12.1.2 創(chuàng)建使用觸發(fā)器【例12-3】定義了一個update觸發(fā)程序，用于檢查更新每一行時將使用的新值，并更改值，使之位于0100的范圍內(nèi)。它必須是before觸發(fā)程序，這是因為，需要在將值用于更新

7、行之前對其進行檢查：12.1.2 創(chuàng)建使用觸發(fā)器當(dāng)我們把數(shù)據(jù)都更新為102后查看數(shù)據(jù)，應(yīng)該都是100，如下： 12.1.3 查看觸發(fā)器可以通過執(zhí)行以下命令執(zhí)行命令查看觸發(fā)器的狀態(tài)、語法等信息show triggers 另一種方法是查詢系統(tǒng)表information_schema.triggers表，這個方式可以查詢指定觸發(fā)器的指定信息，操作起來明顯方便得多。12.1.3 查看觸發(fā)器【例12-4】查詢名稱為tb1_insert的觸發(fā)器12.1.4 刪除觸發(fā)器在MySQL中，刪除觸發(fā)器的基本形式如下：drop trigger schema_name.trigger_name觸發(fā)程序 數(shù)據(jù)庫（sche

8、ma_name）是可選的。如果省略了schema，將從當(dāng)前數(shù)據(jù)庫中刪除觸發(fā)程序。【例12-5】刪除觸發(fā)器tb1_insert. 12.1.4 刪除觸發(fā)器【例12-4】查詢名稱為tb1_insert的觸發(fā)器12.1.5 對觸發(fā)器的進一步說明下面是使用觸發(fā)器的一些限制：觸發(fā)器不能調(diào)用將數(shù)據(jù)返回客戶端的存儲過程，也不能使用采用call語句的動態(tài)sql（允許存儲過程通過參數(shù)將數(shù)據(jù)返回觸發(fā)器）。觸發(fā)器不能使用以顯式或隱式方式開始或結(jié)束事務(wù)的語句，如start transaction、commit或rollback。 需要注意以下兩點： 1.MySQL觸發(fā)器針對行來操作，因此當(dāng)處理大數(shù)據(jù)集的時候可能效率很

9、低。2.觸發(fā)器不能保證原子性，例如在myisam中，當(dāng)一個更新觸發(fā)器在更新一個表后，觸發(fā)對另外一個表的更新，若觸發(fā)器失敗，不會回滾第一個表的更新。innodb中的觸發(fā)器和操作則是在一個事務(wù)中完成，是原子操作。事件212.2.1 事件概念自MySQL5.1.0起，增加了一個非常有特色的功能事件調(diào)度器（event scheduler），可以用做定時執(zhí)行某些特定任務(wù)（例如：刪除記錄、對數(shù)據(jù)進行匯總等等），來取代原先只能由操作系統(tǒng)的計劃任務(wù)來執(zhí)行的工作。MySQL的44-事件調(diào)度器可以精確到每秒鐘執(zhí)行一個任務(wù)，而操作系統(tǒng)的計劃任務(wù)（如：linux下的cron或windows下的任務(wù)計劃）只能精確到每分

10、鐘執(zhí)行一次。事件調(diào)度器有時也可稱為臨時觸發(fā)器(temporal triggers)，因為事件調(diào)度器是基于特定時間周期觸發(fā)來執(zhí)行某些任務(wù)，而觸發(fā)器(triggers)是基于某個表所產(chǎn)生的事件觸發(fā)的，區(qū)別也就在這里。12.2.2 創(chuàng)建事件在MySQL中，創(chuàng)建事件的基本形式如下：create event event_name on schedule schedule on completion not preserve enable | disable | disable on slave comment comment do event_body; schedule: at timestamp i

11、nterval: quantity 12.2.2 創(chuàng)建事件【例12-6】創(chuàng)建一個立即啟動的事件,創(chuàng)建后查看學(xué)生信息如下:12.2.2 創(chuàng)建事件注意，在使用時間調(diào)度器這個功能之前必須確保event_scheduler已開啟，可執(zhí)行SET global event_scheduler = 1; 或者可以在配置my.ini文件 中加上event_scheduler = 1或set global event_scheduler = on;來開啟，也可以直接在啟動命令加上“event_scheduler=1”。12.2.2 創(chuàng)建事件【例12-7】創(chuàng)建一個30秒后啟動的事件, 創(chuàng)建后查看學(xué)生信息如下:12

12、.2.2 創(chuàng)建事件30秒后再查詢SELECT * FROM student WHERE sno=1414855329;12.2.3 修改事件在MySQL中，修改事件的基本形式如下：alter event event_name on schedule schedule rename to new_event_name on completion not preserve comment comment enable | disable do sql_statement 1）臨時關(guān)閉事件alter event event_name disable;2）開啟事件alter event event_name enable; 12.2.3 修改事件【例12-8】將事件direct的名字改成firstdirect。12.