mysql資料庫觸發器詳解

原文網址：

http://www.2cto.com/database/201202/120797.html

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;

上述觸發器執行個體使用了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，可以看到表結構：

+—————————-+————–+——+—–+———+——-+

+—————————-+————–+——+—–+———+——-+

| ACTION_ORDER | bigint(4) | NO | | 0 | |

+—————————-+————–+——+—–+———+——-+

這樣，使用者就可以按照自己的需要，檢視觸發器，比如使用如下語句檢視上述觸發器：

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

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;

即修改sql未執行成功。即如果before觸發器執行失敗，sql也會執行失敗。

3.2 如果sql執行失敗，會執行after類型的觸發器程式嗎？

1）在FC_Word.planinfo中建立after觸發器：

create trigger trigger_after_planinfo_update

after update

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;

2）檢視觸發表：

mysql> select * from FC_Output.fcevent where planid=1;

Empty set (0.00 sec)

沒有planid=1的記錄

mysql> update planinfo set showprob1=200 where planid=1;

4）由于不存在showprob1列，提示錯誤：

ERROR 1054 (42S22): Unknown column ‘showprob1’ in ‘field list’

5）再次檢視觸發表：

觸發表中沒有planid=1的記錄，sql在執行失敗時，after型觸發器不會執行。

3.3 如果after類型的觸發器程式執行失敗，sql會復原嗎？

+——- 5）再次檢視：

即修改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位置*/

table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE))

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上建立觸發器：

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

ON FC_Word.test_info FOR EACH ROW

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;

這個觸發器程式有點長，可以單看飄黃的兩句，即更新操作滿足第一個條件執行飄黃語句時，觸發器的行為。觸發器是建立在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

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的處理方式。

mysql資料庫觸發器詳解

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述