如何使用MySQL的After触发器

2024年10月22日 如何使用MySQL的After触发器 极客笔记

如何使用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触发器。

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

展开阅读全文