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


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

Audit table


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

Trigger


DROP TRIGGER IF EXISTS `after_insert`;

DELIMITER $$

CREATE TRIGGER `after_insert`
AFTER INSERT ON `student`
FOR EACH ROW
BEGIN
INSERT INTO `student_audit`
(`row_id`, `name`, `surname`, `timestamp`)
VALUES
(NEW.`id`, NEW.`name`, NEW.`surname`, now());
END$$

DELIMITER ;

Test


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

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

mysql> INSERT INTO student (name, surname) VALUES ('inanzzz', 'test');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 20:27:59 |
+----+---------+---------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM student_audit;
+----+---------+---------+--------+---------------------+
| id | name | surname | row_id | timestamp |
+----+---------+---------+--------+---------------------+
| 1 | inanzzz | test | 1 | 2015-05-01 20:27:59 |
+----+---------+---------+--------+---------------------+
1 row in set (0.00 sec)