触发器(trigger)是数据库的一类对象,与表关联,当表中指定的事件发生时,比如插入数据,更新数据,删除数据,触发器就会被激活,来执行一些SQL语句。
- 触发器不能建立在临时表或者视图上。
- 触发器的名称在schema的命名空间里,也就是说一个数据库里面的触发器名称必须唯一。
- 创建触发器的用户需要有TRIGGER权限。
1. 触发器创建语法
创建触发器使用create trigger语法,如下:
CREATE
[DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
- trigger_name,触发器名称。
- trigger_time,触发时机,可选值为BEFORE和AFTER,表示行数据被修改之前还是之后触发。
- trigger_event,激活触发器的操作类型,可选值为INSERT,UPDATE和DELETE。drop table,truncate table 以及删除分区表并不会触发DELETE类型的触发器。
- trigger_order,触发器执行顺序,如果一个表创建多个相同操作类型的触发器,那么这些触发器会按照被创建的顺序依次触发。可以在创建触发器时,指定FOLLOWS或者PRECEDES来明确多个触发器的执行顺序。
- trigger_body,触发器执行体,当触发器被激活时,要执行的语句。执行体中,用NEW标识新的行数据,OLD标识旧的行数据。
2. 查看触发器
SHOW TRIGGERS\G
SHOW TRIGGERS IN db_name\G
SHOW TRIGGERS IN db_name like '%xxx%'\G
3. 删除触发器
DROP TRIGGER trigger_name;
DROP TRIGGER db_name.trigger_name;
4. 触发器使用案例
案例一
有一张表account,往这个表插入数据时,希望能够计算所有插入记录的amount数量之和。采用触发器实现如下:
# 创建表
CREATE TABLE account (id INT, amount DECIMAL(10,2), primary key(id));
# 创建触发器
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
# 计算插入数据之和
SET @sum = 0;
INSERT INTO account VALUES(1,14.98),(2,1937.50),(3,-100.00);
SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
案例二
使用触发器做数据校验,更新account表中的amount字段,amount字段取值只能在0~100之间,其余的值都是无效值,小于0的值自动纠正为0,大于100的值自动纠正为100,采用触发器实现如下:
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount 100 THEN
SET NEW.amount = 100;
END IF;
END;//
mysql> delimiter ;
案例三
使用触发器实现影子表,对account表的所有操作,都会同步到影子表account_shadow中。
# 创建影子表
mysql> create table account_shadow like account;
mysql> delimiter //
# 创建insert触发器
mysql> CREATE TRIGGER trg_insert after INSERT ON account
FOR EACH ROW
BEGIN
INSERT INTO account_shadow SET id=NEW.id, amount=NEW.amount;
END;
# 创建update触发器
CREATE TRIGGER trg_update after UPDATE ON account
FOR EACH ROW
BEGIN
update account_shadow SET id=NEW.id, amount=NEW.amount where id=OLD.id;
END;
# 创建delete触发器
CREATE TRIGGER trg_delete after DELETE ON account
FOR EACH ROW
BEGIN
delete from account_shadow where id=OLD.id;
END;//
mysql> delimiter ;
5. 触发器可能导致的问题
- 触发器对数据库性能一定的损耗,建议使用前进行压测,判断性能不影响正常业务时再使用。
- 触发器可能会导致死锁,在高并发业务下,过多地使用触发器,将会大大增加死锁的概率。触发器实现在MySQL内部,对客户端不可见,导致死锁时,也更不容易被发现。