MySQL触发器事件讲解
本这逍遥子科技主要为各位介绍的是触发器（Trigger）。在介绍 Trigger 的语法前，先和读者聊聊触发器的特性。
MySQL 正式支持触发器是在 MySQL 5.0.2 的版本之后。触发器是注册在数据库表格上的程序。所以，在事件发生（对数据列做新增/修改/删除）时，数据库会依照触发条件（事件前/事件后）帮你执行预先储存好的程序。因此，常常有人利用触发器来验证数据的正确性，或做数据计算。
也因为触发器的功能十分强大，所以偶而就会发现有些人喜欢在将商业逻辑加到触发器中，甚至加了大量的触发器在单一的数据表中。但是触发器是非常昂贵的解决方案，就像是金庸笔下的七伤拳（先伤己，再伤敌；伤敌七分，自损三分），它会耗用大量的系统资源（尤其当在数据量非常庞大的系统中），过度的使用，反而造成数据库的效能下降，不可不防。
另外一个重点是，在支持交易（transaction）的储存引擎中，一但表格有设定触发器，则整个交易行为（包含触发器的新增/修改/删除等全部的操作），也是不可分割的（atomic）。
接下来，我们来介绍出发器的时机，事件与对象的种类与关系。
触发器的时机：
事件前：
before
事件后：
after
触发器事件：
新增：
insert
load data
replace
修改：
update
删除：
update
replace
不包括（drop table, truncate
table）
参考对象：
NEW：表示”新”的数据对象。在下列的操作时会出现。
insert
update
OLD：表示”旧”的数据对象。在下列的操作时会出现。
update
delete
如果你想取得在线手册，同样的，你可以使用”help create trigger”来取得创建触发器的语法。
创建触发器语法如下：
|
1 2 3 4 5 6 7 8 |
mysql> help create trigger Name: 'CREATE TRIGGER' Description: Syntax: CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body |
在创建触发器时，有下列的步骤需预先知道或特别注意的：
- 首先，创建触发器时，使用者必须要有SUPER的权限。
- 和先前创建Stored Procedure一样，在创建触发器时，可以指定DEFINER。而在执行触发器的时候，会先检查DEFINER的设定。而该使用者必须要有三个权限：
- SUPER的权限。
- 如果使用OLD.[字段]或NEW.[字段]，则必须要有SELECT数据表的权限。
- 如果使用SET NEW.[拦位] = “值”，则必须要有UPDATE数据表的权限。
- 指定DEFINER后，要再指定trigger的时间点是注册的事件。同时指定”for each row”，让每笔数据修改都触发MySQL的操作。
- 最后，在begin和end的语法中，指定触发器要操作的行为。
再来，我们来看看几个创建触发器的范例：
范例一：新增后的触发器。
(after insert)
|
1 2 3 4 5 6 7 8 9 |
mysql> delimiter // mysql> create trigger ai_t1 -> after insert -> on t1 -> for each row -> begin -> insert into i1 values (new.id, new.name); -> end// mysql> delimiter ; |
这个触发器就是在新增动作结束后，便将新增数据的id, name同步储存在i1的数据表中。
范例二：更新后的触发器。
(after update)
|
1 2 3 4 5 6 7 8 9 |
mysql> delimiter // mysql> create trigger au_t1 -> after update -> on t1 -> for each row -> begin -> insert into u1 values(old.id, old.name, new.id, new.name)// -> end// mysql> delimiter ; |
这个触发器就是在更新动作结束后，便将新/旧的id, name数据，同步储存在u1的数据表中。
范例三：删除后的触发器。
(after delete)
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> delimiter // mysql> drop trigger if exists ad_p// mysql> create trigger ad_p -> after delete -> on p -> for each row -> begin -> insert into old_p values (old.id, old.no); -> delete from c where c.id=old.id; -> end// mysql> delimiter ; |
这个触发器就是在删除动作结束后，便将旧的id, no数据，同步储存在old_p的数据表中，同时，将数据表c中有关于被删除id的相关资料也一并删除(这样的行为雷同数据表的外来键delete cascade，但还多了一个logging的机制)。
看完范例后，你也可以开始在你的MySQL数据库中，创建一个触发器来玩玩啰。
再来我们要介绍MySQL的事件。
什么是”事件”？”事件”是数据库系统根据时间排程而执行的工作。
由于它是由”时间”来触发执行，所以，我们可以将”事件”视为是一种”暂时”的触发器。
事件是由一个特定的线程来管理，而拥有PROCESS权限的使用者则可以用”SHOW PROCESSLIST”指令来看目前执行的工作。
首先，我们可以先来检查MySQL的事件排程是否已经启动。
|
1 2 3 4 5 6 7 |
mysql> select @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | OFF | +-------------------+ 1 row in set (0.00 sec) |
如果没有启动，则你可以利用下面的指令将它开启。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> set global event_scheduler = ON; mysql> select @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set (0.00 sec) mysql> show processlist; +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | 8 | event_scheduler | localhost | NULL | Daemon | 240 | Waiting on empty queue | NULL | | 9 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ 2 rows in set (0.00 sec) |
再来我们要看看创建事件的语法。
类似的指令，我们可以由”help create event”来取得创建事件的语法：
创建事件的语法如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> help create event Name: 'CREATE EVENT' Description: Syntax: CREATE [DEFINER = { user | CURRENT_USER }] EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] DO event_body;
schedule: AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...]
interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND} |
在使用创建事件时，我们需要注意的地方如下：
1. 创建者（DEFINER）：如之前预存函数/触发器的设定一样，使用者可以设定DEFINER。
2. 时间触发的次数：可分为一次性或多次触发。如果是一次性的触发，可使用”AT”；如果是多次的触发，则使用”EVERY”。
3. 触发的起始时间设定：可用”STARTS”来设定起始时间；”ENDS”来设定结束时间。
4. 时间间隔（interval）：时间间隔的单位，可参考在线手册。
5. 保存事件：默认值是”ON COMPLETION NOT PRESERVE”。也就是在事件完成后(如果是多次触发事件，则是超过事件结束时间”ENDS”)，该事件就会自动被删除。如果，你想保留这个事件，则必须指定”ON COMPLETION PRESERVE”。
再来，我们来看个创建事件的范例：
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> set global event_scheduler=on; mysql> grant event on stanley.* to 'bob'@'localhost'; mysql> create -> definer='stanley'@'localhost' -> event if not exists event_name -> on schedule every 10 second -> on completion preserve -> enable -> comment 'Trigger this event in every 10 seconds.' -> do -> select now() into outfile '/tmp/now.txt'; |
这个事件就是每十秒触发一次的事件，其动作是将系统时间储存在外部的档案上(/tmp/now.txt)，而且这是一个没有设定结束时间的事件。
结论：
触发器与事件是一个好用的工具，数据库管理师可以将商业逻辑包装在数据库中，则可以减少应用程序的工作，并可确保跨应用程序中，仍可确保相同的”数据”逻辑，常见的用途是对数据库的稽核。但如开宗明义所说的，触发器是把双面刃，滥用的情况下，反而会造成资源的耗损，不可不查也～