mysql中的觸發(fā)器用法案例詳細說明

MySQL觸發(fā)器語法詳解:

觸發(fā)器 trigger是一種特殊的存儲過程，他在插入（inset）、刪除（delete）或修改（update）特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)本身標準的功能更精細和更復雜的數(shù)據(jù)控制能力。觸發(fā)器不是由程序調(diào)用，而是由某個事件來觸發(fā)的。在有數(shù)據(jù)修改時自動強制執(zhí)行其業(yè)務規(guī)則，經(jīng)常用于加強數(shù)據(jù)的完整性約束和業(yè)務規(guī)則等。觸發(fā)器可以查詢其他表，而且包含復制的sql語句。觸發(fā)器也可用于強制引用完整性。觸發(fā)器可以強制比用check約束定義的約束更為復雜的約束。

(一).CREATE TRIGGER語法

CREATE TRIGGER trigger_nametrigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt;
觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫對象，當表上出現(xiàn)特定事件時，將激活該對象。
觸發(fā)程序與命名為tbl_name的表相關(guān)。tbl_name必須引用永久性表。不能將觸發(fā)程序與TEMPORARY表或視圖關(guān)聯(lián)起來。
trigger_time是觸發(fā)程序的動作時間。它可以是BEFORE或AFTER，以指明觸發(fā)程序是在激活它的語句之前或之后觸發(fā)。
trigger_event指明了激活觸發(fā)程序的語句的類型。trigger_event可以是下述值之一：

(1).INSERT：將新行插入表時激活觸發(fā)程序，例如，通過INSERT、LOAD DATA和REPLACE
語句。
(2).UPDATE：更改某一行時激活觸發(fā)程序，例如，通過UPDATE語句。
(3).DELETE：從表中刪除某一行時激活觸發(fā)程序，例如，通過DELETE和REPLACE語句。

請注意，trigger_event與以表操作方式激活觸發(fā)程序的SQL語句并不很類似，這點很重要。例如，關(guān)于INSERT的BEFORE觸發(fā)程序不僅能被INSERT語句激活，也能被LOAD DATA語句激活。可能會造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...語法：BEFORE INSERT觸發(fā)程序?qū)τ诿恳恍袑⒓せ�，后跟AFTER INSERT觸發(fā)程序，或BEFORE UPDATE和AFTER UPDATE觸發(fā)程序，具體情況取決于行上是否有重復鍵。

對于具有相同觸發(fā)程序動作時間和事件的給定表，不能有兩個觸發(fā)程序。例如，對于某一表，不能有兩個BEFORE UPDATE觸發(fā)程序。但可以有1個BEFORE UPDATE觸發(fā)程序和1個BEFORE INSERT觸發(fā)程序，或1個BEFOREUPDATE觸發(fā)程序和1個AFTER UPDATE觸發(fā)程序。trigger_stmt是當觸發(fā)程序激活時執(zhí)行的語句。如果你打算執(zhí)行多個語句，可使用BEGIN ... END復合語句結(jié)構(gòu)。這樣，就能使用存儲子程序中允許的相同語句

(二).DROP TRIGGER語法

DROP TRIGGER[schema_name.]trigger_name舍棄觸發(fā)程序。方案名稱（schema_name）是可選的。如果省略了schema（方案），將從當前方案中舍棄觸發(fā)程序。

注釋：從MySQL 5.0.10之前的MySQL版本升級到5.0.10或更高版本時（包括所有的MySQL5.1版本），必須在升級之前舍棄所有的觸發(fā)程序，并在隨后重新創(chuàng)建它們，否則，在升級之后DROP TRIGGER不工作。DROP TRIGGER語句需要SUPER權(quán)限。

(三).使用觸發(fā)程序

在本節(jié)中，介紹了在MySQL 5.1中使用觸發(fā)程序的方法，并介紹了在使用觸發(fā)程序方面的限制。

觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫對象，當表上出現(xiàn)特定事件時，將激活該對象。在某些觸發(fā)程序的用法中，可用于檢查插入到表中的值，或?qū)?a target="_blank">更新涉及的值進行計算。

觸發(fā)程序與表相關(guān)，當對表執(zhí)行INSERT、DELETE或UPDATE語句時，將激活觸發(fā)程序�？梢詫⒂|發(fā)程序設(shè)置為在執(zhí)行語句之前或之后激活。例如，可以在從表中刪除每一行之前，或在更新了,每一行后激活觸發(fā)程序。要想創(chuàng)建觸發(fā)程序或舍棄觸發(fā)程序，可使用CREATE TRIGGER或DROP TRIGGER語句.觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲程序，也不能使用采用CALL語句的動態(tài)SQL（允許存儲程序通過參數(shù)將數(shù)據(jù)返回觸發(fā)程序）。

觸發(fā)程序不能使用以顯式或隱式方式開始或結(jié)束事務的語句，如START TRANSACTION、
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來引用更新前的某一行的列，也能使用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，不是實際插入新記錄時將自動生成的序列號。

通過使用BEGIN ...END結(jié)構(gòu)，能夠定義執(zhí)行多條語句的觸發(fā)程序。在BEGIN塊中，還能使用存儲子程序中允許的其他語法，如條件和循環(huán)等。但是，正如存儲子程序那樣，定義執(zhí)行多條語句的觸發(fā)程序時，如果使用mysql程序來輸入觸發(fā)程序，需要重新定義語句分隔符，以便能夠在觸發(fā)程序定義中使用字符“;”。在下面的示例中，演示了這些要點。在該示例中，定義了1個UPDATE觸發(fā)程序，用于檢查更新每一行時將使用的新值，并更改值，使之位于0～100的范圍內(nèi)。它必須是BEFORE觸發(fā)程序，這是因為，需要在將值用于更新行之前對其進行檢查：

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
   -> FOR EACH ROW
   -> BEGIN
   -> IF NEW.amount < 0 THEN
   -> SET NEW.amount = 0;
   -> ELSEIF NEW.amount > 100 THEN
   -> SET NEW.amount = 100;
   -> END IF;
   -> END;//
mysql> delimiter ;

較為簡單的方法是，單獨定義存儲程序，然后使用簡單的CALL語句從觸發(fā)程序調(diào)用存儲程序。如果你打算從數(shù)個觸發(fā)程序內(nèi)部調(diào)用相同的子程序，該方法也很有幫助。在觸發(fā)程序的執(zhí)行過程中，MySQL處理錯誤的方式如下：

(1)如果BEFORE觸發(fā)程序失敗，不執(zhí)行相應行上的操作。
(2)僅當BEFORE觸發(fā)程序（如果有的話）和行操作均已成功執(zhí)行，才執(zhí)行AFTER觸發(fā)程序。
(3) 如果在BEFORE或AFTER觸發(fā)程序的執(zhí)行過程中出現(xiàn)錯誤，將導致調(diào)用觸發(fā)程序的整個語句的失敗。
(4)對于事務性表，如果觸發(fā)程序失�。ㄒ约坝纱藢е碌恼麄�語句的失�。�，該語句所執(zhí)行的所有更改將回滾。對于非事務性表，不能執(zhí)行這類回滾，因而，即使語句失敗，失敗之前所作的任何更改依然有效。

例一：

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;

以上就是mysql中的觸發(fā)器用法實例詳解的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！