mysql触发器

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

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

如果插入的记录的m1列的值小于1，则按1插入。
如果m1列的值大于10，则按10插入。
在向t1表中插入记录之后自动把这条记录插入到t2表。

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

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

CREATE TRIGGER 触发器名
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END
小贴士：
 
由大括号`{}`包裹并且内部用竖线`|`分隔的语句表示必须在给定的选项中选取一个值，比如`{BEFORE|AFTER}`表示必须在`BEFORE`、`AFTER`这两个之间选取一个。
其中{BEFORE|AFTER}表示触发器内容执行的时机，它们的含义如下：

名称    描述
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代表修改前的记录。
现在我们可以正式定义一个触发器了：

mysql> delimiter $
mysql> CREATE TRIGGER bi_t1
    -> BEFORE INSERT ON t1
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.m1 < 1 THEN
    ->         SET NEW.m1 = 1;
    ->     ELSEIF NEW.m1 > 10 THEN
    ->         SET NEW.m1 = 10;
    ->     END IF;
    -> END $
Query OK, 0 rows affected (0.02 sec)
 
mysql> delimiter ;
mysql>

我们对t1表定义了一个名叫bi_t1的触发器，它的意思就是在对t1表插入新记录之前，对准备插入的每一条记录都会执行BEGIN ... END之间的语句，NEW.列名表示当前待插入记录指定列的值。现在t1表中一共有4条记录：

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)
 
mysql>
我们现在执行一下插入语句并再次查看一下t1表的内容：

mysql> INSERT INTO t1(m1, n1) VALUES(5, 'e'), (100, 'z');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|   10 | z    |
+------+------+
6 rows in set (0.00 sec)
 
mysql>

这个INSERT语句影响的记录有两条，分别是(5, 'e')和(100, 'z')，这两条记录将分别执行我们自定义的触发器内容。很显然(5, 'e')被成功的插入到了t1表中，而(100, 'z')插入到表中后却变成了(10, 'z')，这个就说明我们的bi_t1触发器生效了！

小贴士： 我们上边定义的触发器名`bi_t1`的`bi`是`before insert`的首字母缩写，`t1`是表名。虽然对于触发器的命名并没有什么特殊的要求，但是习惯上还是建议大家把它定义我上边例子中的形式，也就是`bi_表名`、`bd_表名`、`bu_表名`、`ai_表名`、`ad_表名`、`au_表名`的形式。

上边只是举了一个对INSERT语句设置BEFORE触发器的例子，对DELETE和UPDATE操作设置BEFORE或者AFTER触发器的过程是类似的，就不赘述了。

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

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

SHOW CREATE TRIGGER 触发器名;
删除触发器：

DROP TRIGGER 触发器名;
这几个命令太简单了，就不举例子了啊～

触发器使用注意事项
触发器内容中不能有输出结果集的语句。

比方说：

mysql> delimiter $
mysql> CREATE TRIGGER ai_t1
    -> AFTER INSERT ON t1
    -> FOR EACH ROW
    -> BEGIN
    ->     SELECT NEW.m1, NEW.n1;
    -> END $
ERROR 1415 (0A000): Not allowed to return a result set from a trigger
mysql>
显示的ERROR的意思就是不允许在触发器内容中返回结果集！

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

NEW代表新插入或着即将修改后的记录，修改它的列的值将影响INSERT和UPDATE语句执行后的结果，而OLD代表修改或删除之前的值，我们无法修改它。比方说如果我们非要这么写那就会报错的：

mysql> delimiter $
mysql> CREATE TRIGGER bu_t1
    -> BEFORE UPDATE ON t1
    -> FOR EACH ROW
    -> BEGIN
    ->     SET OLD.m1 = 1;
    -> END $
ERROR 1362 (HY000): Updating of OLD row is not allowed in trigger
mysql>
可以看到提示的错误中显示在触发器中OLD代表的记录是不可被更改的。

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

比方说如果我们非要这么写那就会报错的：

mysql> delimiter $
mysql>     CREATE TRIGGER ai_t1
    ->     AFTER INSERT ON t1
    ->     FOR EACH ROW
    ->     BEGIN
    ->         SET NEW.m1 = 1;
    ->     END $
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
mysql>
可以看到提示的错误中显示在AFTER触发器中是不允许更改NEW代表的记录的。

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