MySQL之TRIGGER触发器

我们之前介绍了视图、函数、存储过程、游标，其中函数和存储过程是需要我们手动调用的，而触发器是MySQL服务器在特定情况下自动调用的，今天就来看一下触发器。

本文摘录自：

• Ben Forta《MySQL必知必会》

• 小孩子4919《MySQL是怎样使用的：从零蛋开始学习MySQL》

CREATE TABLE t1 (  m1 INT NOT NULL,  n1 CHAR(1) DEFAULT NULL) ENGINE = InnoDB;INSERT INTO t1(m1, n1) VALUES(1, 'a');INSERT INTO t1(m1, n1) VALUES(2, 'b');INSERT INTO t1(m1, n1) VALUES(3, 'c');INSERT INTO t1(m1, n1) VALUES(4, 'd');

我们使用MySQL的过程中可能会有下边这些需求：

• 在向t1表插入或更新数据之前对自动对数据进行校验，要求m1列的值必须在1~10之间，校验规则如下：

• 如果插入的记录的m1列的值小于1，则按1插入。

• 如果m1列的值大于10，则按10插入。

• 在向t1表中插入记录之后自动把这条记录插入到t2表。

也就是我们在对表中的记录做增、删、改操作前和后都可能需要让MySQL服务器自动执行一些额外的语句，这个就是所谓的触发器的应用场景。

• DELETE；

• INSERT；

• UPDATE。

其他MySQL语句不支持触发器。

我们看一下定义触发器的语句：

CREATE TRIGGER 触发器名{BEFORE|AFTER}{INSERT|DELETE|UPDATE}ON 表名FOR EACH ROWBEGIN    触发器内容END

①唯一的触发器名；

②触发器关联的表；

③触发器应该响应的活动（DELETE、INSERT或UPDATE）；

④触发器何时执行（处理之前或之后）。

• 其中{BEFORE|AFTER}表示触发器内容执行的时机，它们的含义如下：

• {INSERT|DELETE|UPDATE}表示具体的语句，MySQL中目前只支持对INSERT、DELETE、UPDATE这三种类型的语句设置触发器。

• FOR EACH ROW BEGIN ... END表示对具体语句影响的每一条记录都执行我们自定义的触发器内容：

• 对于INSERT
  语句来说，FOR EACH ROW
  影响的记录就是我们准备插入的那些新记录。

• 对于DELETE
  语句和UPDATE
  语句来说，FOR EACH ROW
  影响的记录就是符合WHERE
  条件的那些记录（如果语句中没有WHERE
  条件，那就是代表全部的记录）。

如果触发器内容只包含一条语句，那也可以省略BEGN、END这两个词儿。

• 因为MySQL服务器会对某条语句影响的所有记录依次调用我们自定义的触发器内容，所以针对每一条受影响的记录，我们需要一种访问该记录中的内容的方式，MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同语句中的含义不同：

• 对于INSERT
  语句设置的触发器来说，NEW
  代表准备插入的记录，OLD
  无效。

• 对于DELETE
  语句设置的触发器来说，OLD
  代表删除前的记录，NEW
  无效。

• 对于UPDATE
  语句设置的触发器来说，NEW
  代表修改后的记录，OLD
  代表修改前的记录。

DELIMITER $$CREATE TRIGGER bi_t1BEFORE INSERT ON t1FOR EACH ROWBEGIN    IF NEW.m1 < 1 THEN        SET NEW.m1 = 1;    ELSEIF NEW.m1 > 10 THEN        SET NEW.m1 = 10;    END IF;END $$DELIMITER ;

INSERT INTO t1(m1, n1) VALUES(5, 'e'), (100, 'z');SELECT * FROM t1;

查看当前数据库中定义的所有触发器的语句：

SHOW TRIGGERS;

查看某个具体的触发器的定义：

SHOW CREATE TRIGGER 触发器名;

删除触发器：

DROP TRIGGER 触发器名;

• 只有表才支持触发器，视图不支持（临时表也不支持）。

• 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

• 如果我们的BEFORE触发器内容执行过程中遇到了错误，那这个触发器对应的具体语句将无法执行；如果具体的操作语句执行过程中遇到了错误，那与它对应的AFTER触发器的内容将无法执行。

• 触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

• MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

• 触发器内容中不能有输出结果集的语句。（显示的ERROR的意思就是不允许在触发器内容中返回结果集）

• 触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改。

• 在BEFORE触发器中，我们可以使用SET NEW.列名 = 某个值的形式来更改待插入记录或者待更新记录的某个列的值，但是这种操作不能在AFTER触发器中使用，因为在执行AFTER触发器的内容时记录已经被插入完成或者更新完成了。（可以看到提示的错误中显示在AFTER触发器中是不允许更改NEW代表的记录的）

• 对于支持事务的表，不论是执行触发器内容还是具体操作语句过程中出现了错误，会把这个过程中所有的语句都回滚。

今天的理论知识挺多的，下面我们来做下总结：

1、触发器是MySQL响应INSERT、UPDATE、DELETE语句（仅此只支持这三种）而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）；

2、MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同语句中的含义不同：

• INSERT
  语句，NEW
  代表准备插入的记录，OLD
  无效。

• DELETE语句，OLD
  代表删除前的记录，NEW
  无效。

• UPDATE语句，NEW
  代表修改后的记录，OLD
  代表修改前的记录。

3、使用触发器的注意事项：

• 只有表才支持触发器，视图不支持（临时表也不支持）。

• 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

• 如果我们的BEFORE触发器内容执行过程中遇到了错误，那这个触发器对应的具体语句将无法执行；如果具体的操作语句执行过程中遇到了错误，那与它对应的AFTER触发器的内容将无法执行。

• 触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

• MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

• 触发器内容中不能有输出结果集的语句。

• 触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改。

• 在BEFORE触发器中，我们可以使用SET NEW.列名 = 某个值的形式来更改待插入记录或者待更新记录的某个列的值，但是这种操作不能在AFTER触发器中使用，因为在执行AFTER触发器的内容时记录已经被插入完成或者更新完成了。

• 对于支持事务的表，不论是执行触发器内容还是具体操作语句过程中出现了错误，会把这个过程中所有的语句都回滚。

今天主要学习了MySQL的TRIGGER触发器，介绍了什么是触发器以及如何使用触发器，并且讲解了使用触发器的一些注意事项。今天的理论知识较多，但我们真实生产环境中对触发器的使用不是很多，所以把触发器当做一个必知的技能，以便不时之需，与大家共勉。

end