MySQL触发器详细介绍附有实例代码及代码讲解

触发器（Trigger）详细介绍

触发器是一种特殊类型的数据库对象，能够在特定的事件发生时自动执行预定义的操作。触发器可以帮助我们在数据修改（插入、更新或删除）时保持数据的一致性及完整性，记录审计日志，或实现复杂的业务逻辑。

触发器主要有三种事件类型和两种触发时机：

触发器类型与时机

1. 事件类型：

   • INSERT：当向表中插入新记录时触发。
   • UPDATE：当对表中的记录进行更新时触发。
   • DELETE：当从表中删除记录时触发。

2. 触发时机：

   • BEFORE：在执行插入、更新或删除操作之前触发。
   • AFTER：在执行插入、更新或删除操作之后触发。

这种设计使得触发器能够在数据变化时自动响应，在需要保持审计日志或执行自动化控制时非常有用。

示例 SQL 及详细解释

数据库表结构

首先，我们定义两个表：employees（员工信息表）和 audit_log（审计日志表）。employees 表用于存储员工信息，audit_log 用于记录对 employees 的操作日志。

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

CREATE TABLE audit_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(10),
    employee_id INT,
    changed_at DATETIME
);

1. INSERT 触发器示例

SQL 代码

DELIMITER //

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, changed_at)
    VALUES ('INSERT', NEW.id, NOW());
END //

DELIMITER ;

详细解释

• DELIMITER //：更改语句终止符，以便定义触发器体，确保 SQL 解析器不会误将 ; 作为触发器定义的结束。
• CREATE TRIGGER before_employee_insert：创建一个触发器，名为 before_employee_insert。
• BEFORE INSERT ON employees：指定触发器在 employees 表插入操作之前触发。
• FOR EACH ROW：表示对于每一行插入操作，触发器将被执行一次。
• BEGIN … END：定义触发器的逻辑体。
• INSERT INTO audit_log：将插入操作的信息写入 audit_log 表。
  • VALUES (‘INSERT’, NEW.id, NOW())：
    • 'INSERT' 表示操作类型。
    • NEW.id 是新插入行的 ID。需要注意的是，在插入操作时，ID 可能并不会在插入前可用，因此通常在插入前应使用其他方法获取新行的 ID（如在插入后使用 LAST_INSERT_ID()）。
    • NOW() 获取当前时间戳，记录操作发生的时间。

2. UPDATE 触发器示例

SQL 代码

DELIMITER //

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, changed_at)
    VALUES ('UPDATE', NEW.id, NOW());
END //

DELIMITER ;

详细解释

• CREATE TRIGGER after_employee_update：创建一个触发器，名为 after_employee_update。
• AFTER UPDATE ON employees：指定触发器将在 employees 表更新操作之后触发。
• FOR EACH ROW：对于每一行更新操作，触发器将被执行一次。
• INSERT INTO audit_log：将更新操作的信息写入 audit_log 表。
  • VALUES (‘UPDATE’, NEW.id, NOW())：
    • 'UPDATE' 表示操作类型。
    • NEW.id 是更新后的行 ID，表示被更新的员工。
    • NOW() 记录操作发生的时间。

3. DELETE 触发器示例

SQL 代码

DELIMITER //

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, changed_at)
    VALUES ('DELETE', OLD.id, NOW());
END //

DELIMITER ;

详细解释

• CREATE TRIGGER after_employee_delete：创建一个触发器，名为 after_employee_delete。
• AFTER DELETE ON employees：指定触发器在 employees 表删除操作之后触发。
• INSERT INTO audit_log：将删除操作的信息写入 audit_log 表。
  • VALUES (‘DELETE’, OLD.id, NOW())：
    • 'DELETE' 表示操作类型。
    • OLD.id 是被删除行的 ID，表示被删除的员工。
    • NOW() 记录操作发生的时间。

使用示例

1. 插入数据：

INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Developer', 60000);

插入后，audit_log 中会记录一条插入操作的日志。

2. 更新数据：

UPDATE employees SET salary = 65000 WHERE name = 'John Doe';

更新后，audit_log 中将新增一条更新操作的日志。

3. 删除数据：

DELETE FROM employees WHERE name = 'John Doe';

删除后，audit_log 中会记录一条删除操作的日志。