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

score 5 · Accepted Answer · answered Jul 22 '11 at 09:12

5

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.

answered Jul 22 '11 at 09:12

wonk0

13,402
1
21
15

No, the command causes the trigger to fail. Unfortunately I don't know how to see the error message. But there is no line inserted in house_history – JochenJung Jul 22 '11 at 09:32
The error message is "Column count doesn't match value count at row 1". Which is because of the additional version ID field. If I delete the version ID field in the history table, your Query works. Do you see any chance, how I can have additional fields in the history table? – JochenJung Jul 22 '11 at 09:51
`SELECT *, col` works; `SELECT col, *` unfortunately not. So if the additional column is at the end of the destination table this would work. But this is really ugly and - IMO - very bad practice. – wonk0 Jul 22 '11 at 10:41
So you would advice me to just name all table fields? – JochenJung Jul 22 '11 at 12:54
wonk0, can you explain why it is such a very bad practice ? – user1075613 Nov 16 '15 at 02:57

score 4 · Answer 2 · answered Sep 10 '12 at 11:31

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

score 2 · Answer 3 · edited May 23 '17 at 10:26

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;

score 2 · Answer 4 · answered Oct 05 '11 at 16:42

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.

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

4 Answers4

Linked