定义

触发器的对象是表，当表上出现特定的事件（增、删、改）时触发该程序的执行。

触发器的类型

  • UPDATE
    • update 操作

  • DELETE

    • delete 操作
    • replace 操作
      :::warning 注意：drop，truncate 等 DDL 操作不会触发 DELETE。 :::

  • INSERT

    • insert 操作
    • load data 操作
    • replace 操作 :::warning 注意：replace 操作可能会触发两次，一次是 DELETE 类型的触发器，一次是 INSERT 类型的触发器。
      :::

:::warning

  • MySQL 5.6 版本针对一张表，同一个类型的触发器只能有一个。
  • MySQL 5.7 版本针对一张表，允许多个同一类型的触发器。
  • 触发器只触发 DML 操作，不会触发 DDL 操作（create、drop 等操作）。 :::

创建触发器

语法

  1. CREATE
  2.     [DEFINER = user]
  3.     TRIGGER trigger_name -- 触发器名字
  4.     trigger_time trigger_event -- 触发时间和事件
  5.     ON tbl_name FOR EACH ROW
  6.     [trigger_order]
  7.     trigger_body
  9. trigger_time: { BEFORE | AFTER } -- 事件之前还是之后触发
  11. trigger_event: { INSERT | UPDATE | DELETE } -- 三个类型事件
  13. trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
  • trigger_name：触发器名称
  • tirgger_time：触发执行时间
    • BEFORE：事件之前触发
    • AFTER：事件之后触发
  • trigger_event：触发事件
    • INSERT：插入某一行时激活触发器，INSERT，LOAD DATA，REPLACE 语句可以触发
    • UPDATE：更改某一行时激活触发器，UPDATE 语句可以触发
    • DELETE：删除某一行时激活触发器，DELETE，REPLACE 语句可以触发
  • tbl_name：触发器要执行的哪张表
  • FOR EACH ROW：触发频率为每一行触发一次
  • trigger_body：触发器的程序体，可以是一条SQL语句或者是用 BEGIN 和 END 包含的多条语句

创建只有一个执行语句的触发器

  1. CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

创建有多个执行语句的触发器

  1. CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
  2. ON 表名 FOR EACH ROW
  3. BEGIN
  4.     执行语句列表
  5. END;

MySQL 默认是以分号（;）作为结束执行符号，与触发器执行语句列表中需要的分行起冲突，为解决此问题可用DELIMITER，如DELIMITER ;; ，就是将结束符号变成 ;;。

  1. DELIMITER ;;
  2. CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
  3. ON 表名 FOR EACH ROW
  4. BEGIN
  5.     执行语句列表
  6. END ;;
  7. DELIMITER;

删除触发器

  1. DROP TRIGGER IF EXISTS tb_name;

示例

数据准备：

  1. -- 创建user表
  2. DROP TABLE IF EXISTS `user`;
  3. CREATE TABLE `user` (
  4.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  5.   `account` varchar(255) DEFAULT NULL,
  6.   `name` varchar(255) DEFAULT NULL,
  7.   `address` varchar(255) DEFAULT NULL,
  8.   PRIMARY KEY (`id`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  11. -- 创建对user表操作历史表
  12. DROP TABLE IF EXISTS `user_history`;
  13. CREATE TABLE `user_history` (
  14.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  15.   `user_id` bigint(20) NOT NULL,
  16.   `operatetype` varchar(200) NOT NULL,
  17.   `operatetime` datetime NOT NULL,
  18.   PRIMARY KEY (`id`)
  19. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建 user 表插入事件对应的触发器

  1. DROP TRIGGER IF EXISTS `tri_insert_user`;
  2. DELIMITER ;;
  3. CREATE TRIGGER `tri_insert_user` AFTER INSERT ON `user` FOR EACH ROW begin
  4.     INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user',  now());
  5. end
  6. ;;
  7. DELIMITER ;
  • old.col：更新以前的列值（只读）。
  • new.col：更新以后的列值。

创建 user 表更新事件对应的触发器

  1. DROP TRIGGER IF EXISTS `tri_update_user`;
  2. DELIMITER ;;
  3. CREATE TRIGGER `tri_update_user` AFTER UPDATE ON `user` FOR EACH ROW begin
  4.     INSERT INTO user_history(user_id,operatetype, operatetime) VALUES (new.id, 'update a user', now());
  5. end
  6. ;;
  7. DELIMITER ;

创建 user 表删除事件对应的触发器

  1. DROP TRIGGER IF EXISTS `tri_delete_user`;
  2. DELIMITER ;;
  3. CREATE TRIGGER `tri_delete_user` AFTER DELETE ON `user` FOR EACH ROW begin
  4.     INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', now());
  5. end
  6. ;;
  7. DELIMITER ;

至此，全部表及触发器创建完成，开始验证结果，分别做插入、修改、删除事件，执行以下语句，观察 user_history是否自动产生操作记录。

  1. INSERT INTO user(account, name, address) VALUES ('user1', 'user1', 'user1');
  2. INSERT INTO user(account, name, address) VALUES ('user2', 'user2', 'user2');
  3. UPDATE user SET name = 'user3', account = 'user3', address='user3' where name='user1';
  4. DELETE FROM `user` where name = 'user2';

image.png

触发器总结

  • 触发器对性能有损耗，应当非常慎重使用。
  • 对于事务表，触发器执行失败则整个语句回滚。
  • Row 格式主从复制，触发器不会在从库上执行。
    • 因为从库复制的肯定是主库已经提交的数据，既然已经提交了说明触发器已经被触发过了，所以从库不会执行。
  • 使用触发器时应防止递归执行。
    1. delimiter //
    2. create trigger trg_test
    3.   before update on 'test_trigger'
    4.   for each row
    5. begin
    6.   update test_trigger set score=20 where name = old.name;  -- 又触发了update操作，循环触发了
    7. end;//

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/iq4wqs 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。