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

I use th开发者_运维知识库is 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.

This is functional trigger:

CREATE TRIGGER table1_trigger BEFORE UPDATE ON table1
FOR EACH ROW BEGIN
   INSERT INTO table1_versions 
          SELECT *,null,NOW() FROM table1 WHERE id = OLD.id ;
 END`

on tables:

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL,
  .....
);
CREATE TABLE IF NOT EXISTS `table1_versions` (
  `id` int(11) NOT NULL,
  .....
  `idhistory` int(20) NOT NULL auto_increment,
  `historydate` datetime default NULL
);

If you're worried about having to update the trigger every time you add a column, you could write a procedure that retrieves the list of table columns from the information schema and uses these to PREPARE a CREATE TRIGGER statement and then EXECUTE it.

However, because prepared statements are not currently possible within triggers, you would still need to run this procedure manually, each time you change the table. (I was trying to create a trigger that dynamically detected changes to individual fields in a table row and then saved each change as a separate row in an audit table. But the lack of prepared statements stopped me doing this).

It's probably not worth the effort unless you add columns to your table often, but if anyone does go to the effort, please post the SQL here.

To add on to the answer and to address the comment above, you can add extra fields before or after old values, as long as you give the table an alias as shown below (giving the house table an alias of h and using h.* instead of *)

DELIMITER //
CREATE TRIGGER `updateHouse` BEFORE UPDATE ON `house` 
        FOR EACH ROW INSERT INTO house_history 
                     SELECT NULL, h.*, NOW() FROM house h WHERE id = OLD.id
    //
DELIMITER ;

Using the following schema

CREATE TABLE `house` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

CREATE TABLE `house_history` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `house_id` INT ,
    `name` VARCHAR(50) NULL,
    `date_changed` DATETIME,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;