MySQL - 更新后触发器

MySQL更新后触发器简介

你好，数据库爱好者们！今天，我们将深入探索MySQL更新后触发器的奇妙世界。如果你是新手，不用担心——我将作为你的友好向导，带领你走过这段旅程，就像我过去几年里为无数学生所做的那样。让我们从基础开始，逐步深入！

MySQL - After Update Trigger

触发器是什么？

想象你有一个神奇的闹钟，每当你的数据库中发生特定的事情时，它就会响起。触发器本质上就是这样的东西！它是一种特殊的存储程序，当数据库中发生特定事件时，会自动执行。

更新后触发器是什么？

更新后触发器是一种在表上执行了UPDATE操作后触发的特定类型的触发器。它就像一个警惕的助手，在你更改数据后立即采取行动。

创建你的第一个更新后触发器

让我们卷起袖子，创建我们的第一个更新后触发器！我们将从一个简单的例子开始，以便我们能够轻松上手。

设置我们的实验场

首先，让我们创建一个简单的表来操作：

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

这个表将记录我们员工的信息。现在，让我们添加一个更新后触发器，以便在我们更改员工薪水时自动更新'last_updated'列。

创建触发器

以下是如何创建我们的更新后触发器：

DELIMITER //
CREATE TRIGGER update_employee_timestamp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
SET NEW.last_updated = CURRENT_TIMESTAMP;
END IF;
END //
DELIMITER ;

让我们分解一下：

DELIMITER //: 这会临时更改分隔符，以便我们可以在触发器定义中使用分号。
CREATE TRIGGER update_employee_timestamp: 我们将触发器命名为'update_employee_timestamp'。
AFTER UPDATE ON employees: 这指定触发器应在更新'employees'表后触发。
FOR EACH ROW: 触发器将对每次更新影响的每一行触发。
IF OLD.salary != NEW.salary THEN: 我们检查薪水是否发生变化。
SET NEW.last_updated = CURRENT_TIMESTAMP: 如果薪水发生变化，我们将'last_updated'列更新为当前时间戳。
DELIMITER ;: 这将分隔符重置为分号。

测试我们的触发器

让我们看看触发器是如何工作的：

INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000);
UPDATE employees SET salary = 55000 WHERE id = 1;
SELECT * FROM employees;

运行这些命令后，你将看到'last_updated'列已经被自动填充了当前时间戳。

高级更新后触发器技术

现在我们已经掌握了基础知识，让我们探索一些更高级的技术。

记录更改

更新后触发器的常见用途之一是记录更改。让我们创建一个新表来记录薪水的更改：

CREATE TABLE salary_changes (
id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10, 2),
new_salary DECIMAL(10, 2),
change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

现在，让我们修改触发器来记录这些更改：

DELIMITER //
CREATE TRIGGER log_salary_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_changes (employee_id, old_salary, new_salary)
VALUES (NEW.id, OLD.salary, NEW.salary);
END IF;
END //
DELIMITER ;

现在，每次更新员工薪水时，这个触发器都会在'salary_changes'表中创建一个新的条目。

使用条件逻辑

触发器还可以包含更复杂的条件逻辑。假设我们想防止薪水减少：

DELIMITER //
CREATE TRIGGER prevent_salary_decrease
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < OLD.salary THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be decreased';
END IF;
END //
DELIMITER ;

如果有人尝试将员工的薪水更新为较低值，这个触发器将引发错误。

使用客户端程序更新后触发器

虽然我们一直在直接在MySQL中创建触发器，但值得注意的是，你也可以通过客户端程序管理触发器。许多数据库管理工具和编程语言提供了与MySQL触发器工作的接口。

使用PHP创建触发器

以下是如何使用PHP创建触发器的示例：

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// 创建触发器的SQL
$sql = "
CREATE TRIGGER update_employee_timestamp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
SET NEW.last_updated = CURRENT_TIMESTAMP;
END IF;
END
";

// 执行查询
if ($conn->multi_query($sql) === TRUE) {
echo "Trigger created successfully";
} else {
echo "Error creating trigger: " . $conn->error;
}

$conn->close();
?>

这个PHP脚本连接到你的MySQL数据库并创建了与我们之前创建的'update_employee_timestamp'触发器相同的触发器。

结论

好了，各位！我们已经穿越了MySQL更新后触发器的领域，从基础到一些更高级的技术。记住，触发器是强大的工具，但请明智地使用它们。它们可以是双刃剑——对于维护数据完整性和自动化任务来说非常棒，但过度使用可能会导致性能问题。

与任何编程概念一样，掌握触发器的关键是实践。所以，继续尝试不同的场景，看看触发器如何让你的数据库工作更加智能，而不是更加努力。祝触发愉快！

方法	描述
CREATE TRIGGER	创建一个新的触发器
DROP TRIGGER	删除一个现有的触发器
SHOW TRIGGERS	显示关于触发器的信息
SIGNAL	在触发器内引发一个错误或警告消息
OLD	在更新触发器中引用列的旧值
NEW	在更新触发器中引用列的新值

Credits: Image by storyset