MySQL BEFORE UPDATE触发器

时间:2019-11-20 08:52:18  来源:igfitidea点击:

简介:在本教程中,您将学习如何在更新数据到表之前创建一个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 ;

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

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触发器以验证数据。