程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 數據庫觸發器詳解

數據庫觸發器詳解

編輯:MySQL綜合教程

1   引言
 
Mysql的觸發器和存儲過程一樣,都是嵌入到mysql的一段程序。觸發器是mysql5新增的功能,目前線上鳳巢系統、北斗系統以及哥倫布系統使用的數據庫均是mysql5.0.45版本,很多程序比如fc-star管理端,sfrd(das),dorado都會用到觸發器程序,實現對於數據庫增、刪、改引起事件的關聯操作。本文介紹了觸發器的類型和基本使用方法,講述了觸發器使用中容易產生的誤區,從mysql源碼中得到觸發器執行順序的結論,本文最後是實戰遭遇的觸發器經典案例。沒有特殊說明時,本文的實驗均基於mysql5.0.45版本。
 
2   Mysql觸發器的類型
 
2.1   Mysql觸發器的基本使用
 
創建觸發器。創建觸發器語法如下:
 
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
 
其中trigger_name標識觸發器名稱,用戶自行指定;
 
trigger_time標識觸發時機,用before和after替換;
 
trigger_event標識觸發事件,用insert,update和delete替換;
 
tbl_name標識建立觸發器的表名,即在哪張表上建立觸發器;
 
trigger_stmt是觸發器程序體;觸發器程序可以使用begin和end作為開始和結束,中間包含多條語句;
 
下面給出sfrd一個觸發器實例:
 
CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER trig_useracct_update
AFTER UPDATE
ON SF_User.useracct FOR EACH ROW
BEGIN
IF OLD.ulevelid = 10101 OR OLD.ulevelid = 10104 THEN
IF NEW.ulevelid = 10101 OR NEW.ulevelid = 10104 THEN
if NEW.ustatid != OLD.ustatid OR NEW.exbudget != OLD.exbudget THEN
INSERT into FC_Output.fcevent set type = 2, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
end if;
ELSE
INSERT into FC_Output.fcevent set type = 1, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
END IF;
END IF;
END;
 
上述觸發器實例使用了OLD關鍵字和NEW關鍵字。OLD和NEW可以引用觸發器所在表的某一列,在上述實例中,OLD.ulevelid表示表SF_User.useracct修改之前ulevelid列的值,NEW.ulevelid表示表SF_User.useracct修改之後ulevelid列的值。另外,如果是insert型觸發器,NEW.ulevelid也表示表SF_User.useracct新增行的ulevelid列值;如果是delete型觸發器OLD.ulevelid也表示表SF_User.useracct刪除行的ulevelid列原值。
 
另外,OLD列是只讀的,NEW列則可以在觸發器程序中再次賦值。
 
上述實例也使用了IF,THEN ,ELSE,END IF等關鍵字。在觸發器程序體中,在beigin和end之間,可以使用順序,判斷,循環等語句,實現一般程序需要的邏輯功能。
 
查看觸發器。查看觸發器語法如下,如果知道觸發器所在數據庫,以及觸發器名稱等具體信息:
 
SHOW TRIGGERS from SF_User like "usermaps%";       //查看SF_User庫上名稱和usermaps%匹配的觸發器
 
如果不了解觸發器的具體的信息,或者需要查看數據庫上所有觸發器,如下:
 
SHOW TRIGGERS;       //查看所有觸發器
 
用上述方式查看觸發器可以看到數據庫的所有觸發器,不過如果一個庫上的觸發器太多,由於會刷屏,可能沒有辦法查看所有觸發器程序。這時,可以采用如下方式:
 
Mysql中有一個information_schema.TRIGGERS表,存儲所有庫中的所有觸發器,desc information_schema. TRIGGERS,可以看到表結構:
 
+----------------------------+--------------+------+-----+---------+-------+
| Field                      | Type         | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| TRIGGER_CATALOG            | varchar(512) | YES |     | NULL    |       |
| TRIGGER_SCHEMA             | varchar(64) | NO   |     |         |       |
| TRIGGER_NAME               | varchar(64) | NO   |     |         |       |
| EVENT_MANIPULATION         | varchar(6)   | NO   |     |         |       |
| EVENT_OBJECT_CATALOG       | varchar(512) | YES |     | NULL    |       |
| EVENT_OBJECT_SCHEMA        | varchar(64) | NO   |     |         |       |
| EVENT_OBJECT_TABLE         | varchar(64) | NO   |     |         |       |
| ACTION_ORDER               | bigint(4)    | NO   |     | 0       |       |
| ACTION_CONDITION           | longtext     | YES |     | NULL    |       |
| ACTION_STATEMENT           | longtext     | NO   |     |         |       |
| ACTION_ORIENTATION         | varchar(9)   | NO   |     |         |       |
| ACTION_TIMING              | varchar(6)   | NO   |     |         |       |
| ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES |     | NULL    |       |
| ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES |     | NULL    |       |
| ACTION_REFERENCE_OLD_ROW   | varchar(3)   | NO   |     |         |       |
| ACTION_REFERENCE_NEW_ROW   | varchar(3)   | NO   |     |         |       |
| CREATED                    | datetime     | YES |     | NULL    |       |
| SQL_MODE                   | longtext     | NO   |     |         |       |
| DEFINER                    | longtext     | NO   |     |         |       |
+----------------------------+--------------+------+-----+---------+-------+
 
這樣,用戶就可以按照自己的需要,查看觸發器,比如使用如下語句查看上述觸發器:
 
select * from information_schema. TRIGGERS where TRIGGER_NAME= 'trig_useracct_update'\G;
 
刪除觸發器。刪除觸發器語法如下:
 
DROP TRIGGER [schema_name.]trigger_name
 
2.2   Msyql觸發器的trigger_time和trigger_event
 
現在,重新注意到trigger_time和trigger_event,上文說過,trigger_time可以用before和after替換,表示觸發器程序的執行在sql執行的前還是後;trigger_event可以用insert,update,delete替換,表示觸發器程序在什麼類型的sql下會被觸發。
 
在一個表上最多建立6個觸發器,即1)before insert型,2)before update型,3)before delete型,4)after insert型,5)after update型,6)after delete型。
 
觸發器的一個限制是不能同時在一個表上建立2個相同類型的觸發器。這個限制的一個來源是觸發器程序體的“begin和end之間允許運行多個語句”(摘自mysql使用手冊)。
 
另外還有一點需要注意,msyql除了對insert,update,delete基本操作進行定義外,還定義了load data和replace語句,而load data和replace語句也能引起上述6中類型的觸發器的觸發。
 
Load data語句用於將一個文件裝入到一個數據表中,相當與一系列insert操作。replace語句一般來說和insert語句很像,只是在表中有primary key和unique索引時,如果插入的數據和原來primary key和unique索引一致時,會先刪除原來的數據,然後增加一條新數據;也就是說,一條replace sql有時候等價於一條insert sql,有時候等價於一條delete sql加上一條insert sql。即是:
?   Insert型觸發器:可能通過insert語句,load data語句,replace語句觸發;
?   Update型觸發器:可能通過update語句觸發;
?   Delete型觸發器:可能通過delete語句,replace語句觸發;
 
3   Mysql觸發器的執行順序
 
先拋出觸發器相關的幾個問題
 
3.1   如果before類型的觸發器程序執行失敗,sql會執行成功嗎?
 
實驗如下:
 
1)在FC_Word.planinfo中建立before觸發器:
 
DELIMITER |
create trigger trigger_before_planinfo_update
before update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
insert into FC_Output.abc (planid) values (New.planid);
END
|
 
2)查看:mysql> select showprob from planinfo where planid=1;
 
+----------+
| showprob |
+----------+
|        2 |
+----------+
 
3)執行sql:
 
update planinfo set showprob=200 where planid=1;      觸發觸發器程序;
 
4)由於不存在FC_Output.abc,before觸發器執行失敗,提示:
 
ERROR 1146 (42S02): Table 'FC_Output.abc' doesn't exist
 
5)再次查看:
 
mysql> select showprob from planinfo where planid=1;
+----------+
| showprob |
+----------+
|        2 |
+----------+
 
即修改sql未執行成功。即如果before觸發器執行失敗,sql也會執行失敗。
 
3.2   如果sql執行失敗,會執行after類型的觸發器程序嗎?
 
實驗如下:
 
1)在FC_Word.planinfo中建立after觸發器:
 
DELIMITER |
create trigger trigger_after_planinfo_update
after update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
INSERT INTO FC_Output.fcevent set level = 2, type = 2, tabid = 5, userid = NEW.userid, planid = NEW.planid, planstat2 = NEW.planstat2, showprob = NEW.showprob, showrate = NEW.showrate, showfactor = NEW.showfactor, planmode = NEW.planmode;
END
|
 
2)查看觸發表:
 
mysql> select * from FC_Output.fcevent where planid=1;
Empty set (0.00 sec)
 
沒有planid=1的記錄
 
3)執行sql:
 
mysql> update planinfo set showprob1=200 where planid=1;
 
4)由於不存在showprob1列,提示錯誤:
 
ERROR 1054 (42S22): Unknown column 'showprob1' in 'field list'
 
5)再次查看觸發表:
 
mysql> select * from FC_Output.fcevent where planid=1;
Empty set (0.00 sec)
 
觸發表中沒有planid=1的記錄,sql在執行失敗時,after型觸發器不會執行。
 
3.3   如果after類型的觸發器程序執行失敗,sql會回滾嗎?
 
實驗如下:
 
1)在FC_Word.planinfo中建立after觸發器:
 
DELIMITER |
create trigger trigger_after_planinfo_update
after update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
insert into FC_Output.abc (planid) values (New.planid);
END
|
 
2)查看:mysql> select showprob from planinfo where planid=1;
 
+----------+
| showprob |
+----------+
|        2 |
+----------+
 
3)執行sql:
 
update planinfo set showprob=200 where planid=1;觸發觸發器程序;
 
4)由於不存在FC_Output.abc,after觸發器執行失敗,提示:
 
ERROR 1146 (42S02): Table 'FC_Output.abc' doesn't exist
 
5)再次查看:
 
mysql> select showprob from planinfo where planid=1;
+----------+
| showprob |
+----------+
|        2 |
+----------+
 
即修改sql未執行成功。即如果after觸發器執行失敗,sql會回滾。
 
這裡需要說明一下,上述實驗所使用的mysql引擎是innodb,innodb引擎也是目前線上鳳巢系統、北斗系統以及哥倫布系統所使用的引擎,在innodb上所建立的表是事務性表,也就是事務安全的。“對於事務性表,如果觸發程序失敗(以及由此導致的整個語句的失敗),該語句所執行的所有更改將回滾。對於非事務性表,不能執行這類回滾”(摘自mysql使用手冊)。因而,即使語句失敗,失敗之前所作的任何更改依然有效,也就是說,對於innodb引擎上的數據表,如果觸發器中的sql或引發觸發器的sql執行失效,則事務回滾,所有操作會失效。
 
3.4   mysql觸發器程序執行的順序
 
當一個表既有before類型的觸發器,又有after類型的觸發器時;當一條sql語句涉及多個表的update時,sql、觸發器的執行順序經過mysql源碼包裝過,有時比較復雜。
 
可以先看一段mysql的源代碼,當SQL中update多表的時候,Mysql的執行過程如下(省去了無關代碼):
 
/* 遍歷要更新的所有表*/
for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)
{
org_updated = updated
/* 如果有BEFORE 觸發器,則執行;如果執行失敗,跳到err2位置*/
if (table->triggers &&
table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,TRG_ACTION_BEFORE, TRUE))
goto err2;
/*執行更新,如果更新失敗,跳到err位置*/
if(local_error=table->file->update_row(table->record[1], table->record[0])))
goto err;
updated++; // 更新計數器
/* 如果有AFTER 觸發器,則執行;如果執行失敗,跳到err2位置*/
if (table->triggers &&
table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE))
goto err2;
err:
{
/*標志錯誤信息,寫日志等*/
}
err2:
{
/*恢復執行過的操作*/
check_opt_it.rewind();
/*如果執行了更新,且表是有事務的,做標志*/
if (updated != org_updated)
{
if (table->file->has_transactions())
transactional_tables= 1;
}
}
}
 
從上面代碼可以找到本章開始時拋出問題的答案。
 
1)   如果before型觸發器執行失敗,直接goto跳到err2位置,不會執行後續sql語句;
 
2)   如果sql執行失敗,直接goto跳到err位置,不會執行或許的after型觸發器;
 
3)   如過after觸發器執行失敗,goto到err2位置,恢復執行過的操作,且在事務型的表上做標記。
 
另外,在使用復雜的sql時,由於有些復雜的sql是mysql自己定義的,所以存在不確定性,使用簡單的sql比較可控。
 
4   Mysql觸發器在數據庫同步中的表現
 
4.1   觸發器運行失敗時,數據庫同步會失敗嗎?
 
有同步關系如下dbA?dbB。初始時同步正常。
 
1)在dbB上建立觸發器:
 
DELIMITER |
create trigger trigger_after_planinfo_update
after update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
insert into FC_Output.abc (planid) values (New.planid);
END
|
 
2)在dbA上執行sql,執行成功;
 
mysql> update planinfo set showprob=200 where planid= 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
3)由於dbB上沒有FC_Output.abc表,觸發器會執行失敗,這時,檢查一下同步狀態:
 
Slave_IO_Running: Yes
Slave_SQL_Running: NO
Last_Errno: 1146
Last_Error: Error 'Table 'FC_Output.abc' doesn't exist' on query. Default database: 'FC_Word'. Query: 'update planinfo set showprob=200 where planid= 1'
 
可以看到IO線程運行正常,sql線程運行失敗,並提示觸發器運行失敗的錯誤信息。
 
回憶一下3.1和3.3所述部分,無論是before部分的觸發器還是after類型的觸發器,對於innodb引擎,當觸發器執行失敗時,相應sql也會執行失敗,所以數據庫同步也會失敗。
 
4.2   創建、刪除觸發器寫bin-log
 
創建和刪除觸發器的語句也會寫入bin-log裡,所以也會如一般的insert,update,delete語句一樣同步到下游數據庫中,即上游創建觸發器,下游也會創建。
 
這裡再引出兩個小問題:有同步關系dbA?dbB,
 
1)   在dbA上創建一個觸發器,如果dbB上已經有同表同類型的觸發器,同步狀態如何?
 
2)   在dbB上刪除一個觸發器,如果dbB上沒有對應觸發器,同步狀態如何?
 
這兩個問題可以類比同步中的insert語句和delete語句,答案就是
 
1)   同步失敗,因為不允許重復創建同表同類型的觸發器;
 
2)   同步正常,因為drop一個不存在的觸發器,不影響運行結果;
 
5   Mysql觸發器經典案例
 
5.1   案例1 一條sql涉及多個表的update時,觸發得到update之前的舊值
 
【現象】表test_info上建有觸發器如下:
 
CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER trig_test_info_update
AFTER UPDATE
ON FC_Word.test_info FOR EACH ROW
BEGIN
DECLARE tlevel INTEGER DEFAULT 0;
DECLARE ttype INTEGER DEFAULT 0;
SET tlevel = 4;
SET ttype = 33;
INSERT INTO TEST_Output.fcevent (te, le, uid, pid, uid, wid, bi, mbid, wl) SELECT ttype, tlevel, NEW.uid, NEW.pid, NEW.uid, NEW.wid, NEW.bi, NEW.mbid, wl FROM TEST_Word.wext2 where wid = NEW.wid;
/*。。。其余部分邏輯省略*/
END IF;
END;
 
這個觸發器程序有點長,可以單看飄黃的兩句,即更新操作滿足第一個條件執行飄黃語句時,觸發器的行為。觸發器是建立在test_info表上的,飄黃語句中可以看到,也需要查詢wext2表。
 
執行如下sql1:
 
Update test_info a, wext2 b set a.th=(a.th+1), a.w4=(a.w4&8), b.wl=NULL where a.wid=b.wid and a.wid=142394379;
 
可以看到sql中既修改了test_info2表,同時修改了wext2表,程序原意是觸發得到wext2表wl字段修改後的新值(即NULL);不過實驗得到,執行上述sql後,觸發器程序查詢到的wurl是sql修改之前的舊值。
 
再執行下面類似sql2:
 
Update wext2 a, test_info2 b set b.th=(b.th+1), b.w4=(b.w4&8), a.wl=NULL where a.wid=b.wid and a.wid=142394379;
 
實驗的到,執行上述sql後,觸發器程序查詢到的wurl是sql修改之後的新值。
 
【原因】原因當然與sql中的別名a,b無關,而是和wext2表和test_info表的書寫順序有關。如本文3.4部分所述,一條sql涉及多個表的update操作時,數據表字段、觸發器執行順序是mysql源碼包裝過的。在執行上述sql1時,先執行test_info的更新,然後是after觸發器,最後是wext2的更新,也就是說,在執行after觸發器時,wext2還沒有進行更新,所以觸發得到的是舊值。而執行sql2時,先執行wext2更新,然後是test_info更新,最後是after觸發器,也就是說,在執行after觸發器時,wext2已經更新完畢,所以出去得到的是新值。
 
引起上述現象是順序關系的,無論該表是否支持事務。在使用復雜的sql時,由於有些復雜的sql是mysql自己定義的,所以存在不確定性,存在風險,使用簡單的sql比較可控。
 
5.2   案例2 mysql5.0.19版本修改表結構後觸發器失效
 
【現象】userpref表上建有after類型觸發器,修改userpref表的外鍵關聯後,在userpref表中的新增記錄沒有觸發下來,即觸發器失效。
 
【原因】mysql5.0.19修改表結構是,觸發器消失。這是mysql5.0.19的一個bug,在創建觸發器時,會把觸發器的內容保存在information_schema.TRIGGERS表中,同時在var目錄下創建觸發器的數據庫目錄下創建一個觸發器名稱為前綴,以TRN為後綴的文件,當修改觸發器的表時,information_schema.TRIGGERS表的內容會刪除,導致觸發器消失。
 
在mysql5.0.45版本中,這個bug已經被修復。Mysql5.0.45版本的觸發器,無論是修改表的索引、外鍵,還是改變表字段,觸發器都不會失效。
 
5.3   案例3 刪除數據表後觸發器失效
 
【現象】聯調環境中存在dbA?dbB,主庫dbA上沒有觸發器,在從庫dbB上的FC_Word.wnegative表,FC_Word.wbuget 表上建有觸發器;觸發器開始運行正常,期間沒有對從庫的任何直接操作,有一日發現對wnegative表上的修改無法觸發。查看從庫狀態,同步正常;用select TRIGGER_NAME from information_schema.TRIGGERS發現wnegative表上的觸發器消失了;在var/FC_Word目錄下也沒有wnegative的.TRN文件,wnegative表上的觸發器不見了。
 
【分析】查找dbB的查詢日志,發現有一條:
 
100223 18:27:45 135939 Query       DROP TABLE IF EXISTS `wnegative`
135939 Query       CREATE TABLE `wnegative` (
KEY `Index_wnegative_planid` (`planid`),
KEY `Index_wnegative_unitid` (`unitid`)
135939 Query       /*!40000 ALTER TABLE `wnegative` DISABLE KEYS */
100223 18:27:46 135939 Query       INSERT INTO `wnegative` VALUES (614,1,289026,2911155,1848481);
 
可以看到,在100223 18:27:45時,刪除了表wnegative,緊接著有創建表wnegative;查找觸發表發現,在100223 18:27:45時間後對wnegative的修改就沒有觸發了,而在這個之前對wnegative的修改是觸發正常的。故,懷疑對wnegative表的刪除使wnegative表上的觸發器也被刪除。對wnegative表的刪除是在主庫dbA上操作後,被同步到dbB上。
 
【原因】在刪除wnegative表時,mysql同時刪除了wegative表上的觸發器。
 
可以通過下面實驗證明上述猜測:
 
1)   首先在wnegative建立after insert型觸發器;
2)   增加一條wnegative中記錄;
3)   查看結果發現觸發器正確觸發;
4)   刪除wnegative表;
5)   使用select TRIGGER_NAME from information_schema.TRIGGERS查看所有觸發器,wnegative表上觸發器已經不存在了;同時到var/FC_Word目錄下,對應觸發器的.TRN文件也不存在了;
6)   重新創建wnegative表,並增加一條wnegative中記錄;沒有了wnegative表上觸發器,自然也不能觸發任何結果。
 
6   結束語
 
Mysql中的觸發器功能已經在鳳巢系統的各個模塊中有廣泛應用,究其細節,還有很多值得注意的地方;本文建立在實驗和案例的基礎上,數據庫基於線上系統使用的mysql5.0.45版本,分析了觸發器相關的一些特殊情況下msyql的處理方式。


摘自 磊.Y的博客

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved