关于mysql:MySQL触发器介绍

前言：

在学习 MySQL 的过程中，可能你理解过触发器的概念，不分明各位是否有具体的去学习过触发器，最近看了几篇对于触发器的文档，分享下 MySQL 触发器相干常识。

1. 触发器简介

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

参考官网文档，触发器创立语法模板如下：

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_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. 触发器具体操作

上面来看下触发器相干的具体操作：

# 创立表 创立触发器
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

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 ;

# 验证触发器作用
mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |   14.98 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)

mysql> update account set amount = 114.98 where acct_num = 137;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)

# 查看触发器
mysql> show triggers;

# 删除触发器
mysql> drop trigger if exists upd_check;

# 查看数据库实例中所有触发器
SELECT
    a.TRIGGER_SCHEMA,
    a.TRIGGER_NAME,
    a.ACTION_TIMING,
    a.EVENT_OBJECT_TABLE,
    a.EVENT_MANIPULATION 
FROM
    information_schema.`TRIGGERS` a 
WHERE
    a.TRIGGER_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');
  
delimiter // 设置 MySQL 执行完结标记，默认为;

下面展现了一些对于触发器的基本操作，其实触发器在生产环境中还是比拟少见的，即便它能解决咱们某些数据库需要，因为触发器的应用存在一系列的毛病，简要总结几点毛病如下：

• 应用触发器实现的业务逻辑在呈现问题时很难进行定位，特地是波及到多个触发器的状况下，会使前期保护变得艰难。
• 大量应用触发器容易导致代码构造被打乱，减少了程序的复杂性，
• 如果须要变动的数据量较大时，触发器的执行效率会非常低。
• 触发器隐式调用容易被疏忽，呈现问题不好排查。

然而触发器也并不是一无用处，比方咱们不想让人删除或更新这个表的数据，能够用触发器实现，上面的一些场景可能对你有所启发：

# 禁止删除数据 即便你有权限
mysql> select * from student;
+--------------+------+--------+-------+-------+
| increment_id | s_id | s_name | s_sex | s_age |
+--------------+------+--------+-------+-------+
|            1 | 1001 | sdfsd  | 男    |    18 |
|            2 | 1003 | zsdfsd | 女    |    19 |
+--------------+------+--------+-------+-------+
2 rows in set (0.00 sec)

mysql> delimiter //
mysql> CREATE TRIGGER `tri_delstu` BEFORE DELETE ON `student` FOR EACH ROW begin
    -> declare msg varchar(255);
    -> set msg="不容许删除学生信息";
    -> SIGNAL SQLSTATE 'HY000' SET  MESSAGE_TEXT = msg;
    -> end; //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> delete from student where s_id = 1003;
ERROR 1644 (HY000): 不容许删除学生信息

# 禁止更新某个字段
mysql> delimiter //
mysql> CREATE TRIGGER trg__updateSid BEFORE UPDATE ON `student`
    -> FOR EACH ROW
    -> BEGIN
    ->  DECLARE msg VARCHAR(100); 
    ->  IF NEW.s_id <> OLD.s_id THEN
    ->  SET msg='不容许批改学号'; 
    ->  SIGNAL SQLSTATE 'HY000' SET message_text = msg; 
    ->  END IF; 
    -> END; //
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> update student set s_id = 1002 where increment_id = 2;
ERROR 1644 (HY000): 不容许批改学号

# 限度批改范畴
mysql> delimiter //
mysql> CREATE TRIGGER `tri_update_age` BEFORE UPDATE ON `student` FOR EACH ROW BEGIN
    ->         DECLARE msg VARCHAR(20);
    ->   IF (NEW.s_age<0) THEN 
    ->         set msg="年龄不能小于 0";
    ->   signal sqlstate 'HY000' set message_text=msg;
    ->         END IF;
    -> END; //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> update student set s_age=10 where s_id = 1001;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update student set s_age=-10 where s_id = 1001;
ERROR 1644 (HY000): 年龄不能小于 0 

总结：

本篇文章简要介绍了触发器的定义及应用案例。在业务逻辑简单或表变动比拟频繁的零碎还是不举荐应用触发器，当然它也是有本人的利用场景，无论怎样，触发器的逻辑总是越简略越好，咱们应该让数据库做它善于做的事，不能想着所有逻辑都在数据库层面实现。