mysql触发器之创建多个触发器操作实例分析

本文实例讲述了mysql触发器之创建多个触发器操作。分享给大家供大家参考，具体如下：

这次记录的内容mysql 版本必须得是5.7.2+的哈，之前的会不好使的。废话不多说，咱们开始正文哈。

在mysql 5.7.2+版本之前，我们只能为表中的事件创建一个触发器，例如，只能为before update或after update事件创建一个触发器。 mysql 5.7.2+版本解决了这样限制，并允许我们为表中的相同事件和动作时间创建多个触发器。当事件发生时，触发器将依次激活。我们来参考创建第一个触发器中的语法。如果表中有相同事件有多个触发器，mysql 将按照创建的顺序调用触发器。要更改触发器的顺序，需要在for each row子句之后指定follows或precedes。我们来看下这两个词的说明：

• follows选项允许新触发器在现有触发器之后激活。
• precedes选项允许新触发器在现有触发器之前激活。

完事来看下使用显式顺序创建新的附加触发器的语法：

delimiter $$
create trigger trigger_name
[before|after] [insert|update|delete] on table_name
for each row [follows|precedes] existing_trigger_name
begin
…
end$$
delimiter ;

然后，我们来看一个在表中的同一个事件和动作上，创建多个触发器的例子。我们来基于products表进行演示，首先来创建一个新的price_logs表，完事呢，每当更改产品的价格(msrp列)时，要将旧的价格记录在一个名为price_logs的表中，先来看想sql：

create table price_logs (
 id int(11) not null auto_increment,
 product_code varchar(15) not null,
 price double not null,
 updated_at timestamp not null default 
       current_timestamp 
       on update current_timestamp,
 primary key (id),
 key product_code (product_code),
 constraint price_logs_ibfk_1 foreign key (product_code) 
 references products (productcode) 
 on delete cascade 
 on update cascade
);

完事，当表的before update事件发生时，创建一个新的触发器。触发器名称为before_products_update，具体实现如下所示：

delimiter $$
create trigger before_products_update 
  before update on products 
  for each row 
begin
   insert into price_logs(product_code,price)
   values(old.productcode,old.msrp);
end$$
delimiter ;

然后，当我们更改产品的价格，并使用以下update语句，最后查询price_logs表：

update products
set msrp = 95.1
where productcode = 's10_1678';
-- 查询结果价格记录
select * from price_logs;

上面查询语句执行后，得到以下结果：

+----+--------------+-------+---------------------+
| id | product_code | price | updated_at     |
+----+--------------+-------+---------------------+
| 1 | s10_1678   | 95.7 | 2017-08-03 02:46:42 |
+----+--------------+-------+---------------------+
1 row in set

可以看到结果中，它按我们预期那样工作了。

完事我们再来假设不仅要看到旧的价格，改变的时候，还要记录是谁修改了它。要实现这个，我们可以向price_logs表添加其他列，但是，为了实现多个触发器的演示，我们将创建一个新表来存储进行更改的用户的数据。这个新表的名称为user_change_logs，结构如下：

create table user_change_logs (
 id int(11) not null auto_increment,
 product_code varchar(15) default null,
 updated_at timestamp not null default current_timestamp 
 on update current_timestamp,
 updated_by varchar(30) not null,
 primary key (id),
 key product_code (product_code),
 constraint user_change_logs_ibfk_1 foreign key (product_code) 
 references products (productcode) 
 on delete cascade on update cascade
);

现在，我们创建一个在products表上的before update事件上激活的第二个触发器。 此触发器将更改的用户信息更新到user_change_logs表。 它在before_products_update触发后被激活：

delimiter $$
create trigger before_products_update_2 
  before update on products 
  for each row follows before_products_update
begin
  insert into user_change_logs(product_code,updated_by)
  values(old.productcode,user());
end$$
delimiter ;

然后我们来使用update语句更新指定产品的价格：

update products
set msrp = 95.3
where productcode = 's10_1678';

再来分别从price_logs和user_change_logs表查询数据：

mysql> select * from price_logs;
+----+--------------+-------+---------------------+
| id | product_code | price | updated_at     |
+----+--------------+-------+---------------------+
| 1 | s10_1678   | 95.7 | 2017-08-03 02:46:42 |
| 2 | s10_1678   | 95.1 | 2017-08-03 02:47:21 |
+----+--------------+-------+---------------------+
2 rows in set
mysql> select * from user_change_logs;
+----+--------------+---------------------+----------------+
| id | product_code | updated_at     | updated_by   |
+----+--------------+---------------------+----------------+
| 1 | s10_1678   | 2017-08-03 02:47:21 | root@localhost |
+----+--------------+---------------------+----------------+
1 row in set

如上所见，两个触发器按照预期的顺序激活执行相关操作了。完事我们来在information_schema数据库的triggers表中的action_order列，看下触发激活同一事件和操作的顺序：

mysql> select 
  trigger_name, action_order
from
  information_schema.triggers
where
  trigger_schema = 'yiibaidb'
order by event_object_table , 
     action_timing , 
     event_manipulation;
+--------------------------+--------------+
| trigger_name       | action_order |
+--------------------------+--------------+
| before_employee_update  |      1 |
| before_products_update  |      1 |
| before_products_update_2 |      2 |
+--------------------------+--------------+
3 rows in set

好啦，本次记录就到这里了。