mysql觸發器詳解

小云云原創: 2017-11-22 10:53:522079瀏覽

什麼是觸發器：

觸發器(trigger)是SQL server 提供給程式設計師和資料分析員來保證資料完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發，例如當對一個表進行操作( insert，delete， update)時就會激活它執行。觸發器經常用於加強資料的完整性約束和業務規則等。觸發器可以從 DBA_TRIGGERS ，USER_TRIGGERS 資料字典中查到。 SQL3的觸發器是一個能由系統自動執行對資料庫修改的語句。

觸發器語法：

CREATE TRIGGER 2798694f131806550efe1fce1e6b7d72 --觸發器必須有名字，最多64個字符，可能後面會附有分隔符.它和MySQL中其他物件的命名方式基本相像.
{ BEFORE | AFTER } --觸發器有執行的時間設定：可設定為事件發生前後。
{ INSERT | UPDATE | DELETE } --同樣也能設定觸發的事件：它們可以在執行insert、update或delete的過程中觸發。
ON e478b3973a5d30721d1f69a4f698b8ad --觸發器是屬於某一個表的:當在這個表上執行插入、更新或刪除操作的時候就導致觸發器的激活. 我們不能給同一張表的同一個事件安排兩個觸發器。
FOR EACH ROW --觸發器的執行間隔：FOR EACH ROW子句通知觸發器每隔一行執行一次動作，而不是對整個表執行一次。
4d0cc07441c5a78e42c97e7720963c8b --觸發器包含所要觸發的SQL語句：這裡的語句可以是任何合法的語句，包含複合語句，但是這裡的語句受的限制和函數的一樣。

一張表格只能建立一個觸發器

觸發器(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(&#39;商品1&#39;,10),(&#39;商品2&#39;,10),(&#39;商品3&#39;,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了，表示我們的觸發器發揮作用了。

以上就是這篇關於觸發器的全部內容，希望能幫助你。

mysql觸發器詳解

相關文章