触发器 (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 的形式的触发器需要保证触发端和被触发端都得成功才能成功!
来源: http://www.linuxidc.com/Linux/2018-04/152079.htm