How to write all changes in mysql table to second?

Question

I use this onChange-trigger to log all changes to my mysql database database “house” as the second table house_history (which has exactly the same fields + version identifier).

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 problem is that there are 80 fields in the table, and I don't want to list them in the trigger.

The reason when I define additional fields in the table, I want the trigger to copy them as well. And I can also easily copy the trigger to another table after creating the corresponding history table.

Is there a way to copy all the fields of the updated row tables and paste them into the history table (with the same field names)?

Answer 1

Assuming both tables have the same columns, e.g.

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

Although I'm not sure if SELECT is allowed from the table to activate the trigger.

But IMO label operators, such as SELECT * or INSERT INTO without a column list, are bad practice in production code.

Answer 2

This is a 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` 

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

Answer 3

If you are worried about the need to update the trigger each time you add a column, you can write a procedure that extracts a list of table columns from the information schema and uses them to PREPARE the CREATE TRIGGER statement, and then EXECUTE it.

However, since prepared statements are not currently available in triggers, you still have to start this procedure manually, each time you change the table. (I tried to create a trigger that dynamically detected changes in individual fields in a table row, and then saved each change as a separate row in the audit table, but the lack of prepared statements did not allow me to do this).

This is probably not worth the effort if you don't add columns to your table often, but if someone does, post SQL here.

Answer 4

To add an answer and refer to the comment above , you can add additional fields before or after the old values ​​if you give the table an alias as shown below (giving the home table an alias 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 diagram

 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;