Generic Trigger on MYSQL to replicate data on other table

Question

I'm currently doing a history table that basically logs every single modification in a history table, and adding the Action executed.

CREATE TABLE `competencies` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `type` varchar(255) NOT NULL,
 `credit_hour_equivalent` double NOT NULL,
 `description` text,
 `book_name` varchar(255) DEFAULT NULL,
 `domain_id` int(11) DEFAULT NULL,
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 `slug` varchar(255) DEFAULT NULL,
 `video_id` int(11) DEFAULT NULL,
 `statement_id` int(11) DEFAULT NULL,
 `author_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `slug` (`slug`),
 KEY `competencies_domain_id_index` (`domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8;
CREATE TABLE `history_competencies` (
 `id` int(11) DEFAULT NULL,
 `title` varchar(255) DEFAULT NULL,
 `type` varchar(255) DEFAULT NULL,
 `credit_hour_equivalent` double DEFAULT NULL,
 `description` text,
 `book_name` varchar(255) DEFAULT NULL,
 `domain_id` int(11) DEFAULT NULL,
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 `slug` varchar(255) DEFAULT NULL,
 `video_id` int(11) DEFAULT NULL,
 `statement_id` int(11) DEFAULT NULL,
 `author_id` int(11) DEFAULT NULL,
 `author_action` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For every INSERT on competencies I want to copy the row into history_competencies.

So I created the following trigger

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
 INSERT INTO history_competencies
 VALUES (NEW.`id`,
 NEW.`title`,
 NEW.`type`,
 NEW.`credit_hour_equivalent`,
 NEW.`description`,
 NEW.`book_name`,
 NEW.`domain_id`,
 NEW.`created_at`,
 NEW.`updated_at`,
 NEW.`slug`,
 NEW.`video_id`,
 NEW.`statement_id`,
 NEW.`author_id`,
 'CREATE');

My question aims to find a better way to write the trigger so I don't have to DROP and CREATE the trigger every time that I change the competencies table or reuse for other tables just changing the table names.

I was looking for something like

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
 INSERT INTO history_competencies
 VALUES (NEW.*, 'CREATE');

Or

CREATE TRIGGER `after_insert_log_competency` after INSERT
ON `competencies`
FOR EACH row
 INSERT INTO history_competencies
 VALUES (NEW, 'CREATE');

But it didn't work.

Do you know if this is even possible?

Thank you!

• Colin 't Hart: You could generate your triggers based on the metadata.

Answer 1

One idea would be to write:

CREATE TRIGGER `after_insert_log_competency` 
AFTER INSERT ON `competencies` 
FOR EACH ROW
 INSERT INTO history_competencies SELECT *, 'CREATE' FROM competencies WHERE id=NEW.id;

That doesn't work well for deletions, as the row wouldn't exist anymore. Also, you are doing an extra eq_ref for each write.

But be careful, because if you ALTER competencies without altering history_competencies, all your INSERTS, etc. on competencies will fail due to the trigger failing.

If you do very frequently ALTERs, and all you do is inserting and selecting those new fields, you could serialize them to a single BLOB. That is a very denormal form, with a lot of drawbacks (no constraints, no indexes, difficult to update a single field), but it may be interesting in some cases.

• emancu: I'm not changing the tables all the time but I'm aware that I've to change the history table too. But I want to avoid the DROP and CREATE of triggers. It seems like I've to do it, because I need to apply the same for DELETE and I don't want to do a SELECT every time. Thanks