mysql中before和after的區(qū)別有哪些

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

觸發(fā)器（trigger）：監(jiān)視某種情況，并觸發(fā)某種操作，它是提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，例如當對一個表進行操作（ insert，delete， update）時就會激活它執(zhí)行。

觸發(fā)器經(jīng)常用于加強數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。 觸發(fā)器創(chuàng)建語法四要素：

1.監(jiān)視地點(table)

2.監(jiān)視事件(insert/update/delete)

3.觸發(fā)時間(after/before)

4.觸發(fā)事件(insert/update/delete)

其中：trigger_time是觸發(fā)器的觸發(fā)事件，可以為before（在檢查約束前觸發(fā)）或after（在檢查約束后觸發(fā)）；trigger_event是觸發(fā)器的觸發(fā)事件，包括insert、update和delete，可以使用old和new來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容。

需要注意的：

1）需注意對同一個表的相同觸發(fā)時間(after/before)的相同觸發(fā)事件(insert/update/delete)，只能定義一個觸發(fā)器，否則報錯

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觸發(fā)器中的if判斷語法格式如下：(1).循環(huán)中還可以有循環(huán)，(2).else后面沒有then, (3).elseif?。。?！不是else if !!

if...then{

if...then{}

end if;

if...then{}

end if;

...

}

elseif...then..

else

end if ;

注意可以使用兩個if循環(huán)，各自end if即可

3)注意mysql觸發(fā)器中的before和after的區(qū)別：

before：(insert、update)可以對new進行修改，

after:不能對new進行修改，兩者都不能修改old數(shù)據(jù)。

對于INSERT語句, 只有NEW是合法的；

對于DELETE語句，只有OLD才合法；

對于UPDATE語句，NEW、OLD可以同時使用。

after是先完成數(shù)據(jù)的增刪改，再觸發(fā)，觸發(fā)的語句晚于監(jiān)視的增刪改操作，無法影響前面的增刪改動作；也就是說先插入訂單記錄，再更新商品的數(shù)量；

before是先完成觸發(fā)，再增刪改，觸發(fā)的語句先于監(jiān)視的增刪改，這樣就可以對new進行修改了；

摘自網(wǎng)絡(luò)的一個例子說明：

首先我們來創(chuàng)建兩張表：

#商品表

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);

我們借助觸發(fā)器來完成下訂單之后，自動對商品表中相應(yīng)商品做減法；如下：

create trigger tg2

after insert on o

for each row

begin

update g set num=num-new.much where id=new.gid;

end$

但是有個問題是，如果下訂單數(shù)超過商品總數(shù)時，那么會導致商品表中產(chǎn)生負數(shù)，這樣我們可以借助before來對訂單中new值進行修改，保證商品表不會出現(xiàn)負數(shù)；

案例：當新增一條訂單記錄時，判斷訂單的商品數(shù)量，如果數(shù)量大于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)不是說一個事務(wù)出發(fā)一次，如下這個事務(wù)修改了10行數(shù)，他會觸發(fā)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的情況，如果觸發(fā)的操作沒有成功，會導致原本的觸發(fā)事件也不成功；

接下來記錄下，我寫的案例，當對一個表做增刪改的時候，觸發(fā)對另一表做相應(yīng)的操作，

例如下面，如果begin后面有語法錯誤或者執(zhí)行錯誤，那么會導致前面的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.關(guān)于insert的觸發(fā)器：

我們的要求是當向blocks_infos的時候，先判斷blocks_infos_opensearch表中有沒有新insert的infos_id，如果有就相應(yīng)的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 ;

注意關(guān)于MySQL replace into 有三種形式（into關(guān)鍵字可以省略）：

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.關(guān)于delete的觸發(fā)器：

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.關(guān)于update的觸發(fā)器：

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做相關(guān)處理的時候，會觸發(fā)另一個表bidinfo.v_publish_info 做相應(yīng)的處理，因為前面已經(jīng)建立了after insert on blocks_infos，不能再建立  after insert

on blocks_infos,所以只能創(chuàng)建  before insert on blocks_infos，如下創(chuàng)建了三個：

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循環(huán)！

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的區(qū)別有哪些”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學習!