MySQL触发器案例分析及before与after的区别

157次阅读

共计 5979 个字符，预计需要花费 15 分钟才能阅读完成。

触发器（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 的形式的触发器需要保证触发端和被触发端都得成功才能成功！

正文完

星哥说事-微信公众号