• 博客访问: 1446747
  • 博文数量: 203
  • 用 户 组: 普通用户
  • 注册时间: 2014-05-12 18:11
  • 认证徽章:
个人简介

曾就职于国美控股集团有限公司 目前就职于某大型采招网 11g ocp 11g ocm 专注Oracle和mysql数据库

文章分类

全部博文(203)

文章存档

2018年(22)

2017年(52)

2016年(24)

2015年(56)

2014年(49)

分类: MySQL

2018-04-24 22:01:52

触发器(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的形式的触发器需要保证触发端和被触发端都得成功才能成功!
阅读(3040) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册