如何使用MySQL的After触发器

在MySQL中，After触发器是一种在指定事件之后执行的触发器，特别是在表上进行插入、更新或删除操作之后。它允许您定义在事件发生后自动执行的操作或过程。本文将介绍如何使用MySQL的After触发器。

MySQL After触发器的语法和结构：

CREATE TRIGGER triggerName
AFTER triggerEvent
ON tableName
FOR EACH ROW
BEGIN
    -- SQL statements
END;

triggerName: 这是您为触发器指定的名称。

triggerTime: 它指示触发器应在指定事件之后执行。

triggerEvent: 它指定将触发触发器的触发事件。

tableName: 这是正在创建触发器的表的名称。

FOR EACH ROW: 它表示触发器将对受触发操作影响的每一行执行。

BEGIN and END: 触发器逻辑和操作应该在BEGIN和END语句之间编写。

关于MySQL AFTER触发器的几点要记住：

• AFTER触发器与特定事件（如INSERT、UPDATE或DELETE）相关联，并在事件完成后执行；这意味着如果您创建了AFTER INSERT触发器，则它将在将新行添加到表中后触发和执行。
• 当您创建AFTER触发器时，需要指定表名、事件（INSERT、UPDATE、DELETE）和触发时间（AFTER）。您还可以在触发器的正文中定义应在其内部执行的操作或语句。
• AFTER触发器可以用于许多目的，例如强制数据完整性约束、记录更改、更新相关表或执行其他计算或验证操作。
• MySQL允许为单个表的单个事件定义多个AFTER触发器。执行的顺序由创建触发器的顺序确定。您可以使用“SHOW TRIGGERS”命令查看现有触发器及其执行顺序。
• 如果在AFTER触发器内部发生错误，则会停止触发器的执行，并回滚在触发器内部所做的任何更改，从而确保数据库保持一致状态并防止数据修改不完整。

MySQL AFTER触发器的类型：

MySQL有三种类型的AFTER触发器：AFTER INSERT触发器、AFTER UPDATE触发器和AFTER DELETE触发器。

1. AFTER INSERT触发器：

在MySQL中，AFTER INSERT触发器是一种数据库对象，允许您在添加新记录到表中后自动执行操作。

要在MySQL中创建AFTER INSERT触发器，您必须使用CREATE TRIGGER语句，指定表名、表事件（INSERT）和触发时间（AFTER）。触发器正文可以包含一个或多个在BEGIN和END关键字之间封闭的SQL语句。

语法：

CREATE TRIGGER afterInsertTrigger
AFTER INSERT ON tableName
FOR EACH ROW
BEGIN
    -- Trigger statements here
END;

在上面的例子中，”afterInsertTrigger”是您给触发器命名的名称，”tableName”是创建触发器的表的名称，”columnName”是表中特定列的名称。您可以在BEGIN和END块中定义自己的逻辑和函数。

示例1：

让我们创建两个表：’school_student’，其中包含Id、Name、Class、Age和Address等字段，以及’school_audit’，其中包含Id和audit_description字段。

使用给定的命令来创建’school_student’表：

CREATE TABLE school_student (
          Id INT PRIMARY KEY,
          Name VARCHAR(45),
          Class INT,
          Age INT,
          Address VARCHAR(45)
        );

创建了表格，并且它将如下所示：

现在，我们将在’school_student’表中插入值。

INSERT INTO school_student
VALUES(301, "Vaishnav", 8, 12, 'Mathura'),
(302, "Amisha", 9, 13, "Noida"),
(303, "Disha", 8, 12, "Lucknow"),
(304, "Kuna", 10, 14, "Lucknow"),
(305, "Kairav", 10, 15, "Lucknow");

在插入值到’school_student’表后，它将以下面的形式显示：

现在，我们将使用以下命令创建’school_audit’表：

CREATE TABLE school_audit (
          Id INT PRIMARY KEY,
          audit_description VARCHAR(120)
        );

表已创建，将会如下所示：

Id | audit_description
—|—
现在我们将创建一个名为 ‘after_insert_school’ 的触发器，当在 ‘school_student’ 表中添加新行时，将插入审核详细信息到 ‘school_audit’ 表中。使用以下语句创建触发器：

CREATE TRIGGER after_insert_school
AFTER INSERT ON school_student
FOR EACH ROW
    INSERT INTO school_audit
    VALUES(1, concat("A new student detail has been inserted", date_format(now(), '%d, %m, %y %h:%i:%s %p')));

创建触发器后，执行它。

现在，我们将插入值到 ‘school_student’ 表中，

INSERT INTO school_student
VALUES(306, 'Arhan', 10, 15, 'Noida');

插入值到’school_student’表后，我们将使用以下语句查看该表：

SELECT * FROM school_student;

‘school_student’表将如下所示显示;

我们已经在“school_student”表上应用了触发器，每当在“school_student”表中插入一个新的行，日期和时间就会被插入到“school_audit”表中。

我们将使用以下语句查看“school_audit”表：

SELECT * FROM school_audit;

正如您在下面看到的那样，日期和时间会自动插入到“school_audit”表中。

示例 2：

让我们创建两个表：’factory_worker’，它具有Id、Name、Age、Address & Wage等字段，和’factory_audit’，它具有Id和audit_description等字段。

使用下面的命令创建’factory_worker’表：

CREATE TABLE factory_worker (
          Id INT PRIMARY KEY,
          Name VARCHAR(45),
          Age INT,
          Address VARCHAR(45),
          Wage INT
        );

表被创建，将如下所示：

现在，我们将在 ‘factory_worker’ 表中插入值，

INSERT INTO factory_worker 
VALUES(401, "Nakul", 25, "Roorkee", 15000),
(402, "Aisha", 26, "Lucknow", 17000),
(403, "Dipika", 30, "Meerut", 16000),
(404, "Karan", 26, "Roorkee", 18000),
(405, "Avinash", 32, "Noida", 19000);

在向’factory_worker’表中插入值之后，它会看起来如下所示：

现在，我们将使用以下命令创建’factory_audit’表：

CREATE TABLE factory_audit (
          Id INT PRIMARY KEY,
          audit_description VARCHAR(150)
        );

表已创建，它将如下所示：

现在我们将创建一个名为“after_insert_factory”的触发器，当向“factory_worker”表添加新行时，将插入审计详细信息到“factory_audit”表中。使用以下语句创建触发器：

CREATE TRIGGER after_insert_factory
AFTER INSERT ON factory_worker
FOR EACH ROW
    INSERT INTO factory_audit VALUES(1, concat("A new worker detail has been inserted", date_format(now(), '%d, %m, %y %h:%i:%s %p')));

创建触发器后，执行它。

现在，我们将在’school_student’表中插入值，

INSERT INTO factory_worker
VALUES(406, 'Kritika', 35, 'Meerut', 18000);

在将值插入“factory_worker”表后，使用给定的命令查看表格：

“factory_worker”表格将如下所示显示。

由于我们在’factory_worker’表上应用了触发器，每插入一行数据到’factory_worker’表中，日期和时间就会插入到’factory_audit’表中。

我们可以使用以下语句查看’factory_audit’表：

SELECT * FROM factory_audit;

正如您在下方所看到的，日期和时间会自动插入到“factory_audit”表中。

2. AFTER UPDATE触发器:

在MySQL中，AFTER UPDATE触发器是一种数据库对象，允许您在新记录添加到表后自动执行操作。要在MySQL中创建AFTER UPDATE触发器，必须使用CREATE TRIGGER语句，指定表名、触发时机（AFTER）、触发事件（UPDATE）和触发器主体。触发器主体由一个或多个SQL语句组成，这些语句被BEGIN和END关键字包围。

语法:

CREATE TRIGGER afterUpdateTrigger
AFTER UPDATE ON tableName
FOR EACH ROW
BEGIN
    -- Trigger statements here
END;

在上面的例子中，”afterUpdateTrigger”是您分配给触发器的名称，”tableName”是创建触发器的表的名称，”columnName”是表中特定列的名称。您可以在BEGIN和END块中定义自定义逻辑和函数。

示例1：

让我们创建第一个名为’snacks_details’的表，该表具有字段包括Id、Name、snacks_type、Quantity、Price和Calories。

使用以下命令创建’snacks_details’表：

CREATE TABLE snacks_details(
Id INT PRIMARY KEY,
Name VARCHAR(50),
snacks_type VARCHAR(40),
Quantity INT,
Price INT,
Calories FLOAT
);

表格已创建，它将如下所示：

现在，我们将使用给定的命令将值插入“snacks_details”表中：

INSERT INTO snacks_details
VALUES(1, "Samosa", "Vegan", 50, 2, 20.5),
(2, "Kachori", "Vegan", 100, 4, 100.2),
(3, "Cookies", "Non-Vegetarian", 200, 10, 50.4),
(4, "Dahi Vada", "Vegetarian", 250, 2, 24.5),
(5, "Panipuri", "Vegan", 120, 12, 50.2);

插入值到 ‘snacks_details’ 表后，它将如下所示：

现在，我们将创建第二个名为 ‘price_details’ 的表，它具有字段如 Id, snacks_id, old_price, new_price, 和 updated_date_time.

CREATE TABLE price_details(
Id INT PRIMARY KEY AUTO_INCREMENT,
snacks_id INT,
old_price INT,
new_price INT,
updated_date_time DATETIME
);

表被创建后，它将如下所示：

现在，我们将创建一个名为“after_update_price”的触发器，用于在“snacks_details”表中更新数量时，在“price_details”表中添加一行新记录。我们将使用以下语句创建触发器：

DELIMITER //
CREATE TRIGGER after_update_price
AFTER UPDATE ON snacks_details
FOR EACH ROW
BEGIN
IF OLD.price!=NEW.price THEN
INSERT INTO price_details(snacks_id, old_price, new_price, updated_date_time)
VALUES(OLD.id, OLD.price, NEW.price, NOW());
END IF;
END//

创建触发器后，执行它。

现在，我们将使用以下语句更新’id=1’在’snacks_details’表中的零食价格：

UPDATE snacks_details
SET Price = 60
WHERE Id = 1;

执行以上语句后，让我们使用以下语句检查’snacks_details’表：

SELECT * FROM snacks_details;

如下所示，’Id=1’的价格已经发生了变化。

现在，让我们使用以下语句来检查’price_details’表：

SELECT * FROM price_details;

正如您可以在下面看到的那样，表格中已插入了包含旧价格和新价格的行。

再次更新“snacks_details”表中零食的价格。这次我们将使用以下语句更新“Id=4”的零食价格：

UPDATE snacks_details
SET Price = 350
WHERE Id = 4;

在执行上述语句之后，让我们使用以下语句检查“snacks_details”表：

SELECT * FROM snacks_details;

正如您可以在下面看到的，’Id=4’的价格已经被修改。

现在，让我们使用以下语句来检查’price_details’表：

SELECT * FROM price_details;

正如你在下方所看到的，表格中已经插入了一行，显示了旧价格和新价格的细节。

示例 2：

让我们创建第一个名为“electrical_appliances”的表，它具有Id、Name、Quantity、manufacturing_year和Price等字段。

使用以下命令创建“electrical_appliances”表：

CREATE TABLE electrical_appliances(
Id INT PRIMARY KEY,
Name VARCHAR(60),
Quantity INT,
manufacturing_year YEAR,
Price INT
);

表格已创建，将显示如下：

现在，我们将使用给定的命令将值插入到“electrical_appliances”表中：

INSERT INTO electrical_appliances
VALUES(1, "Television", 2, 2023, 45000),
(2, "Refrigerator", 4, 2022, 60000),
(3, "Oven", 5, 2022, 20000),
(4, "Coffee Maker", 6, 2023, 35000),
(5, "Blender", 4, 2023, 12000);

在’electrical_appliances’表中插入值后，将显示如下所示：

现在，我们将创建第二个名为’quantity_details’的表，该表具有字段Id、appliances_id、old_quantity、new_quantity和updated_date_time。

CREATE TABLE quantity_details(
Id INT PRIMARY KEY AUTO_INCREMENT,
appliances_id INT,
old_quantity INT,
new_quantity INT,
updated_date_time DATETIME
);

表已创建，它的样式如下所示：

现在，我们将创建一个名为“after_update_quantity”的触发器，每当在“电器”表中更新数量时，将在“quantity_details”表中添加一行。我们将使用以下语句来创建触发器：

DELIMITER //
CREATE TRIGGER after_update_quantity
AFTER UPDATE ON electrical_appliances
FOR EACH ROW
BEGIN
IF OLD.quantity!=NEW.quantity THEN
INSERT INTO quantity_details(appliances_id, old_quantity, new_quantity, updated_date_time)
VALUES(OLD.id, OLD.quantity, NEW.quantity, NOW());
END IF;
END//

创建触发器后，执行它。

现在，我们将使用以下语句更新’electrical_appliances’表中’Id=2’的数量：

UPDATE electrical_appliances
SET Quantity = 6
WHERE Id = 2;

执行上述语句后，让我们使用以下语句检查“electrical_appliances”表：

SELECT * FROM electrical_appliances;

正如您可以在下面看到的那样，“Id=2”的数量已经发生了变化。

现在，让我们使用以下语句检查“quantity_details”表：

SELECT * FROM quantity_details;

正如您在下面所看到的，表格中已插入了一行，显示了旧价格和新价格的详细信息。

再次更新’electrical_appliances’表的数量。这次我们将使用以下语句来更新’Id=5’的数量：

UPDATE electrical_appliances
SET Quantity = 5
WHERE Id = 5;

执行上述语句后，我们使用以下语句来检查“electrical_appliances”表：

SELECT * FROM electrical_appliances;

正如您可以在下面看到的那样，“Id=5”的数量已经发生了变化。

现在，让我们使用以下语句检查’quantity_details’表：

SELECT * FROM quantity_details;

正如你在下面所看到的，表格中插入了一行，显示了旧数量和新数量的详细信息。

3. AFTER DELETE 触发器：

在MySQL中，AFTER DELETE触发器是一个数据库对象，允许您在新记录添加到表后自动执行操作。

要在MySQL中创建一个AFTER DELETE触发器，您需要使用CREATE TRIGGER语句，指定表名、表事件（DELETE）和触发时机（AFTER）。触发器体可以包含一个或多个SQL语句，这些语句需要用BEGIN和END关键字括起来。

语法：

CREATE TRIGGER afterInsertTrigger
AFTER DELETE ON tableName
FOR EACH ROW
BEGIN
    -- Trigger statements here
END;

在上面的示例中，”afterDeleteTrigger”是您分配给触发器的名称，”tableName”是创建触发器的表的名称，”columnName”是表中特定列的名称。您可以在BEGIN和END块中定义自定义逻辑和函数。

示例1：

让我们创建一个名为’customers’的表，该表具有Id、Name、Age和Address等字段。

使用以下命令构建’customers’表：

CREATE TABLE customers(
Id INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Address VARCHAR(50)
);

表已创建，它将如下所示：

现在，我们将使用给定的命令将值插入到“customers”表中：

INSERT INTO customers
VALUES(1, "Rahul", 21, "Kanpur"),
(2, "Ashima", 24, "Kanpur"),
(3, "Sahil", 20, "Meerut"),
(4, "Nisha", 31, "Mumbai"),
(5, "Mihir", 29, "Lucknow");

在向’customers’表中插入值之后，它看起来会像下面这样：

现在，我们将创建另一个名为’backup_data’的表，该表将存储从’customers’表中删除的数据。

‘backup_data’表包含用户名称和评论等字段。

CREATE TABLE backup_data(
user_name VARCHAR(60),
comments VARCHAR(260)
);

表已创建，将如下所示：

我们将使用给定的语句创建一个名为’data_delete’的触发器：

DELIMITER //
CREATE TRIGGER data_delete
AFTER DELETE
ON customers
FOR EACH ROW
BEGIN
INSERT INTO backup_data
VALUES (current_user(), CONCAT('Deleted data-', old.id, ' ', old.name, ' ', old.age, ' ', old.address, ' ', 'Deleted on ', NOW()));
END//

创建触发器后，执行它。

现在，我们将使用给定的命令从“customers”表中删除数据：

DELETE FROM customers
WHERE Id=2;

现在，我们将使用给定的语句查看’customers’表：

SELECT * FROM customers;

如下所示，数据’Id=2’已被删除。

现在我们将检查 ‘backup_data’ 表，以查看删除的数据是否已存储在表中。

我们将使用给定的语句查看 ‘backup_data’ 表：

SELECT * FROM backup_data;

如下所示，删除的数据已存储在表中。

示例2：

让我们创建一个名为’staff’的表，它包含诸如Id、Name、Age、Address和Salary等字段。

使用给定的命令来构建’staff’表：

CREATE TABLE staff(
Id INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Address VARCHAR(50),
Salary INT
);

表格已经创建，它将如下所示：

现在，我们将使用给定的命令将值插入到“staff”表中：

INSERT INTO staff
VALUES(1, "Aryan", 25, "Meerut", 25000),
(2, "Karishma", 26, "Meerut", 26000),
(3, "Muskan", 27, "Kanpur", 26000),
(4, "Sameer", 28, "Lucknow", 28000),
(5, "Ansh", 28, "Mumbai", 28000);

在向“staff”表中插入值后，它的外观如下所示：

现在，我们将创建另一个名为“backup”的表，该表将存储从“staff”表中删除的数据。

“backup”表包括字段：用户和评论。

CREATE TABLE backup(
User VARCHAR(65),
Comments VARCHAR(275)
);

表格被创建，它的样子看起来像下面这样:

我们将使用给定的语句创建一个名为’after_delete’的触发器:

DELIMITER //
CREATE TRIGGER after_delete
AFTER DELETE
ON staff
FOR EACH ROW
BEGIN
INSERT INTO backup
VALUES (current_user(), CONCAT('Deleted data-', old.id, ' ', old.name, ' ', old.age, ' ', old.address, ' ', old.salary, ' ', 'Deleted on ', NOW()));
END//

创建触发器后执行它。

现在，我们将使用给定的命令从“staff”表中删除数据：

DELETE FROM staff
WHERE Id=1;

现在，我们将使用给定的语句查看“staff”表：

SELECT * FROM staff;

就如下所示，’Id=1’的数据已被删除。

现在我们将检查’backup’表，看看删除的数据是否已经存储在表中。我们将使用给定的语句来查看’backup’表：

SELECT * FROM backup;

正如下面所示，已删除数据已存储在表中。

结论:

在本文中，我们学习了如何使用MySQL AFTER触发器。我们了解到有三种类型的AFTER触发器：AFTER UPDATE触发器，AFTER INSERT触发器和AFTER DELETE触发器。我们通过示例透彻地了解了每种类型的AFTER触发器。

本文链接：http://so.lmcjl.com/news/15998/