极客笔记如何使用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)
);
表格已创建，其外观如下所示：
| Id | Name | Class | Age | Address |
|---|---|---|---|---|
现在，我们将在“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_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. 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
);
表格已创建，它将看起来如下所示：
| 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);
在’ snak_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//
在创建触发器之后，执行它。
现在，我们将使用以下语句在’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
);
表格已经创建好了，它的样式如下所示：
| 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”的触发器，用于在“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)
);
表已创建，它的样式将如下所示：
| 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)
);
表格已创建，如下所示：
| user_name | comments |
|---|---|
我们将使用以下语句创建一个名为’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
);
表格被创建，它将如下所示：
| Id | Name | Age | Address | Salary |
|---|---|---|---|---|
现在，我们将使用给定命令向“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)
);
表格已经创建好了，它的外观将如下所示：
| User | Comments |
|---|---|
我们将使用给定的语句创建一个名为’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触发器。