MySQL触发器trigger的使用

MySQL, 数据库 / 作者：

触发器(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内部，对客户端不可见，导致死锁时，也更不容易被发现。

发表评论