如何使用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 and END: 包含一个或多个SQL语句的触发器体。

关于MySQL 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);

在将值插入“笔记本电脑”表后，它将显示如下：

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、姓名、年龄、地址和工资等字段。

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

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

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

Id	Name	Age	Address	Payment

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

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触发器之后，它显示了一个错误。

现在，我们将使用给定的语句查看’labour’表：

SELECT * FROM labour;

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

结论：

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