MYSQL觸發器

觸發器（trigger）是個特殊的存儲過程， 觸發器無需人工調用,程式滿足一定條件時自動執行.存儲過程需要手工調用,指定參數等;

有的時候，可以用觸發器來維護資料的完整性。如我有一個表ge_element，該表中有一個region_id，對應到ge_region表中的 id，但是，region_id是可以為空的，是以不應該設定外鍵限制，而我在删除ge_region表中的記錄時，希望把在ge_element表中被引用到的記錄的region_id設為0,因為沒有資料庫的外鍵限制，我隻能在程式中操作，但我又不想通過程式來操作，因為引用region_id的表可能不隻ge_element一個，這時，就可能用到觸發器，在删除ge_region表中的記錄時，把被引用的表中的region_id設為0。

4.觸發器與表的關系：

觸發器是屬于一個表的，當在這個表上執行insert, update, delete操作時，就會導緻相應的觸發器被激活；

不能給同一個表的同一個操作建立兩個不同的觸發器 。

5.觸發間隔：

for each row 子句通知觸發器每隔一行執行一次動作，而不是對整下表執行一次。

6.觸發的sql語句：

觸發器包含所要觸發的sql語句：這裡的語句可以是任何合法的語句，包括複合語句，但是這裡的語句受的限制和函數的一樣。

複合語句(begin / end)是合法的.

流控制（flow-of-control）語句(if, case, while, loop, while, repeat, leave,iterate)也是合法的.

變量聲明(declare)以及指派(set)是合法的.

允許條件聲明.

異常處理聲明也是允許的.

但是在這裡要記住函數有受限條件:不能在函數中通路表.是以在函數中使用以下語句是非法的。

7.建立觸發器的權限：

你必須要有相當大的權限才能夠建立觸發器；我在建立觸發器的時候提示要有super privilege才可以建立

一，什麼觸發器

1，個人了解

觸發器，從字面來了解，一觸即發的一個器，簡稱觸發器（哈哈，個人了解），舉個例子吧，好比天黑了，你開燈了，你看到東西了。你放炮仗，點燃了，一會就炸了。

2，官方定義

觸發器（trigger）是個特殊的存儲過程，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發 ，比如當對一個表進行操作（ insert，delete， update）時就會激活它執行。觸發器經常用于加強資料的完整性限制和業務規則等。 觸發器可以從 dba_triggers ，user_triggers 資料字典中查到。

觸發器有一個非常好的特性就是:觸發器可以禁止或復原違反引用完整性的更改，進而取消所嘗試的資料修改 。

什麼意思，舉個例子解釋一下，街機遊戲大家都玩過吧，闖過一關，闖下一關，有一關沒闖過就要從第一關開始。觸發器根這個類似。

官方解釋如下

觸發程式視為單一交易中的一部份，是以可以由原觸發程式還原交易，如果在交易過程中偵測到嚴重的錯誤（如使用者中斷連線），則會自動還原整個交易。

他的作用很明顯了，可以保重資料的完整性，下面有一個執行個體來說明他的好處，以及如果使編寫代碼不那麼複雜

二，觸發器文法

create trigger trigger_name  

trigger_time  

trigger_event  

on tbl_name  

for each row trigger_stmt  

觸發程式是與表有關的命名資料庫對象，當表上出現特定事件時，将激活該對象。

觸發程式與命名為tbl_name 的表相關。tbl_name 必須引用永久性表。不能将觸發程式與temporary表或視圖關聯起來。

trigger_time 是觸發程式的動作時間。它可以是before或after，以指明觸發程式是在激活它的語句之前或之後觸發。

trigger_event 指明了激活觸發程式的語句的類型。trigger_event 可以是下述值之一：

·         insert：将新行插入表時激活觸發程式，例如，通過insert、load data和replace語句。

·         update：更改某一行時激活觸發程式，例如，通過update語句。

·         delete：從表中删除某一行時激活觸發程式，例如，通過delete和replace語句。

請注意，trigger_event 與以表操作方式激活觸發程式的sql語句并不很類似，這點很重要。例如，關于insert的before觸發程式不僅能被insert語句激活，也能被load data語句激活。

可能會造成混淆的例子之一是insert into .. on duplicate update ...文法：before insert觸發程式對于每一行将激活，後跟after insert觸發程式，或before update和after update觸發程式，具體情況取決于行上是否有重複鍵。

對于具有相同觸發程式動作時間和事件的給定表，不能有兩個觸發程式。例如，對于某一表，不能有兩個before update觸發程式。但可以有1個before update觸發程式和1個before insert觸發程式，或1個before update觸發程式和1個after update觸發程式。

trigger_stmt 是當觸發程式激活時執行的語句。如果你打算執行多個語句，可使用begin ... end複合語句結構。這樣，就能使用存儲子程式中允許的相同語句

三，建立解發器

1,使用者表user

create table `user` (  

`id` int(11) not null auto_increment comment '使用者id',  

`name` varchar(50) not null default '' comment '名稱',  

`sex` int(1) not null default '0' comment '0為男，1為女',  

primary key  (`id`)  

) engine=myisam  default charset=utf8 ;  

insert into `user` (`id`, `name`, `sex`) values  

(1, '張映', 0),  

(2, 'tank', 0);  

 2,評論表comment

create table `comment` (  

`c_id` int(11) not null auto_increment comment '評論id',  

`u_id` int(11) not null comment '使用者id',  

`name` varchar(50) not null default '' comment '使用者名稱',  

`content` varchar(1000) not null default '' comment '評論内容',  

primary key  (`c_id`)  

insert into `comment` (`c_id`, `u_id`, `name`, `content`) values  

(1, 1, '張映', '觸發器測試'),  

(2, 1, '張映', '解決字段備援'),  

(3, 2, 'tank', '使代碼更簡單');  

在這裡有一個備援字段name ，我們在讀取評論進可以用聯合查尋來找到user表中的名字，為什麼要有備援字段呢，因簡單的sql語句執行效率更高，但不是備援字段越多越好，備援字段多了，同樣會增加資料庫負擔 .

我要做的事情是，當我更新user表的name時，觸發器同時更新comment表，就不要寫php代碼去更新了，當使用者被删除user時，comment表中，有關該使用者的資料将被删除

3，更新name觸發器

delimiter ||      //mysql 預設結束符号是分号，當你在寫觸發器或者存儲過程時有分号出現，會中止轉而執行  

drop trigger if exists updatename||    //删除同名的觸發器，  

create trigger updatename after update on user for each row   //建立觸發器，  

begin  

//old,new都是代表目前操作的記錄行，你把它當成表名，也行;  

if new.name!=old.name then   //當表中使用者名稱發生變化時,執行  

update comment set comment.name=new.name where comment.u_id=old.id;  

end if;  

end||  

delimiter ;   

 3，觸發器删除comment資料

delimiter ||  

drop trigger if exists deletecomment||  

create trigger deletecomment before delete on user for each row  

delete from comment where comment.u_id=old.id;  

delimiter ;  

有一點很讓人郁悶，就是寫好的觸發器代碼，不能修改，你要删除掉重建，郁悶中，對了還有一點就是phpmyadmin，有的能建立觸發器，有的不能，有的能建立，但建立了看不到。在研究一下。

4,測試觸發器是否可用

a，測試updata觸發器

update user set name='蒼鷹'  where id = 1;

更新後去comment表裡面看看，裡面name字段裡面的段有沒有改變

b，測試delete觸發器

delete from user  where id = 1;

四，觸發器的優點

1，觸發器的"自動性"

對程式員來說，觸發器是看不到的，但是他的确做事情了，如果不用觸發器的話，你更新了user表的name字段時，你還要寫代碼去更新其他表裡面的備援字段，我舉例子，隻是一張表，如果是幾張表都有備援字段呢，你的代碼是不是要寫很多呢，看上去是不是很不爽呢。

2，觸發器的資料完整性

觸發器有復原性，舉個例子，我發現我很喜歡舉子，就是你要更新五張表的資料，不會出現更新了二個張表，而另外三張表沒有更新。

但是如果是用php代碼去寫的話，就有可能出現這種情況的，比如你更新了二張表的資料，這個時候，資料庫挂掉了。你就郁悶了，有的更新了，有的沒更新。這樣頁面顯示不一緻了，變有bug了。