如何将mysql表中的所有更改记录到第二个表中? [英] How to log all changes in a mysql table to a second one?

问题描述

我使用此onChange-trigger将mysql数据库表"house"中的所有更改记录到第二个表house_history(具有完全相同的字段+版本ID)中.

DELIMITER //
CREATE TRIGGER house_change_trigger BEFORE UPDATE ON house
  FOR EACH ROW BEGIN
    INSERT INTO house_history
    (
      hnr,
      top,
      acc_nr
    )
    VALUES
    (
      OLD.hnr,
      OLD.top,
      OLD.acc_nr
    );
  END
//

触发器有效，我唯一的问题是，该表有80个字段，我不想在触发器中列出所有字段.

由于在表中定义其他字段时，我也希望触发器也将其复制.创建相应的历史记录表后，我还可以轻松地将触发器复制到另一个表.

是否可以复制已更新行的所有表字段并将其插入到历史表中(具有相同的字段名称)?

解决方案

假定两个表都具有相同的列，例如

INSERT INTO house_history SELECT * FROM house WHERE hnr = OLD.hnr

虽然我不确定是否允许从表中选择SELECT，但触发器将在其上激活.

但是在生产代码中，没有列列表的IMO快捷方式语句(如SELECT *或INSERT INTO)是不好的做法.

I use this onChange-trigger to log all changes within my mysql database tabel "house" to as second table house_history (which has exactly the same fields + a version ID).

DELIMITER //
CREATE TRIGGER house_change_trigger BEFORE UPDATE ON house
  FOR EACH ROW BEGIN
    INSERT INTO house_history
    (
      hnr,
      top,
      acc_nr
    )
    VALUES
    (
      OLD.hnr,
      OLD.top,
      OLD.acc_nr
    );
  END
//

The trigger works, my only issue is that, the table has 80 fields, and I don't want to list all of them in the trigger.

Cause when I define additional fields in the table I want the trigger to copy them as well. And I also will be able easily to copy the trigger to another table after creating the corresponding history-table.

Is there a way to copy all the tables fields of the updated row and insert them to the history-table (having the same field names)?

解决方案

Assuming both tables have the same columns something like

INSERT INTO house_history SELECT * FROM house WHERE hnr = OLD.hnr

Though I am not sure if it is allowed to SELECT from the table the trigger is activated upon.

But IMO shortcut statements like SELECT * or INSERT INTO without a column list are bad practice in production code.

这篇关于如何将mysql表中的所有更改记录到第二个表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！