如何使用MySQL AFTER触发器

在MySQL中，AFTER触发器是一种在指定事件之后执行的触发器，特别是在表上进行INSERT、UPDATE或DELETE操作之后。它允许您定义在事件发生后自动执行的操作或流程。在本文中，我们将学习如何使用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)。触发器主体可以包含一个或多个SQL语句，这些语句在BEGIN和END关键字之间。

语法：

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_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. UPDATE后触发器：

在MySQL中，UPDATE后触发器是一种数据库对象，允许您在向表中添加新记录后自动执行操作。要在MySQL中创建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);

在’ snak_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//

在创建触发器之后，执行它。

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

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”的触发器，用于在“electrical_appliances”表中更新数量时，在“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/18280/