Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

Triggers are activated BEFORE or AFTER for INSERT, UPDATE and DELETE events so we can have all together 6 triggers set for a table.


Dummy table


Field timestamp will help us controlling triggers. It has no other purpose. Timestamp is set to change after insert and update.


CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`surname` varchar(45) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Audit table


CREATE TABLE `student_audit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`row_id` int(10) unsigned NOT NULL,
`name_old` varchar(45) NOT NULL,
`surname_old` varchar(45) NOT NULL,
`name_new` varchar(45) NOT NULL,
`surname_new` varchar(45) NOT NULL,
`timestamp` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Trigger


DROP TRIGGER IF EXISTS `after_update`;

DELIMITER $$

CREATE TRIGGER `after_update`
AFTER UPDATE ON `student`
FOR EACH ROW
BEGIN
IF NEW.`timestamp` <> OLD.`timestamp` THEN
INSERT INTO `student_audit`
(`row_id`, `name_old`, `surname_old`, `name_new`, `surname_new`, `timestamp`)
VALUES
(OLD.`id`, OLD.`name`, OLD.`surname`, NEW.`name`, NEW.`surname`, now());
END IF;
END$$

DELIMITER ;

Test


mysql> SELECT * FROM student;
Empty set (0.01 sec)

mysql> SELECT * FROM student_audit;
Empty set (0.00 sec)

mysql> INSERT INTO student (name, surname) VALUES ('inanzzz', 'test'), ('hello', 'world');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 21:41:17 |
| 2 | hello | world | 2015-05-01 21:41:17 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_audit;
Empty set (0.00 sec)

mysql> UPDATE student SET name = 'hello' WHERE surname = 'world';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 21:41:17 |
| 2 | hello | world | 2015-05-01 21:41:17 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_audit;
Empty set (0.00 sec)

mysql> UPDATE student SET name = 'bye' WHERE surname = 'world';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 21:41:17 |
| 2 | bye | world | 2015-05-01 21:42:35 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_audit;
+----+--------+----------+-------------+----------+-------------+---------------------+
| id | row_id | name_old | surname_old | name_new | surname_new | timestamp |
+----+--------+----------+-------------+----------+-------------+---------------------+
| 1 | 2 | hello | world | bye | world | 2015-05-01 21:42:35 |
+----+--------+----------+-------------+----------+-------------+---------------------+
1 row in set (0.00 sec)