How to log all changes in a mysql table to a second one?

Question

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)?

Answer 1

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.