萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql之觸發器trigger的使用例子
mysql之觸發器trigger的使用例子
為什麼要使用觸發器
觸發器的優點
1，觸發器的"自動性"
對程序員來說，觸發器是看不到的，但是他的確做事情了，如果不用觸發器的話，你更新了user表的name字段時，你還要寫代碼去更新其他表裡面的冗余字段，我舉例子，只是一張表，如果是幾張表都有冗余字段呢，你的代碼是不是要寫很多呢，看上去是不是很不爽呢。
2，觸發器的數據完整性
觸發器有回滾性，舉個例子，我發現我很喜歡舉子，就是你要更新五張表的數據，不會出現更新了二個張表，而另外三張表沒有更新。
但是如果是用php代碼去寫的話，就有可能出現這種情況的，比如你更新了二張表的數據，這個時候，數據庫掛掉了。你就郁悶了，有的更新了，有的沒更新。這樣頁面顯示不一致了，變有bug了。
關於觸發器(trigger)的例子
觸發器(trigger)：監視某種情況，並觸發某種操作。
觸發器創建語法四要素：1.監視地點(table) 2.監視事件(insert/update/delete) 3.觸發時間(after/before) 4.觸發事件(insert/update/delete)
語法：
create trigger triggerName after/before insert/update/delete on 表名 for each row #這句話在mysql是固定的 begin sql語句; end;
注：各自顏色對應上面的四要素。
首先我們來創建兩張表：
#商品表
create table g
(
id int primary key auto_increment,
name varchar(20),
num int
);
#訂單表
create table o
(
oid int primary key auto_increment,
gid int,
much int
);
insert into g(name,num) values('商品1',10),('商品2',10),('商品3',10);
如果我們在沒使用觸發器之前：假設我們現在賣了3個商品1，我們需要做兩件事
1.往訂單表插入一條記錄 insert into o(gid,much) values(1,3); 2.更新商品表商品1的剩余數量 update g set num=num-3 where id=1;
現在，我們來創建一個觸發器：
需要先執行該語句：delimiter $(意思是告訴mysql語句的結尾換成以$結束)
create trigger tg1 after insert on o for each row begin update g set num=num-3 where id=1; end$ 這時候我們只要執行： insert into o(gid,much) values(1,3)$
會發現商品1的數量變為7了，說明在我們插入一條訂單的時候，觸發器自動幫我們做了更新操作。
但現在會有一個問題，因為我們觸發器裡面num和id都是寫死的，所以不管我們買哪個商品，最終更新的都是商品1的數量。比如：我們往訂單表再插入一條記錄：insert into o(gid,much) values(2,3),執行完後會發現商品1的數量變4了，而商品2的數量沒變，這樣顯然不是我們想要的結果。我們需要改改我們之前創建的觸發器。
我們如何在觸發器引用行的值，也就是說我們要得到我們新插入的訂單記錄中的gid或much的值。
對於insert而言，新插入的行用new來表示，行中的每一列的值用new.列名來表示。
所以現在我們可以這樣來改我們的觸發器
create trigger tg2 after insert on o for each row begin update g set num=num-new.much where id=new.gid;(注意此處和第一個觸發器的不同) end$
第二個觸發器創建完畢，我們先把第一個觸發器刪掉
drop trigger tg1$
再來測試一下，插入一條訂單記錄：insert into o(gid,much) values(2,3)$
執行完發現商品2的數量變為7了，現在就對了。
現在還存在兩種情況：
1.當用戶撤銷一個訂單的時候，我們這邊直接刪除一個訂單，我們是不是需要把對應的商品數量再加回去呢？
2.當用戶修改一個訂單的數量時，我們觸發器修改怎麼寫?
我們先分析一下第一種情況：
監視地點：o表
監視事件：delete
觸發時間：after
觸發事件：update
對於delete而言：原本有一行,後來被刪除，想引用被刪除的這一行，用old來表示，old.列名可以引用被刪除的行的值。
那我們的觸發器就該這樣寫：
create trigger tg3 after delete on o for each row begin update g set num = num + old.much where id = old.gid;(注意這邊的變化) end$
創建完畢。
再執行delete from o where oid = 2$
會發現商品2的數量又變為10了。
第二種情況：
監視地點：o表
監視事件：update
觸發時間：after
觸發事件：update
對於update而言：被修改的行，修改前的數據，用old來表示，old.列名引用被修改之前行中的值；
修改的後的數據，用new來表示，new.列名引用被修改之後行中的值。
那我們的觸發器就該這樣寫：
create trigger tg4 after update on o for each row begin update g set num = num+old.much-new.much where id = old/new.gid; end$
先把舊的數量恢復再減去新的數量就是修改後的數量了。
我們來測試下：先把商品表和訂單表的數據都清掉，易於測試。
假設我們往商品表插入三個商品，數量都是10，
買3個商品1：insert into o(gid,much) values(1,3)$
這時候商品1的數量變為7；
我們再修改插入的訂單記錄: update o set much = 5 where oid = 1$
我們變為買5個商品1，這時候再查詢商品表就會發現商品1的數量只剩5了，說明我們的觸發器發揮作用了。
如果再看不到我們接著看
在MySQL Server裡面也就是對某一個表的一定的操作，觸發某種條件（Insert,Update,Delete 等），從而自動執行的一段程序。從這種意義上講觸發器是一個特殊的存儲過程。下面通過MySQL觸發器實例，來了解一下觸發器的工作過程吧！
一、創建MySQL實例數據表：
在mysql的默認的測試test數據庫下，創建兩個表t_a與t_b：
下載: Create_SQL.sql
/*Table structure for table `t_a` */ DROP TABLE IF EXISTS `t_a`; CREATE TABLE `t_a` ( `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1; /*Data for the table `t_a` */ LOCK TABLES `t_a` WRITE; UNLOCK TABLES; /*Table structure for table `t_b` */ DROP TABLE IF EXISTS `t_b`; CREATE TABLE `t_b` ( `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1; /*Data for the table `t_b` */ LOCK TABLES `t_b` WRITE; UNLOCK TABLES;
在t_a表上分創建一個CUD（增、改、刪）3個觸發器，將t_a的表數據與t_b同步實現CUD，注意創建觸發器每個表同類事件有且僅有一個對應觸發器，為什麼只能對一個觸發器，不解釋啦，看MYSQL的說明幫助文檔吧。
二、創建MySQL實例觸發器：
在實例數據表t_a上依次按照下面步驟創建tr_a_insert、tr_a_update、tr_a_delete三個觸發器
1、創建INSERT觸發器trigger_a_insert：
下載: trigger_a_insert.sql
DELIMITER $$ USE `test`$$ --判斷數據庫中是否存在tr_a_insert觸發器 DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_insert`$$ --不存在tr_a_insert觸發器，開始創建觸發器 --Trigger觸發條件為insert成功後進行觸發 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `tr_a_insert` AFTER INSERT ON `t_a` FOR EACH ROW BEGIN --Trigger觸發後，同時對t_b新增同步一條數據 INSERT INTO `t_b` SET username = NEW.username, groupid=NEW.groupid; END; $$ DELIMITER;
2、創建UPDATE觸發器trigger_a_update：
下載: trigger_a_update.sql
DELIMITER $$ USE `test`$$ --判斷數據庫中是否存在tr_a_update觸發器 DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_update`$$ --不存在tr_a_update觸發器，開始創建觸發器 --Trigger觸發條件為update成功後進行觸發 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `tr_a_update` AFTER UPDATE ON `t_a` FOR EACH ROW BEGIN --Trigger觸發後，當t_a表groupid,username數據有更改時，對t_b表同步一條更新後的數據 IF new.groupid != old.groupid OR old.username != new.username THEN UPDATE `t_b` SET groupid=NEW.groupid,username=NEW.username WHEREusername=OLD.username AND groupid=OLD.groupid; END IF; END; $$ DELIMITER ;
3、創建DELETE觸發器trigger_a_delete：
下載: trigger_a_delete.sql
DELIMITER $$ USE `test`$$ --判斷數據庫中是否存在tr_a_delete觸發器 DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_delete`$$ --不存在tr_a_delete觸發器，開始創建觸發器 --Trigger觸發條件為delete成功後進行觸發 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `tr_a_delete` AFTER DELETE ON `t_a` FOR EACH ROW BEGIN --t_a表數據刪除後，t_b表關聯條件相同的數據也同步刪除 DELETE FROM `t_b` WHERE username=Old.username AND groupid=OLD.groupid; END; $$ DELIMITER ;
三、測試MySQL實例觸發器：
分別測試實現t_a與t_b實現數據同步CUD(增、改、刪)3個Triggers
1、測試MySQL的實例tr_a_insert觸發器：
在t_a表中新增一條數據，然後分別查詢t_a/t_b表的數據是否數據同步，測試觸發器成功標志，t_a表無論在何種情況下，新增了一條或多條記錄集時，沒有t_b表做任何數據insert操作，它同時新增了一樣的多條記錄集。
下面來進行MySQL觸發器實例測試：
--t_a表新增一條記錄集
INSERT INTO `t_a` (username,groupid) VALUES ('sky54.net',123)
--查詢t_a表
SELECT id,username,groupid FROM `t_a`
--查詢t_b表
SELECT id,username,groupid FROM `t_b`2、測試MySQL的實例tr_a_update、tr_a_delete觸發器：
這兩個MySQL觸發器測試原理、步驟與tr_a_insert觸發器一樣的，先修改/刪除一條數據，然後分別查看t_a、t_b表的數據變化情況，數據變化同步說明Trigger實例成功，否則需要逐步排查錯誤原因。
世界上任何一種事物都其其優點和缺點，優點與缺點是自身一個相對立的面。當然這裡不是強調“世界非黑即白”式的“二元論”，“存在即合理”嘛。當然MySQL觸發器的優點不說了，說一下不足之處，MySQL Trigger沒有很好的調試、管理環境，難於在各種系統環境下測試，測試比MySQL存儲過程要難，所以建議在生成環境下，盡量用存儲過程來代替MySQL觸發器。
