聊一聊MySQL触发器

数据库 MySQL
在学习 MySQL 的过程中,可能你了解过触发器的概念,不清楚各位是否有详细的去学习过触发器,最近看了几篇关于触发器的文档,分享下 MySQL 触发器相关知识。

[[414338]]

本文转载自微信公众号「MySQL技术」,作者MySQL技术。转载本文请联系MySQL技术公众号。

1.触发器简介

触发器即 triggers ,它是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

参考官方文档,触发器创建语法模板如下:

  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 
  8.  
  9. trigger_time: { BEFORE | AFTER } 
  10. trigger_event: { INSERT | UPDATE | DELETE } 
  11. trigger_order: { FOLLOWS | PRECEDES } other_trigger_name 

触发器只能创建在永久表上,不能对临时表或视图创建触发器。触发器的名称在单个数据库内是唯一的。参考上面创建语句,触发器创建有几点要素,下面简要说明下:

trigger_time:是触发动作时间,可以是 BEFORE 或 AFTER ,表示触发器在要修改的每一行之前或之后激活。

  • trigger_event:指示激活触发器的操作类型。这些 trigger_event 值是被允许的:
  • insert:只要向表中插入新行,触发器就会激活。例如 insert 、load data、replace 语句。
  • update:更改表中某一行数据时激活触发器。例如 update 语句。

delete:从表中删除某一行数据时激活触发器。例如 delete 和 replace 语句。表上的 DROP TABLE 和 TRUNCATE TABLE 语句不会激活此触发器,因为它们不使用 delete ,删除分区也不会激活 delete 触发器。

trigger_body:是触发器激活时要执行的语句。如果要执行多个语句,可使用 BEGIN…END 复合语句结构。在触发器主体中,可以使用 old 和 new 来引用触发器中发生变化的记录内容。

2.触发器具体操作

下面来看下触发器相关的具体操作:

  1. # 创建表 创建触发器 
  2. mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); 
  3. Query OK, 0 rows affected (0.03 sec) 
  4. mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); 
  5.  
  6. mysql> delimiter // 
  7. mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account 
  8.        FOR EACH ROW 
  9.        BEGIN 
  10.            IF NEW.amount < 0 THEN 
  11.                SET NEW.amount = 0; 
  12.            ELSEIF NEW.amount > 100 THEN 
  13.                SET NEW.amount = 100; 
  14.            END IF; 
  15.        END;// 
  16. mysql> delimiter ; 
  17.  
  18. # 验证触发器作用 
  19. mysql> select * from account; 
  20. +----------+---------+ 
  21. | acct_num | amount  | 
  22. +----------+---------+ 
  23. |      137 |   14.98 | 
  24. |      141 | 1937.50 | 
  25. |       97 | -100.00 | 
  26. +----------+---------+ 
  27. rows in set (0.00 sec) 
  28.  
  29. mysql> update account set amount = 114.98 where acct_num = 137; 
  30. Query OK, 1 row affected (0.01 sec) 
  31. Rows matched: 1  Changed: 1  Warnings: 0 
  32.  
  33. mysql> select * from account; 
  34. +----------+---------+ 
  35. | acct_num | amount  | 
  36. +----------+---------+ 
  37. |      137 |  100.00 | 
  38. |      141 | 1937.50 | 
  39. |       97 | -100.00 | 
  40. +----------+---------+ 
  41. rows in set (0.00 sec) 
  42.  
  43. # 查看触发器 
  44. mysql> show triggers; 
  45.  
  46. # 删除触发器 
  47. mysql> drop trigger if exists upd_check; 
  48.  
  49. # 查看数据库实例中所有触发器 
  50. SELECT 
  51.  a.TRIGGER_SCHEMA, 
  52.  a.TRIGGER_NAME, 
  53.  a.ACTION_TIMING, 
  54.  a.EVENT_OBJECT_TABLE, 
  55.  a.EVENT_MANIPULATION  
  56. FROM 
  57.  information_schema.`TRIGGERS` a  
  58. WHERE 
  59.  a.TRIGGER_SCHEMA NOT IN ( 'information_schema''performance_schema''mysql''sys' ); 
  60.    
  61. delimiter // 设置MySQL执行结束标志,默认为; 

上面展示了一些关于触发器的基本操作,其实触发器在生产环境中还是比较少见的,即使它能解决我们某些数据库需求,因为触发器的使用存在一系列的缺点,简要总结几点缺点如下:

  • 使用触发器实现的业务逻辑在出现问题时很难进行定位,特别是涉及到多个触发器的情况下,会使后期维护变得困难。
  • 大量使用触发器容易导致代码结构被打乱,增加了程序的复杂性,
  • 如果需要变动的数据量较大时,触发器的执行效率会非常低。
  • 触发器隐式调用容易被忽略,出现问题不好排查。

但是触发器也并不是一无用处,比如我们不想让人删除或更新这个表的数据,可以用触发器实现,下面的一些场景可能对你有所启发:

  1. # 禁止删除数据 即使你有权限 
  2. mysql> select * from student; 
  3. +--------------+------+--------+-------+-------+ 
  4. | increment_id | s_id | s_name | s_sex | s_age | 
  5. +--------------+------+--------+-------+-------+ 
  6. |            1 | 1001 | sdfsd  | 男    |    18 | 
  7. |            2 | 1003 | zsdfsd | 女    |    19 | 
  8. +--------------+------+--------+-------+-------+ 
  9. rows in set (0.00 sec) 
  10.  
  11. mysql> delimiter // 
  12. mysql> CREATE TRIGGER `tri_delstu` BEFORE DELETE ON `student` FOR EACH ROW begin 
  13.     -> declare msg varchar(255); 
  14.     -> set msg="不允许删除学生信息"
  15.     -> SIGNAL SQLSTATE 'HY000' SET  MESSAGE_TEXT = msg; 
  16.     -> end; // 
  17. Query OK, 0 rows affected (0.02 sec) 
  18.  
  19. mysql> delimiter ; 
  20. mysql> delete from student where s_id = 1003; 
  21. ERROR 1644 (HY000): 不允许删除学生信息 
  22.  
  23. # 禁止更新某个字段 
  24. mysql> delimiter // 
  25. mysql> CREATE TRIGGER trg__updateSid BEFORE UPDATE ON `student` 
  26.     -> FOR EACH ROW 
  27.     -> BEGIN 
  28.     ->  DECLARE msg VARCHAR(100);  
  29.     ->  IF NEW.s_id <> OLD.s_id THEN 
  30.     ->  SET msg='不允许修改学号';  
  31.     ->  SIGNAL SQLSTATE 'HY000' SET message_text = msg;  
  32.     ->  END IF;  
  33.     -> END; // 
  34. Query OK, 0 rows affected (0.06 sec) 
  35.  
  36. mysql> delimiter ; 
  37. mysql> update student set s_id = 1002 where increment_id = 2; 
  38. ERROR 1644 (HY000): 不允许修改学号 
  39.  
  40. # 限制修改范围 
  41. mysql> delimiter // 
  42. mysql> CREATE TRIGGER `tri_update_age` BEFORE UPDATE ON `student` FOR EACH ROW BEGIN 
  43.     ->         DECLARE msg VARCHAR(20); 
  44.     ->   IF (NEW.s_age<0) THEN  
  45.     ->         set msg="年龄不能小于0"
  46.     ->   signal sqlstate 'HY000' set message_text=msg; 
  47.     ->         END IF; 
  48.     -> END; // 
  49. Query OK, 0 rows affected (0.02 sec) 
  50.  
  51. mysql> delimiter ; 
  52. mysql> update student set s_age=10 where s_id = 1001; 
  53. Query OK, 1 row affected (0.01 sec) 
  54. Rows matched: 1  Changed: 1  Warnings: 0 
  55.  
  56. mysql> update student set s_age=-10 where s_id = 1001; 
  57. ERROR 1644 (HY000): 年龄不能小于0 

总结: 

本篇文章简要介绍了触发器的定义及使用案例。在业务逻辑复杂或表变动比较频繁的系统还是不推荐使用触发器,当然它也是有自己的应用场景,无论怎样,触发器的逻辑总是越简单越好,我们应该让数据库做它擅长做的事,不能想着所有逻辑都在数据库层面实现。

 

责任编辑:武晓燕 来源: MySQL技术
相关推荐

2020-10-15 06:56:51

MySQL排序

2021-04-23 10:31:18

MySQLRole数据库

2022-12-26 08:13:54

子查询MySQL

2021-03-01 18:37:15

MySQL存储数据

2023-09-22 17:36:37

2021-01-28 22:31:33

分组密码算法

2020-05-22 08:16:07

PONGPONXG-PON

2018-06-07 13:17:12

契约测试单元测试API测试

2022-08-08 08:25:21

Javajar 文件

2021-01-29 08:32:21

数据结构数组

2023-05-15 08:38:58

模板方法模式

2021-08-04 09:32:05

Typescript 技巧Partial

2019-02-13 14:15:59

Linux版本Fedora

2021-02-06 08:34:49

函数memoize文档

2018-11-29 09:13:47

CPU中断控制器

2022-11-01 08:46:20

责任链模式对象

2023-07-06 13:56:14

微软Skype

2020-03-06 15:11:21

进程线程Web

2022-02-21 15:01:45

MySQL共享锁独占锁

2020-08-12 08:34:16

开发安全We
点赞
收藏

51CTO技术栈公众号