MySQL更新后触发器

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

简介:在本教程中,您将学习如何创建MySQL AFTER UPDATE触发器来记录对表所做的更改。

MySQL AFTER UPDATE触发器简介

在与触发器关联的表上发生更新事件后,将自动调用MySQL AFTER UPDATE触发器。

下面显示了创建MySQL AFTER UPDATE触发器的语法:

CREATE TRIGGER trigger_name
AFTER UPDATE
ON table_name FOR EACH ROW
trigger_body

使用以下语法:

首先,在CREATE TRIGGER子句中指定要创建的触发器的名称。

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

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

最后,指定由一个或多个语句组成的触发器主体。

如果触发器主体具有多个语句,则需要使用BEGIN END块。
并且,您还需要更改默认定界符,如以下代码所示:

DELIMITER $$

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

DELIMITER ;

在AFTER UPDATE触发器中,您可以访问OLD和NEW行,但不能更新它们。

MySQL AFTER UPDATE触发器示例

让我们看一个创建AFTER 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)
);

其次,将示例数据插入Sales表:

INSERT INTO Sales(product, quantity, fiscalYear, fiscalMonth)
VALUES
    ('2001 Ferrari Enzo',140, 2021,1),
    ('1998 Chrysler Plymouth Prowler', 110,2021,1),
    ('1913 Ford Model T Speedster', 120,2021,1);

第三,从Sales表中查询数据以显示其内容:

SELECT * FROM Sales;

最后,创建一个表来存储销售表中数量列中的更改:

DROP TABLE IF EXISTS SalesChanges;

CREATE TABLE SalesChanges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salesId INT,
    beforeQuantity INT,
    afterQuantity INT,
    changedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

创建AFTER UPDATE触发器示例

以下语句在sales表上创建AFTER UPDATE触发器:

DELIMITER $$

CREATE TRIGGER after_sales_update
AFTER UPDATE
ON sales FOR EACH ROW
BEGIN
    IF OLD.quantity <> new.quantity THEN
        INSERT INTO SalesChanges(salesId,beforeQuantity, afterQuantity)
        VALUES(old.id, old.quantity, new.quantity);
    END IF;
END$$

DELIMITER ;

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

如果将"数量"列中的值更新为新值,触发器将插入新行以将更改记录在SalesChanges表中。

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

首先,触发器的名称是在CREATE TRIGGER子句中指定的after_sales_update:

CREATE TRIGGER after_sales_update

其次,触发事件是:

AFTER UPDATE

第三,与触发器关联的表是sales:

ON Sales FOR EACH ROW

最后,使用触发器主体中的IF-THEN语句检查新值是否与旧值不同,然后将更改插入SalesChanges表中:

IF OLD.quantity <> new.quantity THEN
    INSERT INTO SalesChanges(salesId,beforeQuantity, afterQuantity)
    VALUES(old.id, old.quantity, new.quantity);
END IF;

测试MySQL AFTER UPDATE触发器

首先,将ID为1的行的数量更新为350:

UPDATE Sales 
SET quantity = 350
WHERE id = 1;

after_sales_update被自动调用。

其次,从SalesChanges表中查询数据:

SELECT * FROM SalesChanges;

第三,将所有行的销售额增加到10%:

UPDATE Sales 
SET quantity = CAST(quantity * 1.1 AS UNSIGNED);

第四,从SalesChanges表中查询数据:

SELECT * FROM SalesChanges;

由于三行的更新,触发器触发了三次。

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