阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

245次阅读
没有评论

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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计5979字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中