MySQL BEFORE UPDATE Trigger

摘要：在本教程中，您将学习如何创建 MySQL BEFORE UPDATE触发器以在数据更新到表之前验证数据。

MySQL BEFORE UPDATE触发器简介

MySQL BEFORE UPDATE触发器在与触发器关联的表上发生更新事件之前自动调用。

以下是创建 MySQL BEFORE UPDATE触发器的语法：

CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body
Code language: SQL (Structured Query Language) (sql)

在这个语法中：

首先，在CREATE TRIGGER关键字后指定要创建的触发器的名称。

其次，使用BEFORE UPDATE子句指定调用触发器的时间。

第三，在ON关键字后指定触发器所属的表的名称。

最后，指定包含一个或多个语句的触发器主体。

如果您在trigger_body中有多个语句，则需要使用BEGIN END块。此外，您还需要更改默认分隔符，如下所示：

DELIMITER $$

CREATE TRIGGER trigger_name
    BEFORE UPDATE
    ON table_name FOR EACH ROW
BEGIN
    -- statements
END$$    

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

在BEFORE UPDATE触发器中，您可以更新NEW值，但不能更新OLD值。

MySQL BEFORE UPDATE触发器示例

让我们看一下使用BEFORE UPDATE触发器的示例。

设置样本表

首先，创建一个名为sales的新表来存储销售额：

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    product VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    fiscalYear SMALLINT NOT NULL,
    fiscalMonth TINYINT NOT NULL,
    CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),
    CHECK(fiscalYear BETWEEN 2000 and 2050),
    CHECK (quantity >=0),
    UNIQUE(product, fiscalYear, fiscalMonth),
    PRIMARY KEY(id)
);
Code language: SQL (Structured Query Language) (sql)

其次，向sales表中插入一些行：

INSERT INTO sales(product, quantity, fiscalYear, fiscalMonth)
VALUES
    ('2003 Harley-Davidson Eagle Drag Bike',120, 2020,1),
    ('1969 Corvair Monza', 150,2020,1),
    ('1970 Plymouth Hemi Cuda', 200,2020,1);
Code language: SQL (Structured Query Language) (sql)

三、从sales表查询数据验证插入：

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

创建BEFORE UPDATE触发器示例

以下语句在sales表上创建BEFORE UPDATE触发器。

DELIMITER $$

CREATE TRIGGER before_sales_update
BEFORE UPDATE
ON sales FOR EACH ROW
BEGIN
    DECLARE errorMessage VARCHAR(255);
    SET errorMessage = CONCAT('The new quantity ',
                        NEW.quantity,
                        ' cannot be 3 times greater than the current quantity ',
                        OLD.quantity);
                        
    IF new.quantity > old.quantity * 3 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = errorMessage;
    END IF;
END $$

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

在sales表中的每一行发生更新事件之前，触发器会自动触发。

如果将quantity列中的值更新为比当前值大 3 倍的新值，触发器将引发错误并停止更新。

让我们详细检查一下触发器：

首先，触发器的名称是CREATE TRIGGER子句中指定的before_sales_update ：

CREATE TRIGGER before_sales_update
Code language: SQL (Structured Query Language) (sql)

其次，触发事件是：

BEFORE UPDATE
Code language: SQL (Structured Query Language) (sql)

第三，触发器关联的表是sales ：

ON sales FOR EACH ROW
Code language: SQL (Structured Query Language) (sql)

第四，声明一个变量并将其值设置为错误消息。请注意，在BEFORE TRIGGER中，您可以通过OLD和NEW修饰符访问列的旧值和新值。

DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT('The new quantity ',
                        NEW.quantity,
                        ' cannot be 3 times greater than the current quantity ',
                        OLD.quantity);
Code language: SQL (Structured Query Language) (sql)

请注意，我们使用CONCAT()函数来形成错误消息。

最后，使用IF-THEN语句检查新值是否比旧值大 3 倍，然后使用SIGNAL语句引发错误：

IF new.quantity > old.quantity * 3 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = errorMessage;
END IF;
Code language: SQL (Structured Query Language) (sql)

测试 MySQL BEFORE UPDATE触发器

首先，将id为1的行的数量更新为150：

UPDATE sales 
SET quantity = 150
WHERE id = 1;
Code language: SQL (Structured Query Language) (sql)

它之所以有效，是因为新数量不违反规则。

其次，从sales表中查询数据来验证更新：

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

第三，将id为1的行的数量更新为500：

UPDATE sales 
SET quantity = 500
WHERE id = 1;
Code language: SQL (Structured Query Language) (sql)

MySQL 发出此错误：

Error Code: 1644. The new quantity 500 cannot be 3 times greater than the current quantity 150
Code language: SQL (Structured Query Language) (sql)

在本例中，触发器发现新数量导致违规并引发错误。

最后，使用SHOW ERRORS显示错误：

SHOW ERRORS;
Code language: SQL (Structured Query Language) (sql)

在本教程中，您学习了如何创建 MySQL BEFORE UPDATE触发器以在数据更新到表之前验证数据。