mysql 触发器进入历史

一、触发器

MySQL 触发器（Triggers）是一种数据库对象，它与表关联，能在特定的事件（如插入、更新或删除）发生时自动执行一些指定的操作。使用触发器可以帮助我们自动维护数据库的完整性、一致性，或者同步更新数据库中的相关数据。当涉及到将数据变更记录到历史表中时，触发器特别有用。

在 INSERT、UPDATE、DELETE 操作之前或之后触发并执行触发器中定义的 SQL 语句。

创建触发器以记录历史
假设我们有一个名为 employees 的表，我们想要记录每次对这个表进行的修改（更新或删除操作）到一个名为 employees_history 的历史表中。以下是如何设置这样的触发器的步骤：

‌创建历史表‌：

首先，你需要有一个地方来存储历史记录。这个表通常包含原表的所有相关字段，以及额外的字段来存储变更的时间戳和操作类型（比如是更新还是删除）。

CREATE TABLE employees_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action VARCHAR(10)
);

二、创建触发器‌

然后，你需要为 employees 表创建一个触发器，用于在更新或删除记录时将旧的数据复制到 employees_history 表中。

1‌、更新操作的触发器‌：

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_history (employee_id, name, position, salary, action)
    VALUES (OLD.id, OLD.name, OLD.position, OLD.salary, 'UPDATE');
END;

2‌、删除操作的触发器‌：

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_history (employee_id, name, position, salary, action)
    VALUES (OLD.id, OLD.name, OLD.position, OLD.salary, 'DELETE');
END;

3‌、创建AFTER INSERT触发器‌：

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_audit (employee_id, name, position, salary, action)
    VALUES (NEW.id, NEW.name, NEW.position, NEW.salary, 'INSERT');
END;

在这个例子中，after_employee_insert触发器会在每次向employees表插入新记录后被触发。触发器会使用NEW关键字来访问新插入的记录的值，并将这些值插入到employees_audit表中，同时记录下操作类型为’INSERT’。

4、查看触发器的状态、语法等信息

SHOW TRIGGERS;

5、删除触发器，如果没有指定 schema_name，默认为当前数据库

DROP TRIGGER [schema_name.]trigger_name;

三、注意事项

‌性能‌：触发器的执行会增加数据库操作的开销，尤其是在高频率更新或删除的场景下。确保评估触发器对性能的影响。

‌事务处理‌：如果原始操作在事务中回滚，触发器中执行的操作也会回滚。

‌权限‌：创建触发器需要特定的权限，确保你的数据库用户账户有足够的权限来创建触发器。

‌触发时机‌：你可以选择触发器在操作之前（BEFORE）还是之后（AFTER）触发。对于记录历史而言，BEFORE 触发器通常更合适，因为它允许你访问旧值（OLD）。

通过以上步骤，你可以设置一个简单但有效的系统来自动记录数据库表的历史变更。这对于审计、数据恢复或简单的变更跟踪都非常有用。