MySQL触发器更新字段值|极客笔记

MySQL触发器更新字段值

在MySQL数据库中，触发器（Trigger）是用于在表上定义的一种特殊类型的存储过程。触发器可以在特定的事件（如INSERT、UPDATE、DELETE）发生时自动触发，执行预定义的操作。在本文中，我们将重点介绍如何使用MySQL触发器来更新表中的字段值。

创建表和插入数据

首先，我们需要创建一个示例表（employees）和插入一些数据作为演示。以下是创建表的SQL语句：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

接下来，我们向该表插入一些示例数据：

INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);
INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);

现在，我们已经创建了一个名为employees的表，并向其中插入了一些数据，接下来我们将创建一个触发器来更新表中的字段值。

创建触发器

在MySQL中，我们可以使用CREATE TRIGGER语句来创建一个触发器。触发器可以在INSERT、UPDATE或DELETE语句执行之前或之后触发。在本例中，我们将创建一个BEFORE INSERT触发器，用于在插入新记录时将工资字段的值设置为固定值（100000.00）。以下是创建触发器的SQL语句：

DELIMITER //
CREATE TRIGGER set_default_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   SET NEW.salary = 100000.00;
END;//
DELIMITER ;

在上面的代码中，我们创建了一个名为set_default_salary的BEFORE INSERT触发器，在每次插入新记录时，在新记录的salary字段上设置一个固定的值100000.00。

测试触发器

现在，让我们测试刚刚创建的触发器。我们将尝试插入一个新记录，并查看触发器是否将salary字段的值设置为100000.00。

INSERT INTO employees (name) VALUES ('David');

执行以上INSERT语句后，我们可以查询employees表，验证触发器是否起作用：

SELECT * FROM employees;

查询结果应该如下所示：

+----+--------+----------+
| id | name   | salary   |
+----+--------+----------+
|  1 | Alice  | 100000.00|
|  2 | Bob    | 100000.00|
|  3 | Charlie| 100000.00|
|  4 | David  | 100000.00|
+----+--------+----------+

如上所示，新插入的记录（David）的工资字段值已被触发器设置为100000.00。

更新触发器

有时候，我们可能需要更新触发器的逻辑。例如，我们可能需要在更新记录时自动调整工资字段的值。以下是一个示例，我们创建一个AFTER UPDATE触发器，用于使工资字段保持在某个范围内（50000到150000）：

DELIMITER //
CREATE TRIGGER adjust_salary_range
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary < 50000.00 THEN
      SET NEW.salary = 50000.00;
   ELSEIF NEW.salary > 150000.00 THEN
      SET NEW.salary = 150000.00;
   END IF;
END;//
DELIMITER ;

在上面的代码中，我们创建了一个名为adjust_salary_range的AFTER UPDATE触发器，当更新记录时，检查工资字段的值是否在50000到150000之间，如果不在这个范围内，则将其调整到最接近的边界值。

测试更新触发器

现在，我们将尝试更新某个员工的工资字段，并查看更新触发器是否起作用：

UPDATE employees
SET salary = 200000.00
WHERE name = 'Bob';

执行以上UPDATE语句后，我们可以再次查询employees表，查看工资字段的值是否被更新：

SELECT * FROM employees;

查询结果应该如下所示：

+----+--------+----------+
| id | name   | salary   |
+----+--------+----------+
|  1 | Alice  | 100000.00|
|  2 | Bob    | 150000.00|
|  3 | Charlie| 100000.00|
|  4 | David  | 100000.00|
+----+--------+----------+

如上所示，更新记录时，触发器检查并调整了Bob的工资字段的值，使其保持在50000到150000之间。