轉(zhuǎn)載自http://blog.51cto.com/9291927/2095105
一、觸發(fā)器簡(jiǎn)介
1、觸發(fā)器簡(jiǎn)介
觸發(fā)器是和表關(guān)聯(lián)的特殊的存儲(chǔ)過程，可以在插入，刪除或修改表中的數(shù)據(jù)時(shí)觸發(fā)執(zhí)行，比數(shù)據(jù)庫(kù)本身標(biāo)準(zhǔn)的功能有更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。
2、觸發(fā)器的優(yōu)點(diǎn)
A、安全性
可以基于數(shù)據(jù)庫(kù)的值使用戶具有操作數(shù)據(jù)庫(kù)的某種權(quán)利?？梢曰跁r(shí)間限制用戶的操作，例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫(kù)數(shù)據(jù)；可以基于數(shù)據(jù)庫(kù)中的數(shù)據(jù)限制用戶的操作，例如不允許股票的價(jià)格的升幅一次超過10%。
B、審計(jì)
可以跟蹤用戶對(duì)數(shù)據(jù)庫(kù)的操作。???審計(jì)用戶操作數(shù)據(jù)庫(kù)的語句；把用戶對(duì)數(shù)據(jù)庫(kù)的更新寫入審計(jì)表。
C、實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則
實(shí)現(xiàn)非標(biāo)準(zhǔn)的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則更為復(fù)雜的限制。與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫(kù)對(duì)象。例如，觸發(fā)器可回退任何企圖吃進(jìn)超過自己保證金的期貨。提供可變的缺省值。
D、實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)相關(guān)完整性規(guī)則。
觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)中相關(guān)的表進(jìn)行連環(huán)更新。
??在修改或刪除時(shí)級(jí)聯(lián)修改或刪除其它表中的與之匹配的行。
??在修改或刪除時(shí)把其它表中的與之匹配的行設(shè)成NULL值。
在修改或刪除時(shí)把其它表中的與之匹配的行級(jí)聯(lián)設(shè)成缺省值。
??觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試圖進(jìn)行數(shù)據(jù)更新的事務(wù)。
E、同步實(shí)時(shí)地復(fù)制表中的數(shù)據(jù)。
F、SQL觸發(fā)器提供了運(yùn)行計(jì)劃任務(wù)的另一種方法。自動(dòng)計(jì)算數(shù)據(jù)值，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特定的處理。例如，如果公司的帳號(hào)上的資金低于5萬元?jiǎng)t立即給財(cái)務(wù)人員發(fā)送警告數(shù)據(jù)。
3、觸發(fā)器的限制
A、觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲(chǔ)程序，也不能使用采用CALL語句的動(dòng)態(tài)SQL語句，但是允許存儲(chǔ)程序通過參數(shù)將數(shù)據(jù)返回觸發(fā)程序，也就是存儲(chǔ)過程或者函數(shù)通過OUT或者INOUT類型的參數(shù)將數(shù)據(jù)返回觸發(fā)器是可以的，但是不能調(diào)用直接返回?cái)?shù)據(jù)的過程。
B、不能在觸發(fā)器中使用以顯示或隱式方式開始或結(jié)束事務(wù)的語句，如START TRANS-ACTION,COMMIT或ROLLBACK。
二、觸發(fā)器的使用
1、創(chuàng)建觸發(fā)器
創(chuàng)建觸發(fā)器的語法：
`CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt`
trigger_name：觸發(fā)器的名稱。
tirgger_time：觸發(fā)時(shí)機(jī)，為BEFORE或者AFTER。
trigger_event：觸發(fā)事件，為INSERT、DELETE或者UPDATE。
tb_name：表示建立觸發(fā)器的表名，在哪張表上建立觸發(fā)器。
trigger_stmt：觸發(fā)器的程序體，可以是一條SQL語句或者是用BEGIN和END包含的多條語句。
FOR EACH ROW表示任何一條記錄上的操作滿足觸發(fā)事件都會(huì)觸發(fā)該觸發(fā)器。
MySQL除了對(duì)INSERT、UPDATE、DELETE基本操作進(jìn)行定義外，還定義了LOAD DATA和REPLACE語句，這兩種語句也能引起上述6中類型的觸發(fā)器的觸發(fā)。
LOAD DATA 語句用于將一個(gè)文件裝入到一個(gè)數(shù)據(jù)表中，相當(dāng)與一系列的 INSERT操作。
REPLACE語句一般來說和INSERT語句很像，只是在表中有primary key或 unique索引時(shí)，如果插入的數(shù)據(jù)和原來primary key或unique索引一致時(shí)，會(huì)先刪除原來的數(shù)據(jù)，然后增加一條新數(shù)據(jù)。
INSERT型觸發(fā)器：插入某一行時(shí)激活觸發(fā)器，通過 INSERT、LOAD DATA、REPLACE語句觸發(fā)；
UPDATE型觸發(fā)器：更改某一行時(shí)激活觸發(fā)器，通過UPDATE語句觸發(fā)；
DELETE型觸發(fā)器：刪除某一行時(shí)激活觸發(fā)器，通過DELETE、REPLACE語句觸發(fā)。
變量聲明：
`DECLARE var_name[,...] type [DEFAULT value]`
對(duì)變量賦值采用SET 語句，語法為：
`SET var_name = expr [,var_name = expr] ...`
MySQL中定義了NEW和OLD，用來表示觸發(fā)器的所在表中，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)。
在INSERT型觸發(fā)器中，NEW用來表示將要（BEFORE）或已經(jīng)（AFTER）插入的新數(shù)據(jù)；
在UPDATE型觸發(fā)器中，OLD用來表示將要或已經(jīng)被修改的原數(shù)據(jù)，NEW用來表示將要或已經(jīng)修改為的新數(shù)據(jù)；
在DELETE型觸發(fā)器中，OLD用來表示將要或已經(jīng)被刪除的原數(shù)據(jù)；
使用方法：NEW.columnName（columnName為相應(yīng)數(shù)據(jù)表某一列名）
另外，OLD是只讀的，而NEW則可以在觸發(fā)器中使用SET賦值，不會(huì)再次觸發(fā)觸發(fā)器，造成循環(huán)調(diào)用。
2、刪除觸發(fā)器
`DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name`
schema_name是數(shù)據(jù)庫(kù)的名稱，是可選的。如果省略了schema，將從當(dāng)前數(shù)據(jù)庫(kù)中舍棄觸發(fā)程序。trigger_name是要?jiǎng)h除的觸發(fā)器的名稱。
3、觸發(fā)器信息查看
在MySQL中，所有的觸發(fā)器的定義都存在于INFORMATION_SCHEMA數(shù)據(jù)庫(kù)的triggers表中，可以通過查詢命令SELECT來查看，具體語法如下：
`SHOW TRIGGERS [FROM schema_name];`
觸發(fā)器的執(zhí)行順序
InnoDB數(shù)據(jù)庫(kù)，若SQL語句或觸發(fā)器執(zhí)行失敗，MySQL會(huì)回滾事務(wù)，有：
A、如果BEFORE觸發(fā)器執(zhí)行失敗，SQL無法正確執(zhí)行。
B、SQL執(zhí)行失敗時(shí)，AFTER型觸發(fā)器不會(huì)觸發(fā)。
C、AFTER類型的觸發(fā)器執(zhí)行失敗，SQL會(huì)回滾。
MySQL的觸發(fā)器是按照BEFORE觸發(fā)器、行操作、AFTER觸發(fā)器的順序執(zhí)行的，其中任何一步發(fā)生錯(cuò)誤都不會(huì)繼續(xù)執(zhí)行剩下的操作，如果對(duì)事務(wù)表進(jìn)行的操作，如果出現(xiàn)錯(cuò)誤，那么將會(huì)被回滾，如果對(duì)非事務(wù)表進(jìn)行操作，那么就無法回滾，數(shù)據(jù)可能會(huì)出錯(cuò)。
