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

其次，在销售表中插入一些行：

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

第三，从销售表中查询数据以验证插入：

SELECT * FROM sales;

创建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 ;

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

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

让我们详细研究一下触发器：

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

CREATE TRIGGER before_sales_update

其次，触发事件是：

BEFORE UPDATE

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

ON sales FOR EACH ROW

第四，声明变量并将其值设置为错误消息。
请注意，在"触发前"中，可以通过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);

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

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

IF new.quantity > old.quantity * 3 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = errorMessage;
END IF;

测试MySQL BEFORE UPDATE触发器

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

UPDATE sales 
SET quantity = 150
WHERE id = 1;

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

其次，从销售表中查询数据以验证更新：

SELECT * FROM sales;

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

UPDATE sales 
SET quantity = 500
WHERE id = 1;

MySQL发出此错误：

Error Code: 1644. The new quantity 500 cannot be 3 times greater than the current quantity 150

在这种情况下，触发器发现新数量引起违规并引发错误。

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

SHOW ERRORS;

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

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

MySQL BEFORE UPDATE触发器简介

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

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

CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body

使用以下语法：

首先，在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 ;

在更新前触发器中，您可以更新新值，但不能更新旧值。