mysql中before和after的區別有哪些

這篇文章主要介紹了mysql中before和after的區別有哪些，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

觸發器（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 ;

感謝你能夠認真閱讀完這篇文章，希望小編分享的“mysql中before和after的區別有哪些”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!