mysql - 在mysql中创建触发器以将一个表中所做的更改保存到另一个表中

Question

我是使用触发器的新手，但在阅读了相当多的内容后，我认为它是满足我需要的最佳解决方案。我在mysql 中有一个名为employees 的表，我想在另一个名为employee_changes 的表中创建对employees 所做的更改的审计跟踪。但我只想为员工表中已更改的列创建审核条目。因此，例如，如果用户更改员工的工资，我希望在employee_changes中创建一行，捕获员工的ID、已更改的列的名称(在本例中为工资)、旧值和新值工资以及用户的日期和姓名。当记录保存在前端时，进行更改的用户的名称将始终在员工表中的字段(LastUpdatedBy)中更新，因此当触发器触发时，它可以从LastUpdatedBy 列。

在This Post的帮助下和 This Post我能够想出这段代码:

DELIMITER //
CREATE TRIGGER emp_update
AFTER INSERT
ON employees FOR EACH ROW
BEGIN
DECLARE col_names CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'employees'
ORDER BY ordinal_position;
select FOUND_ROWS() into num_rows;
SET i = 1;
the_loop: LOOP
IF i > num_rows THEN
    CLOSE col_names;
    LEAVE the_loop;
END IF;

FETCH col_names 
INTO col_name;    

 IF OLD.col_name <> NEW.col_name THEN
    INSERT INTO employee_changes (EmployeeNumber, FieldName, OldValue, NewValue, ChangeDate, ChangedBy) 
    VALUES(OLD.EmployeeNumber, col_name, OLD.col_name, NEW.col_name, NOW(), NEW.LastUpdatedBy);
 END IF;

SET i = i + 1;  
END LOOP the_loop;

END //
DELIMITER ;

但是当我运行它时，没有任何反应，INFORMATION_SCHEMA.TRIGGERS 中没有创建触发器。因此，当我更新员工表时，我的employee_changes 表中什么也没有发生。

我感觉我快要到了，但我却卡住了。

感谢您的帮助。

Answer 1

以下行肯定会引发错误:

IF OLD.col_name <> NEW.col_name THEN

因为 col_name 不是字段的名称，它是一个变量。这在 MySQL 中永远行不通，您必须提供准确的列名称。忘记列名的动态查询，您必须对每一列的测试进行硬编码。

同样，在插入语句中，您不能使用 OLD.col_name 或 NEW.col_name 公式，您必须一一显式提供字段名称。如果更改表的结构，则必须更改触发器的代码。

不幸的是，这是 MySQL 的限制，目前我不知道此问题有任何解决方案或解决方法。