MySQL 同一事件和操作时间的多个触发器

摘要：在本教程中，您将学习如何为具有相同事件和操作时间的表创建多个触发器。

本教程与 MySQL 版本 5.7.2+ 相关。如果您使用旧版本的 MySQL，则教程中的语句将不起作用。

在 MySQL 5.7.2 版本之前，您只能为表中的某个事件创建一个触发器，例如，您只能为BEFORE UPDATE或AFTER UPDATE事件创建一个触发器。 MySQL 5.7.2+ 取消了此限制，并允许您为具有相同事件和操作时间的给定表创建多个触发器。当事件发生时，这些触发器将依次激活。

以下是定义触发器的语法，该触发器将在现有触发器之前或之后激活以响应相同的事件和操作时间：

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE|AFTER}{INSERT|UPDATE|DELETE} 
ON table_name FOR EACH ROW 
{FOLLOWS|PRECEDES} existing_trigger_name
BEGIN
    -- statements
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

在此语法中， FOLLOWS或PRECEDES指定是否应在现有触发器之前或之后调用新触发器。

FOLLOWS允许在现有触发器之后激活新触发器。
PRECEDES允许新触发器在现有触发器之前激活。

MySQL 多触发器示例

我们将使用示例数据库中的products表进行演示。

假设您想要更改产品的价格（ MSRP列）并将旧价格记录在名为PriceLogs的单独表中。

首先，使用以下CREATE TABLE语句创建一个新的price_logs表：

CREATE TABLE PriceLogs (
    id INT AUTO_INCREMENT,
    productCode VARCHAR(15) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    updated_at TIMESTAMP NOT NULL 
			DEFAULT CURRENT_TIMESTAMP 
            ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (productCode)
        REFERENCES products (productCode)
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);Code language: SQL (Structured Query Language) (sql)

其次，创建一个新触发器，该触发器在products表的BEFORE UPDATE事件发生时激活：

DELIMITER $$

CREATE TRIGGER before_products_update 
   BEFORE UPDATE ON products 
   FOR EACH ROW 
BEGIN
     IF OLD.msrp <> NEW.msrp THEN
         INSERT INTO PriceLOgs(productCode,price)
         VALUES(old.productCode,old.msrp);
     END IF;
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

三、查看产品S12_1099的价格：

SELECT 
    productCode, 
    msrp 
FROM 
    products
WHERE 
    productCode = 'S12_1099';Code language: SQL (Structured Query Language) (sql)

第三，使用以下UPDATE语句更改产品的价格：

UPDATE products
SET msrp = 200
WHERE productCode = 'S12_1099';Code language: SQL (Structured Query Language) (sql)

四、从PriceLogs表中查询数据：

SELECT * FROM PriceLogs;Code language: SQL (Structured Query Language) (sql)

它按预期工作。

假设您要记录更改价格的用户。为此，您可以向PriceLogs表添加一个附加列。

但是，为了演示多个触发器，我们将创建一个新的单独表来存储进行更改的用户的数据。

五、创建UserChangeLogs表：

CREATE TABLE UserChangeLogs (
    id INT AUTO_INCREMENT,
    productCode VARCHAR(15) DEFAULT NULL,
    updatedAt TIMESTAMP NOT NULL 
	DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP,
    updatedBy VARCHAR(30) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (productCode)
        REFERENCES products (productCode)
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);Code language: SQL (Structured Query Language) (sql)

第六，为products表创建一个BEFORE UPDATE触发器。该触发器在before_products_update触发器之后激活。

DELIMITER $$

CREATE TRIGGER before_products_update_log_user
   BEFORE UPDATE ON products 
   FOR EACH ROW 
   FOLLOWS before_products_update
BEGIN
    IF OLD.msrp <> NEW.msrp THEN
	INSERT INTO 
            UserChangeLogs(productCode,updatedBy)
        VALUES
            (OLD.productCode,USER());
    END IF;
END$$

DELIMITER ;Code language: HTML, XML (xml)

让我们快速测试一下。

第七，使用以下UPDATE语句更新产品的价格：

UPDATE 
    products
SET 
    msrp = 220
WHERE 
    productCode = 'S12_1099';Code language: SQL (Structured Query Language) (sql)

第八，从PriceLogs和UserChangeLogs表中查询数据：

SELECT * FROM PriceLogs;Code language: SQL (Structured Query Language) (sql)

SELECT * FROM UserChangeLogs;Code language: SQL (Structured Query Language) (sql)

如您所见，两个触发器均按预期顺序激活。

触发订单信息

如果您使用SHOW TRIGGERS语句来显示触发器，您将看不到触发器针对同一事件和操作时间激活的顺序。

SHOW TRIGGERS 
FROM classicmodels
WHERE `table` = 'products';Code language: SQL (Structured Query Language) (sql)

要查找此信息，您需要查询information_schema数据库的triggers表中的action_order列，如下所示：

SELECT 
    trigger_name, 
    action_order
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'classicmodels'
ORDER BY 
    event_object_table , 
    action_timing , 
    event_manipulation;Code language: SQL (Structured Query Language) (sql)

在本教程中，您学习了如何为具有相同事件和操作时间的表创建多个触发器。

本教程有帮助吗？