(15)mysql中的觸發器，15mysql中觸發器

(15)mysql中的觸發器，15mysql中觸發器
概述

觸發器，顧名思義就是當某個事情（事件）發生時候，執行某一段程式。觸發器有四大要素：監視地點（table_name）、監視事件（insert/update/delete）、觸發時間（before/after）、觸發執行程式（insert/update/delete）

文法

#建立觸發器create trigger triggerName觸發時間 監視事件 on 表名for each rowbeginsql語句(觸發事件)end————————————————————————————————————#刪除觸發器drop trigger [database_name.]trigger_name;————————————————————————————————————#查看觸發器show triggers;

• 觸發器只能建立在永久表（permanent table）上。
• 同一個表 、同一個觸發時間、相同的監視事件，只能定義一個觸發器。
• 使用old（更新前的行資料）、new（更新後的行資料）來引用觸發器中變化的記錄內容。
• 觸發器只支援行級觸發，不支援語句級觸發，因此當處理大資料集的時候可能效率很低。。
• 觸發執行程式不能調用將資料返回用戶端的預存程序/函數，但允許儲存程式通過參數（即out/inout參數）將資料返回觸發執行程式。
• 觸發器不能執行事務操作。
• 觸發器不能保證原子性，例如在MYISAM中，當一個更新觸發器在更新一個表後，觸發對另外一個表的更新，若觸發器失敗，不會復原第一個表的更新。InnoDB中的觸發器和操作則是在一個事務中完成，是原子操作。
• after和before的區別：after是先完成資料的增刪改，再觸發，觸發的語句晚於監視的增刪改操作，無法影響前面的增刪改動作；也就是說先插入訂單記錄，再更新商品的數量；before是先完成觸發，再增刪改，觸發的語句先於監視的增刪改，我們就有機會判斷，修改即將發生的操作

作用

• 安全：可以基於資料庫的值使使用者具有操作資料庫的某種權利。例如：可以基於時間限制使用者的操作，例如不允許下班後和節假日修改資料庫資料。
• 審計：可以跟蹤使用者對資料庫的操作。例如：審計使用者操作資料庫的語句。
• 實現複雜的資料完整性規則：實現非標準的資料完整性檢查和約束。觸發器可產生比規則更為複雜的限制。與規則不同，觸發器可以引用列或資料庫物件。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。
• 實現複雜的非標準的資料庫相關完整性規則：觸發器可以對資料庫中相關的表進行連環更新。例如：在修改或刪除時級聯修改或刪除其它表中的與之匹配的行。
• 同步即時地複製表中的資料。
• 自動計算資料值，如果資料的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低於5萬元則立即給財務人員發送警告資料。

舉例

建立兩張表goods(商品表)和order_t（訂單表）

舉例一
我要下一個3個商品1的訂單，需要兩步走，插入一條資料到訂單表，更新商品表中的商品1的num列。

#沒有使用觸發器insert order_t(gid,much) values('1',3);update goods set num=num-3 where id=1;

現在，我先建立一個觸發器

delimiter $$create trigger test_tr1after insert on order_tfor each rowbeginupdate goods set num=num-3 where id=1;end $$delimiter ;

建立完了以後，我只需執行一條就可以完成上面的任務。

#使用觸發器insert order_t(gid,much) values('1',3);

會發現商品1的數量變為7了，說明在我們插入一條訂單的時候，觸發器自動幫我們做了更新操作。

舉例二
現在會有一個問題，因為我們觸發器裡面num和id都是寫死的，所以不管我們買哪個商品，最終更新的都是商品1的數量。比如：我們往訂單表再插入一條記錄：insert into o(gid,much) values(2,3),執行完後會發現商品1的數量變4了，而商品2的數量沒變，這樣顯然不是我們想要的結果。我們需要改改我們之前建立的觸發器。
對於insert而言，新插入的行用new來表示，行中的每一列的值用new.列名來表示。
改觸發器

delimiter $$create trigger test_tr1after insert on order_tfor each rowbeginupdate goods set num=num-new.much where id=new.gid;end $$delimiter ;

再來測試一下，插入一條訂單記錄：

insert into o(gid,much) values(2,3)

執行完發現商品2的數量變為7了，現在就對了。

現在還存在兩種情況：

舉例三
當使用者撤銷一個訂單的時候，我們這邊直接刪除一個訂單，我們是不是需要把對應的商品數量再加回去呢？
對於delete而言：原本有一行,後來被刪除，想引用被刪除的這一行，用old來表示，old.列名可以引用被刪除的行的值。

delimiter $$create trigger test_tri2after delete on order_tfor each rowbeginupdate goods set num = num + old.much where id = old.gid;end $$delimiter ;

再執行

delete from order_t where id = 2;

會發現商品2的數量又變為10了。

舉例四
當使用者修改一個訂單的數量時，我們觸發器修改怎麼寫?
對於update而言：被修改的行，修改前的資料，用old來表示，old.列名引用被修改之前行中的值；修改的後的資料，用new來表示，new.列名引用被修改之後行中的值。

delimiter $$create trigger test_trg3after update on order_tfor each rowbeginupdate goods set num = num+old.much-new.much where id = old/new.gid;end $$delimiter ;

我們再修改插入的訂單記錄:

update order_t set much = 5 where id = 1;

我們變為買5個商品1，這時候再查詢商品表就會發現商品1的數量只剩5了，說明我們的觸發器發揮作用了。

參考

http://blog.csdn.net/tonyxf121/article/details/8255782
http://www.cnblogs.com/zzwlovegfj/archive/2012/07/04/2576989.html