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了，說明我們的觸發器發揮作用了。

---------------------------------------------------------------------------------------------

 

三、after和before的區別：

 

after是先完成資料的增刪改，再觸發，觸發的語句晚於監視的增刪改操作，無法影響前面的增刪改動作；也就是說先插入訂單記錄，再更新商品的數量；

 

before是先完成觸發，再增刪改，觸發的語句先於監視的增刪改，我們就有機會判斷，修改即將發生的操作；

 

 

 

我們用一個典型案例來區分它們的區別，建立一個觸發器：

 

#監視地點: 商品表o

 

#監視事件：insert

 

#觸發時間：before

 

#觸發事件：update

 

案例：當新增一條訂單記錄時，判斷訂單的商品數量，如果數量大於10，就預設改為10

 

create trigger tg6

 

before insert on o

 

for each row

 

begin

 

　　if new.much > 10 then

 

　　　　set new.much = 10;

 

　　end if;

 

　　update g set num = num - new.much where id = new.gid;

 

end$

 

執行完，把之前建立的after觸發器刪掉，再來插入一條訂單記錄：

 

insert into o(gid,much) valus(1,20)$

 

執行完會發現訂單記錄的數量變為10，商品1的數量變為0了，就不會出現負數了。

 

 

mysql觸發器(trigger)