Is it possible to create a trigger when updating a certain column by the desired value?

Question

SQL

Hello.

Can you please tell me if it is possible to create a trigger for inserting a new row, which would follow the update of one row sorted by the date of addition in a certain column and would work only when the value of this column would equal, for example, 5.

Roughly speaking, I would follow such a request :

SELECT * FROM `testtable` WHERE `checkCol` = 4 ORDER BY createdAt DESC LIMIT 1

And when checkCol updates from 4 to 5, a trigger would fire to insert a query like this:

INSERT INTO `testtable` (`id`, `checkCol`, `someData`, `createdAt`) VALUES (NULL, '1', 'data 4', current_timestamp());

Schema and data example:

CREATE TABLE IF NOT EXISTS `testtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `checkCol` tinyint(1) NOT NULL,
  `someData` varchar(255) DEFAULT NULL,
  `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `testtable` (`id`, `checkCol`, `someData`, `createdAt`) VALUES
(1, 5, 'data1', '2020-08-28 14:24:15'),
(2, 4, 'data2', '2020-08-28 14:24:25'),
(3, 4, 'data3', '2020-08-28 14:24:35');

Answer 1

A trigger cannot INSERT into the same table it fires on.