MySQL觸發器喝更新操作

標籤：右鍵   增加   約束   執行   into   sdn   固定   表示   arch   

一、觸發器概念

觸發器（trigger）：監視某種情況，並觸發某種操作，它是提供給程式員和資料分析員來保證資料完整性的一種方法，它是與表事件相關的特殊的預存程序，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發，例如當對一個表進行操作（ insert，delete， update）時就會啟用它執行。

觸發器經常用於加強資料的完整性條件約束和商務規則等。 觸發器建立文法四要素：
1.監視地點(table)
2.監視事件(insert/update/delete)
3.觸發時間(after/before)
4.觸發事件(insert/update/delete)
觸發器基本文法如下所示：

 

 

 其中：trigger_time是觸發器的觸發事件，可以為before（在檢查約束前觸發）或after（在檢查約束後觸發）；trigger_event是觸發器的觸發事件，包括insert、update和delete，需注意對同一個表相同觸發時間的相同觸發事件，只能定義一個觸發器；可以使用old和new來引用觸發器中發生變化的記錄內容。
觸發器SQL文法：

create trigger triggerName after/before insert/update/delete on 表名 for each row #這句話在mysql是固定的 begin  sql語句; end; 

 

 推薦大家閱讀：http://www.cnblogs.com/zzwlovegfj/archive/2012/07/04/2576989.html （MySQL觸發器（trigger）

同時使用Navicat for MySQL建立觸發器方法如所示，但是建議使用語句操作。
首先在Navicat for MySQL找到需要建立觸發器對應的表，右鍵“設計表”，然後建立觸發器。

 

二、簡單的Insert觸發器

 假設存在一張學生表（student），包括學生的基本資料，學號（stuid）為主鍵。

 

另外存在一張成績表（cj），對應每個學生包括一個值。其中number表示序號為主鍵，自動遞增序列。它在插入過程中預設自增。同時假設成績表中包括學生姓名和學號。

 

該成績表目前沒有值，先需要設計一個觸發器，當增加新的學生時，需要在成績表中插入對應的學生資訊，至於具體math、chinese、english後面由老師打分更新即可。
那麼，如何設計觸發器呢？
1.首先它是一個插入Insert觸發器，是建立在表student上的；
2.然後是after，插入後的事件；
3.事件內容是插入成績表，主需要插入學生的學號和姓名，number為自增，而成績目前不需要。
注意：new表示student中新插入的值。 

create trigger ins_stu after insert on student for each row begin  insert into cj ( stu_id, stu_name)  values( new.stuid, new.username); end; 

 

 建立的觸發器如所示：

 

然後插入資料：

insert student values (‘eastmount‘,‘111111‘,‘6‘,‘1991-12-05‘);

同時插入兩個資料，觸發器正確執行了~

 注意：建立觸發器和表一樣，建議增加判斷：DROP TRIGGER IF EXISTS `ins_stu`;

三、判斷值後調用觸發器

這裡簡單講述幾個判斷插入類型的觸發器。
比如觸發器調用，當插入時間小時為20時，對資料進行插入：

DROP TRIGGER IF EXISTS `ins_info`; create trigger ins_info after insert on nhfxelect for each row begin  if HOUR(new.RecordTime)=‘20‘ then  insert into nhfxbyhour (UnitDepName, UnitDepCode, ElectCost, TimeJG, RecordTime)  values( ‘數統學院‘, ‘1‘, new.USERKWH, ‘20‘, new.RecordTime);  end if; end; 

 

 這個觸發器中，RecordTime為datetime類型，如"2016-08-28 20:10:00"，這時hour()這個值為20才能插入；否則資料不能插入。同時可以date_format(new.RecordTime, ‘%Y-%m-%d‘)判斷日期為某天或某年某月進行插入。
同時，再如更新觸發器，如果設定的值為某個範圍，才進行操作或性別為"男"或"女"才進行操作。

基本文法：if 判斷條件 thensql語句;end if;

四、Update觸發器-即時更新

假設存在一個即時插入資料的伺服器，例如學生的消費金額或用電量等。
StuCost：學生的用電資料，即時插入，Cost為每30秒消費金額，RecordTime為每分鐘插入時間，datetime類型；
StuCostbyHour：統計學生一小時的消費金額，HourCost為金額總數，按小時統計，TimeJD時間段，1~24，對應每小時，RecordTime為統計時間。
現在需要設計一個即時更新觸發器，當插入消費資料時，按小時統計學生的消費金額，同理，用電量等。

DROP TRIGGER IF EXISTS `upd_info`; create trigger upd_info after insert on StuCost for each row begin  update StuCostbyHour set HourCost = HourCost + new.Cost  where (TimeJD = hour(new.RecordTime) + 1) and date_format(new.RecordTime, ‘%Y-%m-%d‘) = date_format(RecordTime, ‘%Y-%m-%d‘); end; 

 

 SQL語句中，需要擷取插入的時間，然後通過TimeJD時間段和日期RecordTime找到對應的值，然後進行累加即可。如所示：

 

左邊是即時插入資料，右邊是觸發器更新加和。後面會介紹MySQL即時事件：http://blog.csdn.net/zlp5201/article/details/38309095 

MySQL觸發器喝更新操作