极客笔记如何使用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)
);
创建了表格，并且它将如下所示：
| ID | 姓名 | 班级 | 年龄 | 地址 |
|---|---|---|---|---|
现在，我们将在’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’表后，它将以下面的形式显示：
| Id | Name | Class | Age | Address |
|---|---|---|---|---|
| 301 | Vaishnav | 8 | 12 | Mathura |
| 302 | Amisha | 9 | 13 | Noida |
| 303 | Disha | 8 | 12 | Lucknow |
| 304 | Kunal | 10 | 14 | Lucknow |
| 305 | Kairav | 10 | 15 | Lucknow |
现在，我们将使用以下命令创建’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
);
表被创建，将如下所示：
| Id | Name | Age | Address | Wage |
|---|---|---|---|---|
现在，我们将在 ‘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’表中插入值之后，它会看起来如下所示：
| Id | Name | Age | Address | Wage |
|---|---|---|---|---|
| 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_audit’表：
CREATE TABLE factory_audit (
Id INT PRIMARY KEY,
audit_description VARCHAR(150)
);
表已创建，它将如下所示：
| Id | audit_description |
|---|---|
现在我们将创建一个名为“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
);
表格已创建，它将如下所示：
| Id | Name | snacks_type | Quantity | Price | Calories |
|---|---|---|---|---|---|
现在，我们将使用给定的命令将值插入“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’ 表后，它将如下所示：
| Id | Name | snacks_type | Quantity | Price | Calories |
|---|---|---|---|---|---|
| 1 | Samosa | Vegan | 2 | 50 | 20.5 |
| 2 | Kachori | Vegan | 4 | 100 | 100.2 |
| 3 | Cookies | Non-Vegetarian | 10 | 200 | 50.4 |
| 4 | Dahi Vada | Vegetarian | 2 | 250 | 24.5 |
| 5 | Panipuri | Vegan | 12 | 120 | 50.2 |
现在，我们将创建第二个名为 ‘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
);
表被创建后，它将如下所示：
| Id | snacks_id | old_price | new_price | updated_date_time |
|---|---|---|---|---|
现在，我们将创建一个名为“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
);
表格已创建，将显示如下：
| Id | Name | Quantity | manufacturing_year | Price |
|---|---|---|---|---|
现在，我们将使用给定的命令将值插入到“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’表中插入值后，将显示如下所示：
| Id | Name | Quantity | manufacturing_year | Price |
|---|---|---|---|---|
| 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 |
现在，我们将创建第二个名为’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
);
表已创建，它的样式如下所示：
| Id | appliances_id | old_quantity | new_quantity | updated_date_time |
|---|---|---|---|---|
现在，我们将创建一个名为“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)
);
表已创建，它将如下所示：
| Id | Name | Age | Address |
|---|---|---|---|
现在，我们将使用给定的命令将值插入到“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’表中插入值之后，它看起来会像下面这样：
| Id | Name | Age | Address |
|---|---|---|---|
| 1 | Rahul | 21 | Kanpur |
| 2 | Ashima | 24 | Kanpur |
| 3 | Sahil | 20 | Meerut |
| 4 | Nisha | 31 | Mumbai |
| 5 | Mihir | 29 | Lucknow |
现在，我们将创建另一个名为’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”表中插入值后，它的外观如下所示：
| Id | Name | Age | Address | Salary |
|---|---|---|---|---|
| 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 |
现在，我们将创建另一个名为“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触发器。