mysql触发器优缺点,浅谈MySQL触发器

前言

触发器是与表有关的数据库对象，在满足定义的条件的时候(INSERT、UPDATE、DELETE事件动作)触发，并(After，Before)执行触发器中定义的语句集合。

这种特性可以协助应用在数据库端保证数据的完整性。

创建

1.语法

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt

tb_name ：需要建立触发器的表名(只能是永久表，不能对临时表创建触发器)

trigger_name ：触发器名称，自行指定

trigger_time：触发时机，取值BEFORE、AFTER

trigger_event ：触发事件，INSERT、UPDATE、DELETE

trigger_stmt ： 触发程序体，可以是一条SQL语句或是BEGIN和END包含的多条语句

2.类型

触发类型受触发时间和触发动作的影响，因此类型只有6种组合形式，并且同一个表的类型不可重复。

BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE

AFTER INSERT、AFTER UODATE、AFTER DELETE

3.BEGIN ... END

...

BEGIN

[statement_list]

END

statement_list代表一个或多个语句的列表，列表内的每条SQL语句都必须用分号(;)来结尾。我们可以使用DELIMITERE指定自定义的定界符，用来区分触发器和内部SQL的结尾。

DELIMITER new_delemiter

new_delemiter 可以设为1个或多个长度的符号，我们可以把它修改为其他符号，如$：DELIMITER $，在结尾我们需要使用DELIMITER ;将分隔符再次修改成分号。

DELIMITER $

... --触发器创建语句；

$ --提交创建语句；

DELIMITER ;

4.NEW和OLD

在trigger_body中，我们可以使用NEW表示将要插入的新行(INSERT的字段)，OLD表示将要删除的旧行(DELETE的字段)。通过OLD，NEW中获取其的内容，方便在触发操作中使用，下面是对应事件是否支持OLD，NEW的对应关系：

DML

OLD

NEW

INSERT

×

√

DELETE

√

×

UPDATE

√

√

UPDATE相当于删除旧行(OLD)，然后插入新行(NEW)，所以UPDATE同时支持OLD，NEW；

示例：

1.在插入数据之前计算出开始和结束时间差，并保存

DELIMITER$

drop trigger if exists tmp1_insert$

create trigger tmp1_insert

before insert on zcw

for each row

begin

set new.diff_day = DATEDIFF(new.end,new.start);

end$

DELIMITER ;

-- 插入语句

INSERT INTO zcw ( start, end) VALUES (NOW(), '2020-08-01');

start end diff_day

2020-07-31 2020-08-01 1

2.在更新A表的同时更新B表的字段

DELIMITER $

drop trigger if exists zcw_update$

create trigger zcw_update

after update on zcw

for each row

begin

update a set rules = new.rules where id=new.id;

end$

DELIMITER ;

-- 更新字段

UPDATE zcw set rules = 'boss' WHERE id = 1;

管理触发器

查看触发器SHOW TRIGGERS [FROM schema_name]，schema_name表示的是表名称。

删除触发器DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

注意点

如果BEFORE触发器执行失败，SQL无法正确执行。

SQL执行失败时，AFTER型触发器不会触发。

AFTER类型的触发器执行失败，SQL会回滚。

与事务的区别

事务是一种容器，是一种特殊的保护机制，是最小的单位，要么全部成功，要么全部失败，也可以自由的控制提交和回滚。

触发器的定位则是一种功能特性，类似于函数和存储是一种功能，只有触发了特定条件才会执行，只有出错失败才会回滚。