MySQL触发器之：`触发器`的`事件`：`INSERT`、`UPDATE`和`DELETE`事件的用法。

MySQL触发器：INSERT、UPDATE和DELETE事件详解

大家好，今天我们来深入探讨MySQL触发器中三个核心事件：INSERT、UPDATE和DELETE。触发器是数据库中一种强大的自动化机制，它能够在特定的数据库操作发生时自动执行预定义的代码块。理解这三个事件的工作原理以及如何正确地使用它们，对于构建健壮、高效且具有高度完整性的数据库应用至关重要。

触发器的基本概念

首先，简单回顾一下触发器的基本概念。一个触发器由以下几个关键部分组成：

• 触发事件(Event): 触发触发器执行的数据库操作。
• 触发时间(Timing): 触发器是在事件发生前(BEFORE)还是发生后(AFTER)执行。
• 触发对象(Table): 触发器关联的表。
• 触发动作(Action): 触发器执行的代码块，通常是一段SQL语句或者存储过程调用。

今天我们主要关注触发事件，即INSERT、UPDATE和DELETE这三种。

INSERT事件

INSERT事件是指当向表中插入新数据时触发的事件。我们可以创建BEFORE INSERT和AFTER INSERT两种类型的触发器。

• BEFORE INSERT触发器: 在新行插入之前执行。主要用于验证数据的有效性、修改数据或者阻止插入操作。
• AFTER INSERT触发器: 在新行插入之后执行。主要用于执行一些后续操作，例如记录日志、更新其他表的数据等。

示例1：使用BEFORE INSERT触发器验证数据

假设我们有一个employees表，其中有一个salary字段，我们希望确保插入的salary值不能为负数。

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

DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative.';
    END IF;
END//
DELIMITER ;

在这个例子中，我们创建了一个名为before_employee_insert的BEFORE INSERT触发器。FOR EACH ROW表示触发器将对每一行插入操作执行。在BEGIN...END块中，我们使用IF语句检查NEW.salary是否小于0。NEW是一个特殊的关键字，它代表即将被插入的新行。如果salary小于0，我们使用SIGNAL SQLSTATE抛出一个错误，阻止插入操作。

测试：

INSERT INTO employees (name, salary) VALUES ('John Doe', 5000.00); -- 成功
INSERT INTO employees (name, salary) VALUES ('Jane Smith', -1000.00); -- 失败，报错：Salary cannot be negative.

示例2：使用AFTER INSERT触发器记录日志

假设我们想要在每次向employees表插入新数据时，记录到employee_logs表中。

CREATE TABLE employee_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    action VARCHAR(255),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_logs (employee_id, action) VALUES (NEW.id, 'Inserted new employee');
END//
DELIMITER ;

在这个例子中，我们创建了一个名为after_employee_insert的AFTER INSERT触发器。在BEGIN...END块中，我们使用INSERT语句向employee_logs表插入一条记录，记录了新插入的员工ID和操作类型。

测试：

INSERT INTO employees (name, salary) VALUES ('Peter Jones', 6000.00);

SELECT * FROM employee_logs; -- 将显示一条记录，employee_id为新插入的员工的ID，action为'Inserted new employee'

UPDATE事件

UPDATE事件是指当表中现有数据被修改时触发的事件。同样，我们可以创建BEFORE UPDATE和AFTER UPDATE两种类型的触发器。

• BEFORE UPDATE触发器: 在行更新之前执行。主要用于验证数据的有效性、修改数据或者阻止更新操作。
• AFTER UPDATE触发器: 在行更新之后执行。主要用于执行一些后续操作，例如记录日志、更新其他表的数据等。

示例3：使用BEFORE UPDATE触发器限制薪资涨幅

假设我们希望限制employees表中salary字段的涨幅，每次更新不能超过10%。

DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary > OLD.salary * 1.10 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary increase cannot exceed 10%.';
    END IF;
END//
DELIMITER ;

在这个例子中，我们创建了一个名为before_employee_update的BEFORE UPDATE触发器。OLD是一个特殊的关键字，它代表被更新的行的原始值。我们使用IF语句检查NEW.salary是否大于OLD.salary的1.1倍。如果超过，我们抛出一个错误，阻止更新操作。

测试：

INSERT INTO employees (name, salary) VALUES ('Alice Brown', 7000.00);

UPDATE employees SET salary = 7500.00 WHERE name = 'Alice Brown'; -- 成功

UPDATE employees SET salary = 8000.00 WHERE name = 'Alice Brown'; -- 失败，报错：Salary increase cannot exceed 10%.

示例4：使用AFTER UPDATE触发器更新审计信息

假设我们想要在每次更新employees表的数据时，更新一个employee_audit表，记录更新的用户、时间以及更新前后的数据。

CREATE TABLE employee_audit (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    old_name VARCHAR(255),
    new_name VARCHAR(255),
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    updated_by VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, old_name, new_name, old_salary, new_salary, updated_by)
    VALUES (OLD.id, OLD.name, NEW.name, OLD.salary, NEW.salary, USER());
END//
DELIMITER ;

在这个例子中，我们创建了一个名为after_employee_update的AFTER UPDATE触发器。在BEGIN...END块中，我们使用INSERT语句向employee_audit表插入一条记录，记录了被更新的员工ID、更新前后的姓名和薪资，以及更新的用户。USER()函数返回当前用户的用户名。

测试：

UPDATE employees SET salary = 7200.00, name = 'Alice Brown Updated' WHERE name = 'Alice Brown';

SELECT * FROM employee_audit; -- 将显示一条记录，包含更新前后的数据和更新用户信息。

DELETE事件

DELETE事件是指当从表中删除数据时触发的事件。同样，我们可以创建BEFORE DELETE和AFTER DELETE两种类型的触发器。

• BEFORE DELETE触发器: 在行删除之前执行。主要用于验证删除操作是否允许、阻止删除操作或者执行一些清理工作。
• AFTER DELETE触发器: 在行删除之后执行。主要用于执行一些后续操作，例如记录日志、更新其他表的数据等。

示例5：使用BEFORE DELETE触发器阻止删除特定员工

假设我们有一个employees表，我们想要阻止删除id为1的员工。

DELIMITER //
CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.id = 1 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete employee with ID 1.';
    END IF;
END//
DELIMITER ;

在这个例子中，我们创建了一个名为before_employee_delete的BEFORE DELETE触发器。我们使用IF语句检查OLD.id是否等于1。如果等于1，我们抛出一个错误，阻止删除操作。

测试：

INSERT INTO employees (name, salary) VALUES ('Special Employee', 8000.00); -- 手动设置Special Employee的id为1

DELETE FROM employees WHERE id = 1; -- 失败，报错：Cannot delete employee with ID 1.

DELETE FROM employees WHERE name = 'Peter Jones'; --成功

示例6：使用AFTER DELETE触发器记录删除日志

假设我们想要在每次从employees表删除数据时，记录到employee_logs表中。

DELIMITER //
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_logs (employee_id, action) VALUES (OLD.id, 'Deleted employee');
END//
DELIMITER ;

在这个例子中，我们创建了一个名为after_employee_delete的AFTER DELETE触发器。在BEGIN...END块中，我们使用INSERT语句向employee_logs表插入一条记录，记录了被删除的员工ID和操作类型。

测试：

DELETE FROM employees WHERE name = 'Alice Brown Updated';

SELECT * FROM employee_logs; -- 将显示一条记录，employee_id为被删除的员工的ID，action为'Deleted employee'

OLD和NEW关键字的总结

关键字	事件	描述
OLD	UPDATE	表示更新之前的行。
OLD	DELETE	表示要被删除的行。
NEW	UPDATE	表示更新之后的行。
NEW	INSERT	表示要被插入的行。
OLD	INSERT	不可用。
NEW	DELETE	不可用。

注意事项

• 性能影响: 触发器会增加数据库的负载，过度使用或编写不当的触发器可能会影响数据库的性能。
• 循环触发: 要避免创建循环触发器，即一个触发器触发另一个触发器，最终又触发了第一个触发器，导致无限循环。
• 事务: 触发器是在同一个事务中执行的，如果触发器中的操作失败，整个事务将会回滚。
• 可维护性: 触发器可能会使数据库逻辑变得复杂，难以理解和维护。因此，应谨慎使用触发器，并做好文档记录。
• 权限: 创建和使用触发器需要相应的数据库权限。

触发器的优缺点

优点：

• 自动化: 能够自动执行数据库操作，无需手动干预。
• 数据完整性: 能够强制执行数据完整性约束，防止无效数据进入数据库。
• 审计: 能够记录数据库操作，方便进行审计和追溯。
• 业务规则: 能够实现复杂的业务规则，例如级联更新和删除。

缺点：

• 性能: 可能会影响数据库的性能。
• 复杂性: 可能会使数据库逻辑变得复杂。
• 可维护性: 可能会降低数据库的可维护性。
• 调试: 调试触发器可能比较困难。

总结与建议

今天我们详细讲解了MySQL触发器中INSERT、UPDATE和DELETE这三个事件的用法，以及BEFORE和AFTER两种触发时间的选择。同时，我们也探讨了OLD和NEW关键字的含义，并给出了使用触发器的一些注意事项。希望通过今天的学习，大家能够更加深入地理解和掌握MySQL触发器，并在实际应用中灵活运用。

在实际开发中，应该根据具体的需求和场景来决定是否使用触发器。如果能够通过其他方式实现相同的功能，例如应用层代码或者存储过程，那么可能不需要使用触发器。但是，在某些情况下，触发器是实现特定功能的最佳或者唯一选择。

总而言之，触发器是数据库管理中一个功能强大的工具，它可以帮助我们实现自动化、数据完整性、审计和业务规则。但是，我们也需要谨慎使用触发器，并充分考虑其性能、复杂性和可维护性等方面的影响。只有这样，我们才能充分发挥触发器的优势，构建更加健壮、高效和可靠的数据库应用。

思考与进阶

触发器不是万能的，要合理使用。学习触发器，要掌握它的本质，充分理解OLD和NEW，才能更好的应用。