Mysql – Generic Trigger on MYSQL to replicate data on other table

MySQLtrigger

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!

Best Answer

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.

Related Question