如何使用MySQL Before触发器

在MySQL中，BEFORE触发器是一个数据库对象，您可以定义以在指定事件之前自动执行，例如UPDATE、INSERT或DELETE。触发器允许您根据数据库内发生的特定事件执行自定义代码或执行特定的操作。

BEFORE触发器在实际操作之前执行，并可用于修改数据或执行附加验证或计算。在本文中，我们将学习如何使用MySQL BEFORE触发器。

MySQL BEFORE触发器的语法和结构:

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

triggerName（触发器名称）: 这是您为触发器分配的名称。

triggerTime（触发时间）: 表示触发器应在指定事件之前执行。

triggerEvent（触发事件）: 它指定第一个触发器应触发的数据库事件。

tableName（表名称）: 它是应用触发器的表的名称。

FOR EACH ROW（对于每一行）: 它确定触发器应对每一行进行执行。

BEGIN 和 END（开始和结束）: 包含一个或多个SQL语句的触发器体的封闭语句块。

关于MySQL BEFORE触发器的一些要点：

• BEFORE触发器非常适合执行数据完整性校验和数据验证；这意味着您可以使用第一个触发器来确保特定字段不为空、强制唯一约束条件或在允许任何操作之前验证数据格式。
• 在利用触发器记录对表所做的更改之前，您可以创建一个审计追踪来跟踪和维护数据历史记录，其中包括有关更改的相关信息，如用户、时间戳和修改后的值。
• BEFORE触发器可以自动执行涉及多个表的复杂操作，您可以使用BEFORE触发器根据特定条件更新相关表或在实体间同步数据。
• 触发器应该简明扼要，专注于关键任务，避免不必要的复杂性。复杂的触发器可能会影响性能和可维护性。
• 在将触发器部署到生产环境之前，请在受控环境中进行彻底测试，以确保它们按预期工作并不会导致任何意外问题。
• 为每个触发器编写清晰的文档，包括其目的、行为和任何依赖关系。这有助于促进对数据库的理解和维护工作。
• 过度使用触发器可能会影响数据库性能，因此必要时需要对其进行优化。

MySQL BEFORE触发器的类型:

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

1. BEFORE INSERT触发器:

在MySQL中，BEFORE INSERT触发器是一种数据库对象，允许您在插入操作发生前自动执行操作。它是一种在实际将数据插入表中之前触发的数据库触发器。

当在表上定义一个INSERT触发器时，触发器在实际插入发生之前被执行。它允许您修改要插入的数据或根据特定条件执行其他操作。

语法:

CREATE TRIGGER triggerName
BEFORE INSERT ON tableName
FOR EACH ROW
BEGIN
    -- Trigger logic and actions go here
END;  

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

示例1：

让我们创建一个名为’train_passengers’的表，该表包含Id、Name、Address和ticketCharges等字段。使用给定的语句创建表：

CREATE TABLE train_passengers (
          Id INT,
          Name VARCHAR(50),
          Address VARCHAR(50),
          ticketCharges INT
        );

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

Id	Name	Address	ticketCharges

INSERT INTO train_passengers
VALUES(115, 'Chaitali', 'Bhopal', 4500),
(154, 'Himesh', 'Ghaziabad', 6000),
(326, 'Harshad', 'Hyderabad', 5000),
(454, 'Naman', 'Mumbai', 6500);

在向’train_passengers’表中插入值之后，它将如下所示：

Id	Name	Address	ticketCharges
115	Chaitali	Bhopal	4500
154	Himesh	Ghaziabad	6000
326	Harshad	Hyderabad	5000
454	Naman	Mumbai	6500

现在我们将创建一个名为’train_ticket’的触发器，在将新的乘客记录输入表中时，设置它从新的票务费用中减去1000元。请使用以下语句创建触发器：

CREATE TRIGGER train_ticket
BEFORE INSERT ON train_passengers
FOR EACH ROW
    SET new.ticketCharges = new. ticketCharges - 1000;

创建触发器后，执行它。

现在，我们将使用给定的命令在“train_passengers”表中插入一条新记录：

INSERT INTO train_passengers
VALUES(256, 'Kavya', 'Roorkee', 7500);

使用以下语句查看结果：

SELECT * FROM train_passengers;

正如下面所示，新的费用中扣除了1000卢比的机票费。

示例2：

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

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

CREATE TABLE employees (
          Id INT PRIMARY KEY,
          Name VARCHAR(60),
          Age INT,
          Address VARCHAR(60),
          Income INT
        );

创建了一个表格，并且它将看起来像下面所示：

ID	Name	Age	Address	Income

现在，我们将使用给定的命令向 ’employees’ 表格中插入值：

INSERT INTO employees
VALUES(101, 'Anjali', 35, 'Meerut', 39000),
(102, 'Divya', 27, 'Meerut', 40000),
(103, 'Nisha', 32, 'Bhopal', 45000),
(104, 'Ishan', 34, 'Chandigarh', 37000);

在插入’employees’表中的值之后，它将如下所示：

Id	Name	Age	Address	Income
101	Anjali	35	Meerut	39000
102	Divya	27	Meerut	40000
103	Nisha	32	Bhopal	45000
104	Ishan	34	Chandigarh	37000

我们将创建一个名为’income_update’的触发器，用于在表中更新新员工的工资。如果新员工的工资低于30000，则将新员工的工资更新为30000。我们将使用以下语句来创建触发器：

DELIMITER //
CREATE TRIGGER income_update
BEFORE INSERT ON employees
FOR EACH ROW
IF new.income<30000 THEN SET new.income=30000;
END IF; //

创建触发器后，执行它。

现在，我们将使用以下语句向’employees’表插入一条新记录：

INSERT INTO employees


VALUES(105, 'Roshni', 30, 'Mumbai', 25000);

在执行上述语句之后，我们将使用以下语句显示“employees”表：

SELECT * FROM employees;

如下所示，新增员工的收入已更新为30000。

2. BEFORE UPDATE触发器：

在MySQL中，BEFORE UPDATE触发器是一个与特定表相关联的数据库对象，在对该表执行UPDATE操作之前自动执行动作。它允许您定义在修改表中的数据之前必须应用的自定义动作。

语法：

CREATE TRIGGER triggerName
BEFORE UPDATE ON tableName
FOR EACH ROW
BEGIN
    -- Trigger logic and actions go here
END;  

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

示例1：

让我们创建一个名为’Laptops’的表，该表具有Id、ModelNumber、ModelName、Price、Insurance和Quantity等字段。

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

CREATE TABLE Laptops(
Id INT PRIMARY KEY,
ModelNumber INT,
ModelName VARCHAR(100),
Price FLOAT,
Insurance FLOAT,
Quantity INT
);

该表已创建，它的外观如下所示：

Id	ModelNumber	ModelName	Price	Insurance	Quantity

现在，我们将使用给定的命令在’Laptops’表中插入数值：

INSERT INTO Laptops
VALUES(1, 5124,"HP Ryzen 5", 45000, 200, 2),
(2, 5695,"Dell Inspiron 14", 55000, 180, 6),
(3, 4524,"ASUS Vivobook 16", 45200, 120, 6),
(4, 5795,"Lenovo ThinkBook 15", 35600, 140, 5),
(5, 1216,"ASUS Vivobook 14", 35000, 150, 2),
(6, 1221,"Dell Inspiron 16", 52000, 130, 6);

在插入’Laptops’表中的值后，它将如下所示：

Id	ModelNumber	ModelName	Price	Insurance	Quantity
1	5124	HP Ryzen 5	45000	200	2
2	5695	Dell Inspiron 14	55000	180	6
3	4524	ASUS Vivobook 16	45200	120	6
4	5795	Lenovo ThinkBook 15	35600	140	5
5	1216	ASUS Vivobook 14	35000	150	2
6	1221	Dell Inspiron 16	52000	130	6

我们将创建一个名为’Validate’的触发器，该触发器将用于更新表中的笔记本电脑价格。我们将使用以下语句来创建触发器：

DELIMITER //
CREATE TRIGGER Validate
BEFORE UPDATE
ON Laptops
FOR EACH ROW
BEGIN
IF NEW.Price < 1000 OR NEW.Price > 100000
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Price should be between Rs1000 and Rs1Lakh";
END IF; 
END;//

现在，我们将使用以下命令来更新笔记本电脑的价格：

UPDATE Laptops SET Price = 500
WHERE Id=2;

正如你所看到的，由于价格低于1000，它将显示一个错误。

现在，使用以下命令查看“Laptops”表，看价格是否发生了变化。

SELECT * FROM Laptops;

正如您所见，价格保持不变。

示例2：

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

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

CREATE TABLE staff(
Id INT PRIMARY KEY,
Name VARCHAR(60),
Age INT,
Address VARCHAR(60),
Wage INT
);

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

Id	Name	Age	Address	Wage

现在，我们将使用给定的命令将值插入 ‘staff’ 表格中：

INSERT INTO staff
VALUES(1, "Himanshu", 25, "Roorkee", 25000),
(2, "Vaibhav", 26, "Meerut", 26000),
(3, "Anushka", 28, "Roorkee", 30000),
(4, "Avni", 30, "Goa", 31000),
(5, "Khushi", 32, "Lucknow", 32000);

在向 ‘staff’ 表插入值之后，它将如下所示：

Id	Name	Age	Address	Wage
1	Himanshu	25	Roorkee	25000
2	Vaibhav	26	Meerut	26000
3	Anushka	28	Roorkee	30000
4	Avni	30	Goa	31000
5	Khushi	32	Lucknow	32000

我们将创建一个名为 ‘update_wage’ 的触发器，用于根据特定条件更新表中员工的工资。我们将使用以下语句来创建触发器：

DELIMITER //
CREATE TRIGGER update_wage
BEFORE UPDATE
ON staff
FOR EACH ROW
BEGIN
IF NEW.Wage < 25000
THEN
SET NEW.Wage=30000;
END IF; 
END;//

现在，我们将使用给定的命令来更新员工工资：

UPDATE staff SET Wage = 21000]
WHERE Id=2;

现在，使用以下命令查看’staff’表，看工资是否发生了变化。

SELECT * FROM staff;

正如您所见，Id=2的工资已经改变。

3. BEFORE DELETE触发器：

在MySQL中，BEFORE DELETE触发器是与表关联的数据库对象，它旨在在对表执行删除操作之前自动执行一个操作。

语法：

CREATE TRIGGER triggerName
BEFORE DELETE ON tableName
FOR EACH ROW
BEGIN
    -- Trigger logic and actions go here
END;  

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

示例1：

让我们创建一个名为“customers”的表，该表具有包括Id、Name、Age、Address和PhoneNumber在内的字段。

使用给定的命令来构建“customers”表：

CREATE TABLE customers(
Id INT PRIMARY KEY,
Name VARCHAR(55),
Age INT,
Address VARCHAR(55),
PhoneNumber FLOAT
);

表已创建，并且将如下所示：

Id	Name	Age	Address	PhoneNumber

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

INSERT INTO customers
VALUES(1, "Rohit", 25, "Mumbai", 9652320000),
(2, "Aryan", 23, "Goa", 9561310000),
(3, "Sakshi", 19, "Lucknow", 8562310000),
(4, "Naveen", 27, "Kanpur", 8525850000),
(5, "Harshita", 24, "Meerut", 9656230000);

在插入值到’customers’表之后，它将如下所示：

Id	Name	Age	Address	PhoneNumber
1	Rohit	25	Mumbai	9652320000
2	Aryan	23	Goa	9561310000
3	Sakshi	19	Lucknow	8562310000
4	Naveen	27	Kanpur	8525850000
5	Harshita	24	Meerut	9656230000

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

DELIMITER //
CREATE TRIGGER before_delete
BEFORE DELETE
ON customers
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "You cannot delete the phone number from customers table.";
END//

现在，我们将使用给定的命令从 ‘customers’ 表中删除：

DELETE FROM customers
WHERE Id=2;

正如您所看到的，在应用DELETE触发器后，它显示了一个错误。

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

SELECT * FROM customers;

如下图所示，没有进行任何删除操作。

示例2：

让我们创建一个名为’labour’的表，它的字段包括Id、Name、Age、Address和Payment。

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

CREATE TABLE labour(
Id INT PRIMARY KEY,
Name VARCHAR(55),
Age INT,
Address VARCHAR(55),
Payment INT
);

表格已创建，它看起来如下所示：

Id	Name	Age	Address	Payment

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

INSERT INTO labour
VALUES(1, "Rekha", 32, "Meerut", 11000),
(2, "Neetu", 35, "Bangalore", 10000),
(3, "Salman", 38, "Goa", 12000),
(4, "Harsh", 29, "Meerut", 14000),
(5, "Nishant", 28, "Bangalore", 16000);

在’labour’表中插入值后，它将如下所示：

Id	Name	Age	Address	Payment
1	Rekha	32	Meerut	11000
2	Neetu	35	Bangalore	10000
3	Salman	38	Goa	12000
4	Harsh	29	Meerut	14000
5	Nishant	28	Bangalore	16000

我们将使用以下语句创建一个名为’before_delete’的触发器：

DELIMITER //
CREATE TRIGGER before_delete
BEFORE DELETE
ON labour
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Not allowed";
END//

现在，我们将使用以下命令从 ‘labour’ 表中删除：

DELETE FROM labour
WHERE Id=1;

正如你所看到的，在应用DELETE触发器之后，它显示了一个错误。

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

SELECT * FROM labour;

正如您在下面所看到的，没有任何被删除。

结论:

在本文中，我们学习了如何使用MySQL BEFORE触发器。我们已经了解到有三种不同类型的BEFORE触发器：BEFORE UPDATE触发器，BEFORE INSERT触发器和BEFORE DELETE触发器。我们通过示例充分理解了每种类型的BEFORE触发器。