前言
触发器是与表有关的数据库对象,在满足定义的条件的时候(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会回滚。
与事务的区别
事务是一种容器,是一种特殊的保护机制,是最小的单位,要么全部成功,要么全部失败,也可以自由的控制提交和回滚。
触发器的定位则是一种功能特性,类似于函数和存储是一种功能,只有触发了特定条件才会执行,只有出错失败才会回滚。