MySQL触发器trigger的使用

触发器(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 < 0 THEN
               SET NEW.amount = 0;
           ELSEIF 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. 触发器可能导致的问题

  1. 触发器对数据库性能一定的损耗,建议使用前进行压测,判断性能不影响正常业务时再使用。
  2. 触发器可能会导致死锁,在高并发业务下,过多地使用触发器,将会大大增加死锁的概率。触发器实现在MySQL内部,对客户端不可见,导致死锁时,也更不容易被发现。

发表评论