mysql觸發(fā)器語法簡介

1、螞mysql觸發(fā)器語法簡介羂mysql觸發(fā)器語法簡介 2011-03-14 1459tagmysql觸發(fā)器語 法fanglor 1.語法命名規(guī)則 CREATE TRIGGER 觸發(fā)器名稱- BEFOREAFTER INSERTUPDATEDELETE ON 表名稱 FOR EACH ROW 觸發(fā)器SQL語句 觸發(fā)器必須有名字最多 64個 字符可能后面會附有分隔符.它和MySQL中其他對象的命名 方式基本相象.這里我有個習(xí)慣就是用表的名字 _觸發(fā)器類 型的縮寫因此如果是表t26觸發(fā)器是在事件 UPDATE參考 下面的點2和3之前BEFORE的那么它的名字就是 t26_bu。2.語法觸發(fā)時間 CR

2、EATE TRIGGER觸發(fā)器名稱 BEFOREAFTER- INSERTUPDATEDELETE ON表名稱FOR EACH ROW 觸發(fā)的SQL語句 觸發(fā)器有執(zhí)行的時間設(shè) 置可以設(shè)置為事件發(fā)生前或后BEFOREAFTER。3.語法事件 CREATE TRIGGER 觸發(fā)器名稱 BEFOREAFTER INSERTUPDATEDELETE- ON 表名稱 FOR EACH ROW 觸發(fā)的SQL語句同樣也能設(shè)定觸發(fā)的事件它們可以在執(zhí)行 insert、update或delete的過程中觸發(fā)。4.語法表CREATETRIGGER 觸發(fā)器名稱 BEFOREAFTERINSERTUPDATEDELET

3、E ON 表名稱-FOR EACH ROW 觸發(fā)的SQL語句 觸發(fā)器是屬于某一個表的當(dāng)在這個表上執(zhí)行插入、更新或刪除操作的時候就導(dǎo)致觸發(fā)器的激活.我們不能給同一張表的同一個事件安排兩個觸發(fā)器。5.語法步長觸發(fā)間隔CREATE TRIGGER觸發(fā)器名稱 BEFOREAFTER INSERTUPDATEDELETE ON 表名稱 FOR EACH ROW- 觸發(fā)的SQL語句 觸發(fā)器的執(zhí)行間隔 FOR EACH ROW 子句 通知觸發(fā)器每隔一行執(zhí)行一次動作而不是對整個表執(zhí)行一 次。6.語法語句 CREATE TRIGGER觸發(fā)器名稱 BEFOREAFTER INSERTUPDATEDELETE ON

4、 表名稱 FOR EACH ROW 觸發(fā)的SQL語句-觸發(fā)器包含所要觸發(fā)的 SQL語句這里的語句可以是任何合法的語句包括復(fù)合語句但是這里的語句受的限制和函數(shù)的一樣。Privileges權(quán)限 你必須擁有相當(dāng)大的權(quán)限才能創(chuàng)建觸發(fā)器CREATE TRIGGER如果你已經(jīng)是Root用戶那么就足夠了。這跟 SQL的標(biāo)準(zhǔn)有 所不同。因此在下一個版本的 MySQL中你完全有可能看 到有一種叫做CREATE TRIGGER的新權(quán)限。然后通過這 樣的方法賦予 GRANT CREATE TRIGGER ON 表名稱 TO 用戶或用戶列表 也可以通過這樣收回權(quán)限REVOKECREATE TRIGGER ON 表名稱

5、FROM用戶或用戶列 表MySQL觸發(fā)器語法詳解.一 CREATE TRIGGER 語法 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 觸發(fā)程序是與表 有關(guān)的命名數(shù)據(jù)庫對象當(dāng)表上出現(xiàn)特定事件時將激活該對象。 觸發(fā)程序與命名為 tbl_name 的表相關(guān)。 tbl_name 必須 引用永久性表。不能將觸發(fā)程序與 TEMPORARY 表或視圖 關(guān)聯(lián)起來。 trigger_time 是觸發(fā)程序的動作時間。它可以是 BEFORE 或 AFTER 以指明觸發(fā)程序是在

6、激活它的語句之前 或之后觸發(fā)。 trigger_event 指明了激活觸發(fā)程序的語句的類 型。trigger_event可以是下述值之一INSERT將新行插入表時激活觸發(fā)程序例如通過 INSERT 、LOAD DATA 和 REPLACE語句。 UPDATE更改某一行時激活觸發(fā)程序例 如通過UPDATE語句。DELETE從表中刪除某一行時激活 觸發(fā)程序例如通過 DELETE 和 REPLACE 語句。 請注意 trigger_event 與以表操作方式激活觸發(fā)程序的 SQL 語句并不 很類似這點很重要。例如 關(guān)于 INSERT 的 BEFORE 觸發(fā)程 序不僅能被 INSERT 語句激活也能被

7、 LOAD DATA 語句激活。 可能會造成混淆的例子之一是 INSERT INTO.ON DUPLICATE UPDATE. 語法 BEFORE INSERT 觸發(fā)程序?qū)?于每一行將激活后跟 AFTER INSERT 觸發(fā)程序或 BEFORE UPDATE 和 AFTER UPDATE 觸發(fā)程序具體情況取決于行上 是否有重復(fù)鍵。 對于具有相同觸發(fā)程序動作時間和事件的 給定表不能有兩個觸發(fā)程序。例如對于某一表不 能有兩個 BEFORE UPDATE 觸發(fā)程序。但可以有 1 個 BEFORE UPDATE 觸發(fā)程序和 1 個 BEFORE INSERT 觸發(fā)程序或 1 個 BEFORE UPDA

8、TE 觸發(fā)程序和 1 個 AFTER UPDATE 觸發(fā)程序。trigger_stmt是當(dāng)觸發(fā)程序激活時執(zhí)行的語句。如果你 打算執(zhí)行多個語句可使用 BEGIN.END復(fù) 合語句結(jié)構(gòu)。這樣 就能使用存儲子程序中允許的相同語句二DROP TRIGGER語法 DROP TRIGGERschema_name.trigger_name 舍棄觸發(fā) 程序。方案名稱schema_name是可選的。如果省略了 schema 方案將從當(dāng)前方 案中舍棄觸發(fā)程序。 注釋從MySQL 5.0.10 之前的MySQL版本升級到或更高版本時包括所有的 MySQL 5.1版 本必須在升級之前舍棄所有的觸發(fā)程序并在 隨后重新創(chuàng)

9、建它們否則在升級之后DROP TRIGGER不工作。DROP TRIGGER語句需要SUPER權(quán)限。三使用觸發(fā)程序 在本節(jié)中介紹了在 MySQL 5.1中使用觸發(fā)程序的方法并介 紹了在使用觸發(fā)程序方面的限制。觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫對象當(dāng)表上出現(xiàn)特定事件時將激活該對象。在某 些觸發(fā)程 序的用法中可用于檢查插入到表中的值或?qū)Ω?涉及的值進(jìn)行計算。觸發(fā)程序與表相關(guān)當(dāng)對表執(zhí)行INSERT、DELETE或UPDATE語句時將激活觸發(fā)程序。可以將 觸發(fā) 程序設(shè)置為在執(zhí)行語句之前或之后激活。例如可以在從表中 刪除每一行之前或在更新了每一行后激活觸發(fā)程序。要想創(chuàng)建觸發(fā)程序或舍棄觸發(fā)程序可使用CREA

10、TE TRIGGER或DROP TRIGGER語句觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶 端的存儲程序也不能使用采用CALL語句的動態(tài)SQL允許存儲程序通過參數(shù)將數(shù)據(jù)返回觸發(fā)程序。觸發(fā)程序不能使用以顯式或隱式方式開始或結(jié)束事務(wù)的語句如STARTTRANSACTION、COMMIT 或 ROLLBACK。使用 OLD 和NEW關(guān)鍵字能夠訪問受觸發(fā)程序影響的行中的列OLD和NEW不區(qū)分大小寫。在INSERT觸發(fā)程序中僅能使用NEW.col_name沒有舊行。在 DELETE觸發(fā)程序中僅能使用 OLD.col_name沒有新行。在 UPDATE觸發(fā)程序中可以使用OLD.col_name來引用更新前的某一行的

11、列也能使用 NEW.col_name來引用更新后的行中的列。 用OLD命名的列 是只讀的。你可以引用它但不能更改它。對于用NEW命名的列如果具有 SELECT權(quán)限可引用它。在 BEFORE觸發(fā)程 序中如果你具有 UPDATE權(quán)限可使用SETNEW.col_namevalue更改它的值。這意味著你可以使用觸發(fā) 程序來更改將要插入到新行中的值或用于更新行的值。在BEFORE觸發(fā)程序中 AUTO_INCREMENT 列的NEW 值為0 不是實際插入新記錄時將自動生成的序列號。通過使用BEGIN.END結(jié)構(gòu)能夠定義執(zhí)行多條語句的觸發(fā)程序。在BEGIN塊中還能使用存儲子程序中允許的其他語法如條件 和循環(huán)

12、等。但是正如存儲子程序那樣定義執(zhí)行多條語句的觸 發(fā)程序時如果使用mysql程序來輸入觸發(fā)程序需要重新定義 語句分隔符以便能夠在觸發(fā)程序定義中使用字符。在下面的 示例中演示了這些要點。在該示例中定義了1個UPDATE觸發(fā)程序用于檢查更新每一行時將使用的新值并更改值使 之位于0100的范圍內(nèi)。它必須是 BEFORE觸發(fā)程序這是因 為需要在將值用于更新行之前對其進(jìn)行檢查 mysql delimiter/ mysql CREATE TRIGGER upd_check BEFORE UPDATE ON account -FOR EACH ROW -BEGIN -IF NEW.amount 0THEN -

13、 SET NEW.amount0 - ELSEIF NEW.amount 100 THEN - SET NEW.amount100 - END IF -END/ mysql delimiter較為簡單的方法是單獨定義存儲程 序然后使用簡單的 CALL語句從觸發(fā)程序調(diào)用存儲程序。如 果你打算從數(shù)個觸發(fā)程序內(nèi)部調(diào)用相同的子程序該方法也 很有幫助。在觸發(fā)程序的執(zhí)行過程中MySQL處理錯誤的方式如下 如果BEFORE觸發(fā)程序失敗不執(zhí)行相應(yīng)行上的操 作。 僅當(dāng)BEFORE觸發(fā)程序如果有的話和行操作均已成功 執(zhí)行才執(zhí)行 AFTER觸發(fā)程序。 如果在BEFORE或AFTER 觸發(fā)程序的執(zhí)行過程中出現(xiàn)錯誤將導(dǎo)致調(diào)用觸發(fā)程序的整 個語句的失敗。對于事務(wù)性表如果觸發(fā)程序失敗以及由此導(dǎo)致的整個語句的失敗該語句所執(zhí)行的所有更改將回滾。對 于非事務(wù)性表不能執(zhí)行這類回滾因而即使語句失敗失敗之前所作的任何更改依然有效。例