mysql之觸發器trigger

標籤：begin   blog   highlight   變化   auto   需要   表名   sql語句   插入   

觸發器(trigger)：監視某種情況，並觸發某種操作。觸發器建立文法四要素：1.監視地點(table) 2.監視事件(insert/update/delete) 3.觸發時間(after/before) 4.觸發事件(insert/update/delete)文法：create trigger triggerNameafter/before insert/update/delete on 表名for each row   #這句話在mysql是固定的beginsql語句;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 tg1after insert on ofor each row beginupdate 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 tg2after insert on ofor each row beginupdate 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 tg3after delete on ofor each rowbeginupdate 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 tg4after update on ofor each rowbeginupdate 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了，說明我們的觸發器發揮作用了。

 

mysql之觸發器trigger