獵八哥淺談MYSQL觸發器

標籤：情境   sql   查看   png   更改   ble   修改   val   charset   

什麼是MYSQL觸發器，我們先瞭解一下觸發的意思。觸發的字面意思是指因觸動而激發起某種反應。

MYSQL必知必會中對觸發器的解釋是：MySQL響應以下任意語句而自動執行的一條MySQL語句（或位於 BEGIN 和 END 語句之間的一組語句）。其中以下任意語句是指：delete，insert，update。

我對mysql中觸發器的理解是mysql資料庫的資料內容造成改變時（增加、刪除、添加）執行的一個行為。

特別注意：當你進行查詢資料時無法調動觸發器（我的理解是此時資料庫中的內容無法發生改變調用觸發器沒有意義，因為觸發器的作用是進行了資料庫的備份，保證安全性等）。觸發器被調動後所執行的行為（或者說是MySQL語句）中有增加、刪除、添加。

觸發器的作用和意義：資料備份，資料同步，保證安全性。具體請看以下幾種案例。SHOW TRIGGERS; 查看錶中所有觸發器。

1.案例一，使用觸發器用來資料備份。

情境：在學生表中，在刪除id為3的學生資訊之前時，將這條要刪除的資訊插入到studentbackup表中，用來做備份。執行delete時產生觸發行為。具體代碼如下（有解析）。

學生表CREATE TABLE `student` (  `id` int(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  `zongfen` int(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=gbk學生備份表CREATE TABLE `studentbackup` (  `id` int(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  `zongfen` int(255) DEFAULT NULL,    deletetime datetime,   PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=gbk建立觸發器CREATE trigger deletestudent before delete on studentfor each ROWbegin     insert into studentbackup (id,name,zongfen,deletetime)values(OLD.id,OLD.name,OLD.zongfen,NOW());end;

解釋：
CREATE trigger deletestudent是指建立一個名為deletestudent的觸發器，由於是在執行之前做備份所以此處使用before，

delete on student是指student執行delete行為時有觸發行為，
for each row的漢語意思是每一行，用在這裡表示當每一行受到影響時，觸發器都會被啟動，

begin，end是觸發器開始和結束的標記，

begin和end之間的語句是觸發器被調動後所執行的行為，這裡新增了一個欄位deletetime，它用來記錄刪除時間，有關mysql中時間的調取方法我會在其他部落格中做詳細說明。

執行刪除語句

delete from student where id=3;

執行完這條delete後，我們會發現學生表的一條資訊被刪除，而這條刪除的資訊在備份表中被插入。

 刪除一條資訊前後兩表的資訊

刪除之前

刪除之後

 2.案例二，使用觸發器用來資料同步。

 情境：在學生表中有四條資訊，學生1表中有四條相同的資訊，當學生表中插入一條資訊時，學生1表中會產生一條相同的新的資訊（即學生表1和學生表的資訊均相同）。執行insert時產生觸發行為。

學生表1
CREATE TABLE `student1` (  `id` int(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  `zongfen` int(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=gbk

建立觸發器

CREATE trigger insertstudent after insert on studentfor each ROWbegin     insert into student1 (id,name,zongfen)values(new.id,new.name,new.zongfen);end;

執行插入語句

insert into student (id,name,zongfen) values(5,‘e‘,195);

插入前後兩表內容：

插入之前

插入之後

　　

3.案例三：使用觸發器用來資料備份和同步。

更改學生表的資訊，這個情況比案例一和案例二都較為複雜。因為當進行學生表資訊更新時，在更改之前要將資訊進行備份，更改之後要將更改後的資訊更新到同步表中。話不多說直接上代碼。

在更新之前將資料備份
CREATE trigger updatestudent before update on studentfor each ROWbegin     insert into studentbackup (id,name,zongfen,deletetime)        values(old.id,old.name,old.zongfen,NOW());end;更新之後將資料同步到同步表中，備忘一下：我們暫不考慮更新時更改id這種行為，

因為id沒有改變，所以這裡where id = old.id;或者where id = new.id;均正確，

CREATE trigger updatestudent1 after update on studentfor each ROWbegin     UPDATE student1 set name = new.name,zongfen = new.zongfen        where id = old.id;end;

執行更新語句

UPDATE student set name = ‘z‘,zongfen = 100 where id = 4;

 

資料更新前後：

更新之前

更新之後

 

 :4.案例四：將學生表中成績最高的學生資訊，匯入到maxgrade中去。話不多說，直接上代碼。

begin     DELETE from maxgrade;    select max(zongfen) into @maxzongfen from student;    select id into @idmax from student where zongfen=@maxzongfen;    select name into @namemax from student where zongfen=@maxzongfen;    insert into maxgrade(id,name,zongfen)value(@idmax,@namemax,@maxzongfen);end;insert student values(4,‘d‘,177);

之前

之後

觸發器的特點：

1.觸發行為只能在之前或者之後執行（before，after）。

2.觸發器的命名可以重複（這裡的重複是指兩個不同的表可以起一個相同的觸發器名字，但是每個表中的不同觸發器的命名必須不同；但從程式的可讀性等發麵考慮還是應該在整個資料庫中每個觸發器的命名都應該不同，這種命名重複在我看來是MYSQL的弊端）。

3.觸發器可以產生增刪改三種行為。

4.觸發器不能被覆蓋或者修改，要更改觸發器時需要先刪除它，然後建立。

刪除語句drop TRIGGER 觸發器名字;

5.begin和end之間可以插入多條sql語句。

6.觸發器內不能調用預存程序。

7.每張表最多可以建6個觸發器，分別是增加、刪除、添加行為前後。

8.視圖不支援觸發器。

 備忘一下：我得MYSQL版本為5.5.40。（查詢語句為：select version();）

獵八哥淺談MYSQL觸發器