（16） 触发器
最后更新于：2022-04-01 23:40:15
**目录**
[TOC]
# 1 Triggers的应用
在“cmdev”资料中有一个“emplog”表格，如果有人执行任何修改“cmdev.emp”表格资料的动作，都要新增一笔讯息到“cmdev.emplog”表格中，查询这个表格的资料，就可以知道在什么时候曾经修改过“cmdev.emp”表格中的资料：
| 字段名称 | 型态 | NULL | 索引 | 默认值 | 其它资讯 | 说明 |
| --- | --- | --- | --- | --- | --- | --- |
| logno | bigint(20) | NO | PRI | NULL | auto_increment | 纪录编号 |
| logdt | timestamp | NO | | CURRENT_TIMESTAMP | | 日期时间 |
| message | varchar(64) | YES | | NULL | | 讯息 |
要完成这样的需求，每一次修改“cmdev.emp”表格资料时，你都必需执行下列的工作：
[](http://box.kancloud.cn/2015-07-18_55a9d77c279e6.png)
要完成这样的需求，你可以使用stored routines来处理修改与新增纪录的工作，或是在应用程式中撰写程式来解决。不过都会是一件很麻烦的事情，而且比较容易造成遗漏纪录的情况。
MySQL数据库提供一种特别的数据库元件，称为“triggers”，一般会把它称为“触发器”。Triggers可以让你先把一些在特定状况要执行的叙述储存起来，MySQL数据库会在正确的时机自动帮你执行这些叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d7852e89f.png)
以上列讨论的需求来说，每一次修改“cmdev.emp”表格资料，都必须新增一笔纪录到“cmdev.emplog”表格中。这个需求的主角是“cmdev.emp”表格，所以你可以为这个表格建立一个trigger元件；因为是在修改资料的情况时才需要执行特定的工作，所以你要选择“UPDATE trigger”；新增一笔纪录到“cmdev.emplog”表格中的叙述就是储存在“cmdev.emp”表格的“UPDATE trigger”中。
如果你建立好需要的trigger元件后，MySQL数据库就会自动帮你执行这些工作：
[](http://box.kancloud.cn/2015-07-18_55a9d78a1305d.png)
# 2 建立Triggers
下列是建立trigger元件的语法：
[](http://box.kancloud.cn/2015-07-18_55a9d78b37d7c.png)
如果trigger元件执行的工作比较复杂，需要一个以上的叙述时，就要把叙述放在“BEGIN”与“END”区块中：
[](http://box.kancloud.cn/2015-07-18_55a9d78f97a28.png)
你可以依照需求为一个表格建立不同的trigger元件：
[](http://box.kancloud.cn/2015-07-18_55a9d7903c68e.png)
以上列讨论的需求来说，每一次修改“cmdev.emp”表格资料，都必须新增一笔纪录到“cmdev.emplog”表格中。你要为“cmdev.emp”表格建立一个“UPDATE TRIGGER”；而“BEFORE”与“AFTER”就是“之前”与“之后”的意思。如果建立“BEFORE UPDATE TRIGGER”，那就表示在修改资料前会执行trigger；如果建立“AFTER UPDATE TRIGGER”，那就表示在修改资料后会执行trigger。以这个需求来说，“BEFORE”或“AFTER”都是一样的。
建立trigger元件与建立stored routines的方式一样，你也要使用“SQL script”来执行建立trigger的工作。下列的范例建立一个名称为“emp_before_update”的trigger元件：
[](http://box.kancloud.cn/2015-07-18_55a9d79334bf0.png)
执行上列的叙述后，MySQL数据库会储存你建立的trigger元件，可是它并不像stored routines可以用来呼叫与执行；MySQL数据库会自动帮你执行这些储存在trigger中的叙述。
为“cmdev.emp”表格建立一个“BEFORE UPDATE TRIGGER”以后，只要发生修改“cmdev.emp”表格资料的情况，MySQL数据库会自动执行这个trigger中的叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d79fe010d.png)
不论是“UPDATE”或是其它两种Trigger元件，MySQL数据库都是以“纪录”来执行trigger。以下列的范例来说，一个会修改三笔纪录的“UPDATE”叙述，MySQL数据库会执行trigger三次：
[](http://box.kancloud.cn/2015-07-18_55a9d7b6966cf.png)
如果在执行修改“cmdev.emp”表格叙述以后，实际上并没有修改任何纪录资料，那MySQL数据库也不会执行trigger：
[](http://box.kancloud.cn/2015-07-18_55a9d7c3eed40.png)
在你建立trigger元件时，要特别注意下列的限制：
* 同一个数据库不可以有相同名称的Trigger
* TEMPORARY表格与View不可以建立Trigger
* 不可以使用“SELECT”叙述
* 不可以使用“CALL”叙述
* 不可以使用与交易(transactions)相关的叙述，包含“START TRANSACTION”、“COMMIT”与“ROLLBACK”
# 3 删除Triggers
你可以使用下列的语法删除不再需要的trigger元件：
[](http://box.kancloud.cn/2015-07-18_55a9d7eb4238d.png)
如果想要修改trigger元件中的叙述，你要先删除以后，再建立新的trigger元件。所以你可以在在建立trigger元件的叙述中，加入删除trigger元件的叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d7ec993f7.png)
# 4 OLD与NEW关键字
在triggers元件中，可以使用一般的SQL叙述完成需要执行的工作，也可以使用在stored routines中讨论过的变量与流程控制，让triggers元件可以处理比较复杂的需求。MySQL数据库在triggers元件中额外提供“OLD”与“NEW”两个关键字：
[](http://box.kancloud.cn/2015-07-18_55a9d7f246a50.png)
因为“OLD”与“NEW”两个关键字的特性，所以它们可以使用的triggers种类会有一些限制：
| Trigger种类 | OLD | NEW |
| --- | --- | --- |
| INSERT | 不能使用 | 新增的字段资料 |
| UPDATE | 修改前的字段资料 | 修改后的字段资料 |
| DELETE | 删除前的字段资料 | 不能使用 |
以“cmdev.emp”表格的“UPDATE TRIGGER”来说，下列是使用“OLD”与“NEW”关键字取得的字段值：
[](http://box.kancloud.cn/2015-07-18_55a9d7f4b87b5.png)
延续上列为更新“cmdev.emp”表格执行纪录工作的trigger来说，如果想要让纪录的讯息更加详细，包含修改前与修改后的部门编号：
[](http://box.kancloud.cn/2015-07-18_55a9d7f56a9f7.png)
要完成上列的需求，就必须使用“OLD”与“NEW”关键字取得的字段值：
[](http://box.kancloud.cn/2015-07-18_55a9d7f96755f.png)
为表格建立“UPDATE TRIGGER”以后，就表示执行这个表格的修改动作，都会执行这个trigger元件：
[](http://box.kancloud.cn/2015-07-18_55a9d7fe9fed7.png)
如果要将“emp_before_update”的需求，修改为“只有在修改员工的部门编号时，才需要新增修改纪录”，你就可以使用在sotred routines讨论过的“IF”指令来完成这个需求：
[](http://box.kancloud.cn/2015-07-18_55a9d8066d681.png)
在“INSERT TRIGGER”中使用“NEW”关键字时，要特别注意“AUTO_INCREMENT”字段型态：
[](http://box.kancloud.cn/2015-07-18_55a9d80c21f93.png)
如果有需要的话，你也可以使用“SET”叙述设定“NEW”关键字指定的字段值。以下列的情况来说：
[](http://box.kancloud.cn/2015-07-18_55a9d8194ce34.png)
要解决上列的问题，你可以要求在新增资料的时候，不要使用小写的文字。不过使用下列的“BEFORE INSERT TRIGGER”来处理的话，会更方便一些：
[](http://box.kancloud.cn/2015-07-18_55a9d81af2f91.png)
建立好这个“BEFORE INSERT TRIGGER”以后，就算新增的员工资料包含小写的名称与职务，这个trigger元件都会在新增纪录之前，把它们转换为大写：
[](http://box.kancloud.cn/2015-07-18_55a9d8281264b.png)
# 5 查询Triggers的相关资讯
如果想要查询triggers的相关资讯，可以查询“information_schema.TRIGGERS”表格，下列是它的主要字段：
| 字段名称 | 型态 | 说明 |
| --- | --- | --- |
| TRIGGER_SCHEMA | varchar(64) | 数据库 |
| TRIGGER_NAME | varchar(64) | 名称 |
| EVENT_MANIPULATION | varchar(6) | 启动的事件，有INSERT、UPDATE与DELETE |
| EVENT_OBJECT_SCHEMA | varchar(64) | 作用的数据库 |
| EVENT_OBJECT_TABLE | varchar(64) | 作用的表格 |
| ACTION_STATEMENT | longtext | 执行的工作 |
| ACTION_TIMING | varchar(6) | 启动的时机，有BEFORE与AFTER |
如果你想要查询建立某个stored routines的详细资讯，可以使用下列的语法：
[](http://box.kancloud.cn/2015-07-18_55a9d83673948.png)
';