一、触发器介绍
MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程序，触发器是个特殊的存储过程。它们不同的是执行存储过程要使用CALL语句来调用，而触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE和DELETE语句。如果定义了触发程序，当对表执行INSERT、DELETE或UPDATE语句时，将激活触发程序。可以将触发程序设计为在执行语句之前或之后激活。例如，可以在从表中删除每一行之前，或在更新每一行之后激活触发程序。
二、创建触发器
触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如:可以根据客户当前的账户状态，控制是否允许插入新订单。
创建只有一个执行语句的触发器，语法如下：
|
1 2 3 4 5 6 7 |
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body |
trigger_name – 标识触发器名称，用户自行指定；
trigger_time – 标识触发时机，可以指定为before或after；
trigger_event – 标识触发事件，包括INSERT、UPDATE和DELETE；
tbl_name – 标识建立触发器的表名，即在哪张表上建立触发器；
trigger_body – 是触发器程序体，触发器程序可以使用begin和end作为一个事务，中间包含多条语句。
示例：创建单个执行语句的触发器
首先创建一个account表，表中有两个字段，分别为：acct_num字段（定义为int类型），amount字段（定义为浮点类型）。
其次创建一个名为tri_amount的INSERT触发器，触发的条件是向account表更新数据之后，对新插入的amount字段值进行求和计算。
代码如下：
|
1 2 3 4 5 6 |
CREATE TABLE account(id INT, amount decimal(10,2)); CREATE TRIGGER tri_insert_amount BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; |
然后往表中插入数据进行测试：
|
1 2 3 4 5 6 7 8 9 |
mysql> set @sum=0; mysql> INSERT INTO account values(1,1.20), (2,1.30); mysql> select @sum; +------+ | @sum | +------+ | 2.50 | +------+ 1 row in set (0.00 sec) |
可以看到@sum变量会随着amount字段插入的数值一直累加。这里我们使用的“NEW.amount”表示更新以后的列值，如果使用“OLD.amount”表示更新以前的列值，属于固定语法。
再来创建一个UPDATE类型的触发器：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DELIMITER // CREATE TRIGGER tri_update_amount BEFORE UPDATE ON account FOR EACH ROW BEGIN IF OLD.amount < 1.20 THEN SET NEW.amount = 1.20; ELSEIF OLD.amount > 1.20 THEN SET NEW.amount = 1.50; END IF; END;// DELIMITER ; |
创建多个执行语句的触发器
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE test1(a1 int); CREATE TABLE test2(a2 int); CREATE TABLE test3(a3 int NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4(a4 int NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 int DEFAULT 0); DELIMITER $ CREATE TRIGGER tri_test BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2=NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END$ DELIMITER ; |
上面的代码创建了一个名为tri_test的触发器，这个触发器的触发条件是在向表test1插入数据前执行触发器的语句，具体执行的代码如下：
|
1 2 3 |
INSERT INTO test3(a3) values(NULL),(NULL),(NULL),(NULL),(NULL); INSERT INTO test4(a4) values(0),(0),(0),(0),(0); INSERT INTO test1(a1) values(5),(2),(1),(4),(6); |
下面看一下四个表的变化
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
mysql> select * from test1\G *************************** 1. row *************************** a1: 5 *************************** 2. row *************************** a1: 2 *************************** 3. row *************************** a1: 1 *************************** 4. row *************************** a1: 4 ************************** 5. row *************************** a1: 6 mysql> select * from test2\G *************************** 1. row *************************** a2: 5 *************************** 2. row *************************** a2: 2 *************************** 3. row *************************** a2: 1 *************************** 4. row *************************** a2: 4 *************************** 5. row *************************** a2: 6 *************************** 1. row *************************** a3: 3 mysql> select * from test4\G *************************** 1. row *************************** a4: 1 b4: 1 *************************** 2. row *************************** a4: 2 b4: 1 *************************** 3. row *************************** a4: 3 b4: 0 *************************** 4. row *************************** a4: 4 b4: 1 *************************** 5. row *************************** a4: 5 b4: 1 |
执行结果显示，在向表test1插入记录的时候，test2、test3、test4都发生了变化。从这个例子看INSERT触发了触发器，向test2中插入了test1中的值，删除了test3中相同的内容，同时更新了test4的b4，即与插入的值相同的个数。
如果你使用OSC工具做过Online DDL操作，就会知道这个工具的原理就是利用INSERT、UPDATE、DELETE三个触发器来完成的。具体触发器如下，可以做做测试。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TRIGGER `pt_osc_test_online_table_del` AFTER DELETE ON `test`.`online_table` FOR EACH ROW DELETE IGNORE FROM `test`.`_online_table_new` WHERE `test`.`_online_table_new`.`id` <=> OLD.`id` CREATE TRIGGER `pt_osc_test_online_table_upd` AFTER UPDATE ON `test`.`online_table` FOR EACH ROW REPLACE INTO `test`.`_online_table_new` (`id`, `name`, `age`) VALUES (NEW.`id`, NEW.`name`, NEW.`age`) CREATE TRIGGER `pt_osc_test_online_table_ins` AFTER INSERT ON `test`.`online_table` FOR EACH ROW REPLACE INTO `test`.`_online_table_new` (`id`, `name`, `age`) VALUES (NEW.`id`, NEW.`name`, NEW.`age`) |
就拿这三个触发器来说，第一个触发器作用就是当“online_table”表有删除操作时，就会对应使用DELETE IGNORE语句删除“_online_table_new”表的记录，如果“_online_table_new”表没有这条记录则忽略。第二个触发器作用就是当“online_table”表有更新操作时，就会对应使用REPLACE INTO语句更新“_online_table_new”表的记录，存在就更新，不存在就插入。第三个触发器作用就是当“online_table”表有插入操作时，就会对应使用REPLACE INTO语句更新“_online_table_new”表的记录，存在就更新，不存在就插入。具体OSC工具是如何运用这三个工具的，可以参考“pt-online-schema-change在线修改表结构”。
三、管理触发器
- 查看触发器
通过SHOW TRIGGERS查看触发器的语句如下：
|
1 |
mysql> SHOW TRIGGERS; |
在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过查询命令SELECT来查看，具体的语法如下：
|
1 2 |
mysql> SELECT * FROM INFORMATION_SCHAME.TRIGGERS\G mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tri_amount'\G |
- 删除触发器
使用DROP TRIGGER语句可以删除MySQL中以及定义的触发器，删除触发器语句基本语句格式如下：
|
1 |
mysql> DROP TRIGGER tri_insert_amount; |
四、总结
- 触发器对性能有损耗，应当非常谨慎使用。
- 对于事务表，触发器执行失败则整个语句回滚，保证一致性。
- 对于ROW格式主从复制，触发器不会在从库上执行。
- 使用触发器时应防止递归执行，导致出现死循环，耗尽CPU资源。
- 在使用触发器的时候需要注意，对于相同的表，相同的时间只能创建一种触发器类型。比如，对表account创建了一个before insert触发器，那么如果对表account再次创建一个before insert触发器，MySQL将会报错。此时，只可以在表account上创建after insert或者before update类型的触发器。
灵活的运用触发器将为操作省去很多麻烦。另外对于无用的触发器及时删除。
