MySQL觸發(fā)器使用詳解

更新時間：2015年01月07日 09:49:20 投稿：hebedich

本文主要詳細介紹了mysql數據庫的觸發(fā)器的相關知識，非常的全面，有需要的小伙伴參考下吧。

MySQL包含對觸發(fā)器的支持。觸發(fā)器是一種與表操作有關的數據庫對象，當觸發(fā)器所在表上出現指定事件時，將調用該對象，即表的操作事件觸發(fā)表上的觸發(fā)器的執(zhí)行。

創(chuàng)建觸發(fā)器
在MySQL中，創(chuàng)建觸發(fā)器語法如下：

CREATE TRIGGER trigger_name

trigger_time

trigger_event ON tbl_name

FOR EACH ROW

trigger_stmt

其中：

trigger_name：標識觸發(fā)器名稱，用戶自行指定；
trigger_time：標識觸發(fā)時機，取值為 BEFORE 或 AFTER；
trigger_event：標識觸發(fā)事件，取值為 INSERT、UPDATE 或 DELETE；
tbl_name：標識建立觸發(fā)器的表名，即在哪張表上建立觸發(fā)器；
trigger_stmt：觸發(fā)器程序體，可以是一句SQL語句，或者用 BEGIN 和 END 包含的多條語句。

由此可見，可以建立6種觸發(fā)器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一個限制是不能同時在一個表上建立2個相同類型的觸發(fā)器，因此在一個表上最多建立6個觸發(fā)器。

trigger_event 詳解
MySQL 除了對 INSERT、UPDATE、DELETE 基本操作進行定義外，還定義了 LOAD DATA 和 REPLACE 語句，這兩種語句也能引起上述6中類型的觸發(fā)器的觸發(fā)。

LOAD DATA 語句用于將一個文件裝入到一個數據表中，相當與一系列的 INSERT 操作。

REPLACE 語句一般來說和 INSERT 語句很像，只是在表中有 primary key 或 unique 索引時，如果插入的數據和原來 primary key 或 unique 索引一致時，會先刪除原來的數據，然后增加一條新數據，也就是說，一條 REPLACE 語句有時候等價于一條。

INSERT 語句，有時候等價于一條 DELETE 語句加上一條 INSERT 語句。

INSERT 型觸發(fā)器：插入某一行時激活觸發(fā)器，可能通過 INSERT、LOAD DATA、REPLACE 語句觸發(fā)；
UPDATE 型觸發(fā)器：更改某一行時激活觸發(fā)器，可能通過 UPDATE 語句觸發(fā)；
DELETE 型觸發(fā)器：刪除某一行時激活觸發(fā)器，可能通過 DELETE、REPLACE 語句觸發(fā)。

BEGIN … END 詳解
在MySQL中，BEGIN … END 語句的語法為：

BEGIN
[statement_list]
END
其中，statement_list 代表一個或多個語句的列表，列表內的每條語句都必須用分號（;）來結尾。
而在MySQL中，分號是語句結束的標識符，遇到分號表示該段語句已經結束，MySQL可以開始執(zhí)行了。因此，解釋器遇到statement_list 中的分號后就開始執(zhí)行，然后會報出錯誤，因為沒有找到和 BEGIN 匹配的 END。

這時就會用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一條命令，不需要語句結束標識，語法為：
DELIMITER new_delemiter
new_delemiter 可以設為1個或多個長度的符號，默認的是分號（;），我們可以把它修改為其他符號，如$：
DELIMITER $
在這之后的語句，以分號結束，解釋器不會有什么反應，只有遇到了$，才認為是語句結束。注意，使用完之后，我們還應該記得把它給修改回來。

一個完整的創(chuàng)建觸發(fā)器示例
假設系統中有兩個表：
班級表 class(班級號 classID, 班內學生數 stuCount)
學生表 student(學號 stuID, 所屬班級號 classID)
要創(chuàng)建觸發(fā)器來使班級表中的班內學生數隨著學生的添加自動更新，代碼如下：

復制代碼代碼如下:

DELIMITER $

create trigger tri_stuInsert after insert

on student for each row

begin

declare c int;

set c = (select stuCount from class where classID=new.classID);

update class set stuCount = c + 1 where classID = new.classID;

end$

DELIMITER ;

變量詳解
MySQL 中使用 DECLARE 來定義一局部變量，該變量只能在 BEGIN … END 復合語句中使用，并且應該定義在復合語句的開頭，

即其它語句之前，語法如下：

DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 為變量名稱，同 SQL 語句一樣，變量名不區(qū)分大小寫；type 為 MySQL 支持的任何數據類型；可以同時定義多個同類型的變量，用逗號隔開；變量初始值為 NULL，如果需要，可以使用 DEFAULT 子句提供默認值，值可以被指定為一個表達式。

對變量賦值采用 SET 語句，語法為：

SET var_name = expr [,var_name = expr] ...

NEW 與 OLD 詳解

上述示例中使用了NEW關鍵字，和 MS SQL Server 中的 INSERTED 和 DELETED 類似，MySQL 中定義了 NEW 和 OLD，用來表示

觸發(fā)器的所在表中，觸發(fā)了觸發(fā)器的那一行數據。
具體地：
在 INSERT 型觸發(fā)器中，NEW 用來表示將要（BEFORE）或已經（AFTER）插入的新數據；
在 UPDATE 型觸發(fā)器中，OLD 用來表示將要或已經被修改的原數據，NEW 用來表示將要或已經修改為的新數據；
在 DELETE 型觸發(fā)器中，OLD 用來表示將要或已經被刪除的原數據；
使用方法： NEW.columnName （columnName 為相應數據表某一列名）
另外，OLD 是只讀的，而 NEW 則可以在觸發(fā)器中使用 SET 賦值，這樣不會再次觸發(fā)觸發(fā)器，造成循環(huán)調用（如每插入一個學生前，都在其學號前加“2013”）。

查看觸發(fā)器

和查看數據庫（show databases;）查看表格（show tables;）一樣，查看觸發(fā)器的語法如下：

SHOW TRIGGERS [FROM schema_name];
其中，schema_name 即 Schema 的名稱，在 MySQL 中 Schema 和 Database 是一樣的，也就是說，可以指定數據庫名，這樣就

不必先“USE database_name;”了。

刪除觸發(fā)器

和刪除數據庫、刪除表格一樣，刪除觸發(fā)器的語法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

觸發(fā)器的執(zhí)行順序

我們建立的數據庫一般都是 InnoDB 數據庫，其上建立的表是事務性表，也就是事務安全的。這時，若SQL語句或觸發(fā)器執(zhí)行失敗，MySQL 會回滾事務，有：

①如果 BEFORE 觸發(fā)器執(zhí)行失敗，SQL 無法正確執(zhí)行。
②SQL 執(zhí)行失敗時，AFTER 型觸發(fā)器不會觸發(fā)。
③AFTER 類型的觸發(fā)器執(zhí)行失敗，SQL 會回滾。

小伙伴們是否對mysql的觸發(fā)器的使用有所了解了呢，如有疑問，就給我留言吧，大家共同進步。

您可能感興趣的文章: