MySQL创建多个触发器

广告位

在本教程中,您将学习如何为MySQL中相同的事件和动作时间创建多个触发器。 本教程与MySQL5.7.2+版本…

在本教程中,您将学习如何为MySQL中相同的事件和动作时间创建多个触发器。

本教程与MySQL5.7.2+版本相关。 如果您有一个较旧版本的MySQL,本教程中的语句将无法正常工作。

MySQL5.7.2+版本之前,您只能为表中的事件创建一个触发器,例如,只能为BEFORE UPDATEAFTER UPDATE事件创建一个触发器。 MySQL 5.7.2+版本解决了这样限制,并允许您为表中的相同事件和动作时间创建多个触发器。当事件发生时,触发器将依次激活。

参考中的语法。如果表中有相同事件有多个触发器,MySQL将按照创建的顺序调用触发器。要更改触发器的顺序,需要在FOR EACH ROW子句之后指定FOLLOWSPRECEDES。如下说明 –

  • 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 ;  

MySQL多重触发器示例

我们来看如何一个在表中的同一个事件和动作上,创建多个触发器的例子。

下面将使用中的products表进行演示。假设,每当更改产品的价格(MSRP列)时,要将旧的价格记录在一个名为price_logs的表中。

首先,使用语句创建一个新的price_logs表,如下所示:

USE yiibaidb;  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 ;  

第三,我们更改产品的价格,并使用以下语句,最后查询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,结构如下:

USE yiibaidb;  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 products  SET msrp = 95.3  WHERE productCode = 'S10_1678';  

其次,分别从price_logsuser_change_logs表查询数据:

SELECT * FROM price_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  
SELECT * FROM user_change_logs;  

上面查询语句执行后,得到以下结果 –

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  

如上所见,两个触发器按照预期的顺序激活执行相关操作了。

触发器顺序

如果使用SHOW TRIGGERS语句,则不会在表中看到触发激活同一事件和操作的顺序。

SHOW TRIGGERS FROM yiibaidb;  

要查找此信息,需要如下查询information_schema数据库的triggers表中的action_order列,如下查询语句 –

SELECT       trigger_name, action_order  FROM      information_schema.triggers  WHERE      trigger_schema = 'yiibaidb'  ORDER BY event_object_table ,            action_timing ,            event_manipulation;  

上面查询语句执行后,得到以下结果 –

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  

在本教程中,我们向您展示了如何在MySQL的表中为同一事件创建多个触发器。

贺, 贺朝

关于作者: 贺朝

为您推荐