mysql觸發器案例分析以及before和after的區別

賀子_DBA時代發表於2018-04-24
MySql觸發器
觸發器（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來引用觸發器中發生變化的記錄內容。
需要注意的：
1）需注意對同一個表的相同觸發時間(after/before)的相同觸發事件(insert/update/delete)，只能定義一個觸發器，否則報錯
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
2）mysql觸發器中的if判斷語法格式如下：(1).迴圈中還可以有迴圈，(2).else後面沒有then, (3).elseif！！！！不是else if !!
if...then{
if...then{}
end if;
if...then{}
end if;
...
}
elseif...then..
else
end if ;
注意可以使用兩個if迴圈，各自end if即可
3)注意mysql觸發器中的before和after的區別：
before：(insert、update)可以對new進行修改，
after:不能對new進行修改，兩者都不能修改old資料。
對於INSERT語句, 只有NEW是合法的；
對於DELETE語句，只有OLD才合法；
對於UPDATE語句，NEW、OLD可以同時使用。
after是先完成資料的增刪改，再觸發，觸發的語句晚於監視的增刪改操作，無法影響前面的增刪改動作；也就是說先插入訂單記錄，再更新商品的數量；
before是先完成觸發，再增刪改，觸發的語句先於監視的增刪改，這樣就可以對new進行修改了；
摘自網路的一個例子說明：
首先我們來建立兩張表：
#商品表
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('商品1',10),('商品2',10),('商品3',10);
我們藉助觸發器來完成下訂單之後，自動對商品表中相應商品做減法；如下：
create trigger tg2
after insert on o
for each row
begin
update g set num=num-new.much where id=new.gid;
end$
但是有個問題是，如果下訂單數超過商品總數時，那麼會導致商品表中產生負數，這樣我們可以藉助before來對訂單中new值進行修改，保證商品表不會出現負數；
案例：當新增一條訂單記錄時，判斷訂單的商品數量，如果數量大於10，就預設改為10
DELIMITER $
create trigger tg6
beforeinsert on o
for each row
begin
　　if new.much > 10 then
　　　　set new.much = 10;
　　end if;
　　update g set num = num - new.much where id = new.gid;
end $
DELIMITER ;
4)不是說一個事務出發一次，如下這個事務修改了10行數，他會觸發10次：
mysql> update blocks_infos set infos_id=1 where infos_id=2;
Query OK, 10 rows affected (0.22 sec)
Rows matched: 10 Changed: 10 Warnings: 0
5)針對before的情況，如果觸發的操作沒有成功，會導致原本的觸發事件也不成功；
接下來記錄下，我寫的案例，當對一個表做增刪改的時候，觸發對另一表做相應的操作，
例如下面，如果begin後面有語法錯誤或者執行錯誤，那麼會導致前面的delete失敗；
DELIMITER $
create trigger tri_delete_blocks_infos1 before delete
on blocks_infos for each row
begin
DECLARE h int;
set h=(select intc from bidinfo.v_publish_info where id=old.infos_id);
if h is null then
update bidinfo.v_publish_info set intc=1 where id= old.infos_id;
else
update bidinfo.v_publish_info set intc=intc+1 where id= old.infos_id;
end if;
end $
DELIMITER ;
1.關於insert的觸發器：
我們的要求是當向blocks_infos的時候，先判斷blocks_infos_opensearch表中有沒有新insert的infos_id，如果有就相應的update,沒有的話就insert，可以如下兩種方法：
方法一使用replace：
DELIMITER $
create trigger tri_insert_blocks_infos after insert
on blocks_infos for each row
begin
replace into blocks_infos_opensearch (infos_id,blocks) select infos_id,group_concat(blocks_id) blocks from blocks_infos where infos_id=new.infos_id group by infos_id;
end $
DELIMITER ;
注意關於MySQL replace into 有三種形式（into關鍵字可以省略）：
1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...
方法二：用if判斷：
DELIMITER $
create trigger tri_insert_blocks_infos after insert
on blocks_infos for each row
begin
DECLARE c INT;
set c=(SELECT COUNT(infos_id) FROM blocks_infos WHERE infos_id=new.infos_id);
if c=1 then
insert into blocks_infos_opensearch select infos_id,GROUP_CONCAT(blocks_id) blocks FROM blocks_infos WHERE infos_id=new.infos_id;
elseif c>1 then
UPDATE blocks_infos_opensearch SET blocks= (SELECT GROUP_CONCAT(blocks_id) blocks FROM blocks_infos WHERE infos_id=new.infos_id ) WHERE infos_id= new.infos_id;
end if ;
end $
DELIMITER ;
2.關於delete的觸發器：
DELIMITER $
CREATE TRIGGER tri_delete_blocks_infos after DELETE
ON blocks_infos FOR EACH ROW
BEGIN
DECLARE c INT;
SET c=(SELECT COUNT(infos_id) FROM blocks_infos WHERE infos_id=old.infos_id);
IF c=0 THEN
DELETE FROM blocks_infos_opensearch WHERE infos_id=old.infos_id;
ELSEIF c>0 THEN
UPDATE blocks_infos_opensearch SET blocks= (SELECT GROUP_CONCAT(blocks_id) blocks FROM blocks_infos WHERE infos_id=old.infos_id ) WHERE infos_id= old.infos_id;
END IF;
END $
DELIMITER ;
3.關於update的觸發器：
DELIMITER $
CREATE TRIGGER tri_update_blocks_infos after update
ON blocks_infos FOR EACH ROW
BEGIN
DECLARE c INT;
DECLARE d varchar(1000);
DECLARE h varchar(1000);
SET c=(SELECT COUNT(infos_id) FROM blocks_infos WHERE infos_id=old.infos_id);
set d=(SELECT GROUP_CONCAT(blocks_id) blocks FROM blocks_infos WHERE infos_id=old.infos_id);
set h=(SELECT GROUP_CONCAT(blocks_id) blocks FROM blocks_infos WHERE infos_id=new.infos_id);
IF c=0 THEN
DELETE FROM blocks_infos_opensearch WHERE infos_id=old.infos_id;
ELSEIF c>0 THEN
UPDATE blocks_infos_opensearch SET blocks= d WHERE infos_id= old.infos_id;
UPDATE blocks_infos_opensearch SET blocks= h WHERE infos_id= new.infos_id;
END IF;
END $
DELIMITER ;
另一個需求是需要當對錶blocks_infos做相關處理的時候，會觸發另一個表bidinfo.v_publish_info 做相應的處理，因為前面已經建立了after insert on blocks_infos，不能再建立 after insert
on blocks_infos,所以只能建立 before insert on blocks_infos，如下建立了三個：
1)insert
DELIMITER $
create trigger tri_insert_blocks_infos1 before insert
on blocks_infos for each row
begin
DECLARE d int;
set d=(select intc from bidinfo.v_publish_info where id=new.infos_id);
if d is null then
update bidinfo.v_publish_info set intc=1 where id= new.infos_id;
else
update bidinfo.v_publish_info set intc=intc+1 where id= new.infos_id;
end if;
end $
DELIMITER ;
2)delete
DELIMITER $
create trigger tri_delete_blocks_infos1 before delete
on blocks_infos for each row
begin
DECLARE h int;
set h=(select intc from bidinfo.v_publish_info where id=old.infos_id);
if h is null then
update bidinfo.v_publish_info set intc=1 where id= old.infos_id;
else
update bidinfo.v_publish_info set intc=intc+1 where id= old.infos_id;
end if;
end $
DELIMITER ;
3)update ，注意可以只用兩個if迴圈！
DELIMITER $
create trigger tri_update_blocks_infos1 before update
on blocks_infos for each row
begin
DECLARE j int;
DECLARE i int;
set i=(select intc from bidinfo.v_publish_info where id=new.infos_id);
set j=(select intc from bidinfo.v_publish_info where id=old.infos_id);
if j is null then
update bidinfo.v_publish_info set intc=1 where id= old.infos_id;
else
update bidinfo.v_publish_info set intc=intc+1 where id= old.infos_id;
end if;
if i is null then
update bidinfo.v_publish_info set intc=1 where id= new.infos_id;
else
update bidinfo.v_publish_info set intc=intc+1 where id= new.infos_id;
end if;
end $
DELIMITER ;
小結：觸發器中的new和old,可以理解為處理過的整行資料，可以透過new.欄位名來取出那個欄位的值，並且alter和before都不能修改old的值，但是before可以修改new的值，還需要注意對同一個表的相同觸發時間(after/before)的相同觸發事件(insert/update/delete)，只能定義一個觸發器，並且before的形式的觸發器需要保證觸發端和被觸發端都得成功才能成功！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2153312/，如需轉載，請註明出處，否則將追究法律責任。

相關文章