MySQL（十一）之觸發器

標籤：set   情況   簡單   視圖   字元   結束   table   建表   名稱   

上一篇介紹的是比較簡單的視圖，其實用起來是相對比較簡單的，以後有什麼更多的關於視圖的用法，到時候在自己補充。接下來讓我們一起瞭解一下觸發器的使用！

一、觸發器概述　 1.1、什麼是觸發器

　　觸發器（Trigger）：監視某種情況，並觸發某種操作。在MySQL Server裡面也就是對某一個表的一定的操作，觸發某種條件（Insert,Update,Delete 等），從而自動執行的一段程式。

　　注意：你必須擁有相當大的許可權才能建立觸發器（CREATE TRIGGER），如果你已經是Root使用者，那麼就足夠了。這跟SQL的標準有所不同。

　1.2、觸發器作用

　　那麼為什麼要使用資料庫物件觸發器呢？在具體開發項目時，經常會遇到如下執行個體：
　　　　1）在學生表中擁有欄位學生姓名，欄位學生總數，每當添加一條學生資訊時，學生的總數就必須同時更改。
　　　　2）在學生表中還會有學生姓名的縮寫，學生住址等欄位，新增學生資訊時，往往需要檢查電話、郵箱等格式是否正確。
　　上面的例子使用觸發器完成時具有這樣的特點，需要在表發生改變時，自動進行一些處理。MySQL在觸發DELETE/UPDATE/INSERT語句時就會自動執行所設定的操作，其他SQL語句則不會啟用觸發器。

　1.3、觸發器四要素

　　監視地點：table
　　監聽事件：insert/update/delete
　　觸發時間：after/before
　　觸發事件：insert/update/delete

二、觸發器用法　2.1、觸發器文法

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

　　　簡單的寫就是這樣：

　　create trigger tg_name after/before insert/update/delete on table　　for each row ####這句話在MySQL中是固定的　　begin　　sql語句;　　end$

 

　　分析：由於在以上程式碼片段中的“sql語句;”是以分號結尾，所以需要將MySQL中的結尾標誌換成“$”，更換MySQL命令結束標誌的命令：delimiter $;
　　　　   以上觸發器文法中的各個段的顏色與四要素對應查看理解。

　2.2、建立觸發器

　　1）建立兩張表

　　create table tb_goods(id int primary key auto_increment,name varchar(20),num int);　　create table tb_orders(id int primary key auto_increment,good_id int,much int);　

 

　　2）在商品表中插入資料

　　insert into tb_goods(name,num)values(‘商品1‘,10),(‘商品2‘,10),(‘商品3‘,10);

 

　　3)假如我們賣了3個商品1

　　沒有觸發器：

　　　　往訂單表中插入一條記錄：insert into tb_orders(good_id,much) values(1,3);
　　　　更新商品表中商品1的剩餘數量：update tb_goods set num=num-3 where id=1;　　　　

　　建立觸發器：　　　

　　create trigger tg_1 after insert on tb_orders
　　for each row
　　begin
　　update tb_goods set num=num-3;
　　end$

 

　　這個時候如果執行insert into tb_orders(good_id,much) values(1,3);會發現商品的數量變為7了，說明在插入一條訂單的時候，觸發器自動做了更新操作。　

　2.3、觸發器對值得引用

　　上述觸發器有一個問題，因為在觸發器中寫死了num和id，所以不管買哪個商品，最終更新的都是商品1的數量。這個時候，需要將觸發器中的值變為動態擷取。　　

　　對於insert來說，新插入的行用new來表示，行中的每一列的值用“new.列名”來表示：　　

　　建立能動態擷取值的觸發器：　

　　create trigger tg_2 after insert on tb_orders　　for each row　　begin　　update tb_goods set num=num-new.much where id=new.id;　　end$

 

　　刪除第一個觸發器：drop trigger tg_1;
　　插入一條訂單記錄：insert into tb_orders(good_id,much) values(2,3)$
　　執行完發現商品的數量變為7了，這樣子就是合適的。

三、觸發器執行個體

　　1）建立表tab1　

　　DROP TABLE IF EXISTS tab1;　　CREATE TABLE tab1(　　    tab1_id varchar(11)　　);

　　2）建立表tab2

　　DROP TABLE IF EXISTS tab2;　　CREATE TABLE tab2(　　    tab2_id varchar(11)　　);

　　3）建立觸發器：t_afterinsert_on_tab1　　　

DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1FOR EACH ROWBEGIN     insert into tab2(tab2_id) values(new.tab1_id);END;

　　4）想tab1插入資料　

INSERT INTO tab1(tab1_id) values(‘0001‘);

　　5）查看變化　

SELECT * FROM tab1;SELECT * FROM tab2;

 

MySQL（十一）之觸發器